Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q280067

↑ Back to the top


Symptoms

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.

↑ Back to the top


Cause

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.

↑ Back to the top


Resolution

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.

↑ Back to the top


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.

↑ Back to the top


Keywords: KB280067, kbprb

↑ Back to the top

Article Info
Article ID : 280067
Revision : 5
Created on : 6/30/2005
Published on : 6/30/2005
Exists online : False
Views : 376