PRB: Retrieving very large XML Documents from SQL Server 2000 by using ReadText method of ADO stream object may be slow

Queries that result in a large amount of XML data being returned through the ReadText method of the ActiveX Data Object (ADO) Stream object may take a great deal of time to execute; if this is done in a COM+ component that is invoked from an ASP page, the user's session may time out.

ADO converts Stream object data from UTF-8 encoding to Unicode; the frequent memory reallocation involved in conversion of such a large quantity of data at once is quite time-consuming.

Make repeated calls to the ReadText method of the ADO command object, and specify a smaller number of characters. Tests have shown that a value equivalent to 128K (131,072) is optimal. Response time decreases as this value is decreased.

Steps to reproduce behavior

1. Open a new Standard EXE project in Visual Basic. Form1 is created by default.
2. Set a reference to ActiveX Data Objects 2.6.
3. Place a CommandButton on Form1.
4. Place the following code in the Click event of the CommandButton:

   On Error GoTo ErrorHandler
   
      Dim cnn As ADODB.Connection
      Dim cmd As ADODB.Command
      Dim str As ADODB.Stream
   
      Dim strXMLOutput As String
      Dim varStart     As Variant
      Dim varEnd       As Variant
   
      Const ReadBytes = 131072
   
      Screen.MousePointer = vbHourglass
   
      Set cnn = New ADODB.Connection
       
      With cnn
         .CursorLocation = adUseClient
         .Open "provider=sqloledb;data source=MyServer\MyInstance;initial & _
         catalog=MyDatabase;user id=MyUserID;password=MyPassword;"
      End With
   
      Set str = New ADODB.Stream
       
      With str
         .Type = adTypeText
         .LineSeparator = adCRLF
         .Mode = adModeRead
         .Open
      End With
   
      Set cmd = New ADODB.Command
       
      With cmd
         Set .ActiveConnection = cnn
         .Properties("Output Stream").Value = str
         .CommandType = adCmdText
         .CommandText = "SELECT * FROM BigTable FOR XML AUTO"
         .Execute , , adExecuteStream
         Set .ActiveConnection = Nothing
      End With
   
      cnn.Close
   
      str.Position = 0
      
      varStart = Now
     
     'This technique results in a much faster assignment.
     'Uncomment this section for faster response.
   '   With str
   '      Do While Not .EOS
   '         strXMLOutput = strXMLOutput & .ReadText(ReadBytes)
   '      Loop
   '   End With
      
     'Single call technique results in a much slower assignment.
     'Comment this out when uncommenting the code above.
      strXMLOutput = str.ReadText
      
      varEnd = Now
   
      MsgBox "ReadText completed:" & vbCrLf & "Start=" & varStart & ", End=" & _
             varEnd & vbCrLf & "Total bytes read: " & Len(strXMLOutput), vbOKOnly + vbInformation, "ReadText"
   
   Bye:
      Set str = Nothing
      Set cmd = Nothing
      Set cnn = Nothing
   
      Screen.MousePointer = vbDefault
   
      Exit Sub
      
   ErrorHandler:
      MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "Stream Error"
      GoTo Bye
   					

5. Replace MyServer, MyInstance, MyDatabase, MyUserID and MyPassword with appropriate values for your database server.
6. Modify the SQL query as appropriate for your database.
7. Run the application with the ReadText loop commented out, as in the preceding code.
8. Comment out the single ReadText method call, and uncomment the ReadText loop. Note that there is a significant improvement in response time.
   
   Note This has been tested with a 60,000 row recordset, returning a 12.8 MB XML document. Using a single call to the ReadText method, response time was over 10 minutes; using the loop, response time was reduced to less than thirty seconds.