Microsoft KB Archive/280067

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

Article ID: 280067

Article Last Modified on 6/30/2005

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Data Access Components 2.6
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q280067



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.



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.



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.



Steps to reproduce behavior
 Open a new Standard EXE project in Visual Basic. Form1 is created by default. Set a reference to ActiveX Data Objects 2.6. Place a CommandButton on Form1.  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 &quot;provider=sqloledb;data source=MyServer\MyInstance;initial & _ catalog=MyDatabase;user id=MyUserID;password=MyPassword;&quot; 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(&quot;Output Stream&quot;).Value = str .CommandType = adCmdText .CommandText = &quot;SELECT * FROM BigTable FOR XML AUTO&quot; .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 &quot;ReadText completed:&quot; & vbCrLf & &quot;Start=&quot; & varStart & &quot;, End=&quot; & _ varEnd & vbCrLf & &quot;Total bytes read: &quot; & Len(strXMLOutput), vbOKOnly + vbInformation, &quot;ReadText&quot;

Bye: Set str = Nothing Set cmd = Nothing Set cnn = Nothing

Screen.MousePointer = vbDefault

Exit Sub ErrorHandler: MsgBox &quot;Error &quot; & Err.Number & &quot;: &quot; & Err.Description, vbOKOnly + vbCritical, &quot;Stream Error&quot; GoTo Bye  Replace MyServer, MyInstance, MyDatabase, MyUserID and MyPassword with appropriate values for your database server. Modify the SQL query as appropriate for your database.</li> Run the application with the ReadText loop commented out, as in the preceding code.</li> 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.</li></ol>

<div class="moreinformation_section">

Stack trace information
The following is a portion of a stack trace that occurs during the frequent allocations. This can be used to identify this issue if a debugger is attached to the process.

0:013> kb ChildEBP RetAddr Args to Child 010c7880 77f86157 10960020 12d20020 00210808 NTDLL!memmove+0x33 010c7a4c 01908587 01b60000 00000101 12d20020 NTDLL!RtlReAllocateHeap+0x9ee 010c7a70 019085c3 01920650 12d20028 00210ffe msdart!XxMpHeapReAlloc+0xb1 010c7aa0 1f482749 01920650 12d20028 00210ffe msdart!MpHeapReAlloc+0x1f 010c7ab0 1f486475 12d20028 00210ffe 800a0c93 msado15!MEMREALLOC+0x1b 010ca328 1f486eec 010c7acc 002208e0 010ca3b4 msado15!CStream::ReadText+0x296 010ca3b8 1f48629e 019428b0 010ccd5c 110224e0 msado15!CStream::ReadAll+0xb8 010ccc30 11018d98 019428b0 ffffffff 010ccd5c msado15!CStream::ReadText+0xbf 010ccdc0 110194b5 0eb09358 0000180c 0000000a customdll!DllCanUnloadNow+0x10a0a 010cce90 779d7d5d 0eb09358 00000008 00000000 customdll!DllCanUnloadNow+0x11127 010ccefc 6a9fa2fb 0eb09358 00000020 00000004 OLEAUT32!tPushValJmpTab+0xf5 010cd858 6a9fa0f4 0eb09358 1100275c 6003008a MSVBVM60!EpiInvokeMethod+0x5a3 010cd8b4 779e776d 0eb09358 6003008a 0314c8ac MSVBVM60!BASIC_CLASS_Invoke+0x64 010cd8ec 779b24ac 0eb09358 6003008a 0314c8ac OLEAUT32!IDispatch_Invoke_Stub+0x6d 010cd924 77d9a3b7 010cda40 00000000 00000000 OLEAUT32!IDispatch_RemoteInvoke_Thunk+0x3c 010cdbe0 77d93a2c 02468988 0238ea6c 03170a60 rpcrt4!NdrStubCall2+0x604 010cdc44 779e8f9f 02468988 03170a60 0238ea6c rpcrt4!CStdStubBuffer_Invoke+0xc8 010cdc64 77b24584 03ab1008 03170a60 0238ea6c OLEAUT32!CStubWrapper::Invoke+0x9f 010cdca8 77b2485f 03170a60 000dbb8c 030fdd90 OLE32!SyncStubInvoke+0x61 010cdcf0 77ab7881 03170a60 0ed48440 03ab1008 OLE32!StubInvoke+0xa8

Keywords: kbprb KB280067

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.