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

MORE INFORMATION

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.

Stack trace information