ADO.NET 2.0 Performance Guidelines - Binary Large Objects

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman


Use CommandBehavior.SequentialAccess and GetBytes to Read Data

The default behavior of the DataReader is to read an entire row into memory. All columns are accessible in any order until the next row is read.

If you retrieve large BLOBs, reading the whole BLOB into memory may cause excessive memory consumption. Using CommandBehavior.SequentialAccess enables you to stream the data or to send the data in chunks from the column containing the BLOB by using the GetBytes, GetChars, or GetString methods.

The following code fragment shows how to use the SequentialAccess and GetBytes methods.

// Allocate a buffer to hold a BLOB chunk.
int bufferSize = 100;  // the size of the buffer to hold interim chunks  of the BLOB
byte[] outbyte = new byte[bufferSize];  // The buffer to hold the BLOB
SqlDataReader myReader = empCmd.ExecuteReader (CommandBehavior.SequentialAccess);
while (myReader.Read())
 // The BLOB data is in column two. Must get the first column
 // before the BLOB data.
  empID = myReader.GetInt32(0); // First column
 // Read the bytes into outbyte[] and retain the number of bytes returned.
  retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
 // Continue reading and writing while there are bytes beyond the
 // Size of the buffer.
 while (retval == bufferSize)
   // Write data to a file or to a Web page (omitted for brevity).
   . . .
   // Reposition the start index to the end of the last buffer
   // and fill the buffer.
   startIndex += bufferSize;
   retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

Note When you use CommandBehavior.SequentialAccess, you must retrieve columns in sequence. For example, if you have three columns, and the BLOB data is in the third column, you must retrieve the data from the first and second columns, before you retrieve the data from the third column.


Use READTEXT to Read from SQL Server 2000

The READTEXT command reads text, ntext, or image values from a text, ntext, or image column. The READTEXT command starts reading from a specified offset and reads the specified number of bytes. This command is available in SQL Server 2000 and later. This command enables you to read data in chunks by sending a fixed set of bytes over the network for each iteration. The following are the steps you must follow to use the READTEXT command:

1. Obtain a pointer to the BLOB by using the TEXTPTR command. 1. Read the BLOB, by using the READTEXT command, in the required chunk size, with the help of the pointer that you obtained in step 1. 1. Send the data to the client. 1. Read the data on the client, and then store it in a buffer or a stream.

The following code fragment shows how to use the READTEXT command.

int BUFFER_LENGTH  = 32768; // chunk size
// Obtain a pointer to the BLOB using TEXTPTR.
SqlCommand cmdGetPointer = new SqlCommand(
   "SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture)" +
   "FROM Categories WHERE CategoryName='Test'", conn);

// Set up the parameters.
SqlParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer",  SqlDbType.VarBinary, 100);

// Run the query.
// Set up the READTEXT command to read the BLOB by passing the following
// parameters: @Pointer – pointer to blob, @Offset – number of bytes to
// skip before starting the read, @Size – number of bytes to read.
SqlCommand cmdReadBinary = new SqlCommand(
    "READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", conn);
// Set up the parameters for the command.
SqlParameter SizeParam  = cmdReadBinary.Parameters.Add("@Size",  SqlDbType.Int);
SqlDataReader dr;
int Offset= 0;
Byte []Buffer = new Byte[BUFFER_LENGTH ];
// Read buffer full of data.
do {
 // Add code for calculating the buffer size - may be less than
 // BUFFER  LENGTH  for the last block.
 dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
 dr.GetBytes(PictureCol, 0, Buffer, 0,  System.Convert.ToInt32 (SizeParam.Value));
 Offset += System.Convert.ToInt32(SizeParam.Value);
 OffsetParam.Value = Offset;
} while( //Check for the offset until it reaches the maximum size.);


Use OracleLob.Read to Read from Oracle Databases

To read BLOBs from an Oracle database, use the .NET Framework Data Provider for Oracle. This data provider provides the System.Data.OracleClient.OracleLob class that can read BLOBs. The following code fragment shows how the OracleLob.Read method enables you to read the data in chunks.

byte[] buffer = new byte[100];
 while((actual = blob.Read(buffer, 0/*buffer offset*/,
        buffer.Length/*count*/)) >0)
{ //write the buffer to some stream


Use UpdateText to Write to SQL Server Databases

If you are using SQL Server, you can use the UpdateText function to write the data in chunks, as shown in the following code fragment.

int BUFFER_LENGTH = 32768; // Chunk size.
// Set the existing BLOB to null and
// Obtain a pointer to the BLOB using TEXTPTR
SqlCommand cmdGetPointer = new SqlCommand(
 "SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE  CategoryName='Test';" +
 "SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE  CategoryName='Test'",
// Set up the parameters.
// Run the query.

// Set up the UPDATETEXT command to read the BLOB by passing the following
// parameters: @Pointer – pointer to blob, @Offset – number of bytes to
// skip before starting the read, @Size – number of bytes to read.
SqlCommand cmdUploadBinary = new SqlCommand(
  "UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn);
// Set up the parameters.
// Read buffer full of data and then run the UPDATETEXT statement.
Byte [] Buffer = br.ReadBytes(BUFFER_LENGTH);
while(Buffer.Length > 0)
  PointerParam.Value = PointerOutParam.Value;
  BytesParam.Value = Buffer;
  DeleteParam.Value = 0; //Do not delete any other data.
  Offset += Buffer.Length;
  OffsetParam.Value = Offset;
  Buffer = br.ReadBytes(BUFFER_LENGTH);

Or, see Knowledge Base article 317043, "HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual C# .NET," at;en-us;317043.


Use OracleLob.Write to Write to Oracle Databases

You can write BLOBs to an Oracle database by using the .NET Framework data provider for Oracle. This data provider permits the System.Data.OracleClient.OracleLob class to write BLOBs. The OracleLob.Write method enables you to write data in chunks.


Avoid Moving Binary Large Objects Repeatedly

Avoid moving BLOB data more than one time. For example, if you build a Web application that serves images, store the images on the file system and the file names in the database instead of storing the images as BLOBs in the database.

Storing the images as BLOBs in the database means that you must read the BLOB from the database to the Web server and then send the image from the Web server to the browser. Reading the file name from the database and having the Web server send the image to the browser reduces the load on the database server. It also reduces the data that is sent between the database and the Web server. This can significantly affect performance and scalability.

Personal tools