Hi Michael,

That's what I expected too, that's why tried the method.
See message #765918. I tried to write the strings to a file on disk, and that seems to be a lot faster.
I tried the same with CASE #3, but that's slow too. It seems to be that the internal sting concatenating in ADO isn't efficient too? When I open my task manager I see in ADO the same activity when I was copying strings after each other in VFP: much CPU activity, Memory is increasing en decreasing rapidly (indicating creating new pointer to string variable and releasing the old one), not much I/O activity.
There is enough physical memory remaining, so swapping is not needed.
What do you think?

lnSec = Seconds()
*lcXml = loStr.ReadText() && very slow!
lcFile = "c:\test.xml"
loStr.SaveToFile(lcFile)
lcXml = FileToStr(lcFile)
?'String:  ', Seconds() - lnSec

When I tried the .SaveToFile() method in ADODB.Stream, it takes about 6 to 7 seconds to write the file in my situation, which is acceptable but slower than my new CASE #1 in message #765918.

Leo

>I would have expected that your CASE #3 would have been the fastest. Especially since you have to iterate the resultset to construct the string.
>
>-Mike
>
>>Hi All,
>>
>>We use a vfp middletier dll that retrieves xml data from SQL Server from an SP that uses the For Xml Auto, Elements clause. When the recordset is large (about 65000 records with 10 fields) the code becomes very slow (about 400 seconds).
>>I tested some different methods, and it ended up that the method we were using first was the fastest.
>>The execute of the sql statement is done in a couple of seconds, but the retrieving of the recordset is very slow whith large recordsets.
>>Has anyone some tips?
>>Thanks in advance!
>>
>>Leo
>>
>>

>>Local lnMethod As Number
>>Local lcSql As String
>>Local lcXml As String
>>Local loConn As ADODB.Connection
>>Local loRs As ADODB.Recordset
>>Local loCmd As ADODB.Command
>>Local loStr As ADODB.Stream
>>
>>lnMethod	= 3
>>lcXml		= ''
>>lcSql		= "<large query>"
>>loConn		= CreateObject('ADODB.Connection')
>>loRs		= CreateObject('ADODB.Recordset')
>>loCmd		= CreateObject('ADODB.Command')
>>loStr		= CreateObject('ADODB.Stream')
>>
>>?'Method: ', lnMethod
>>DO Case
>>	Case lnMethod = 1 && our first method
>>		loConn.Open('DSN=;UID=;PWD=')
>>		With loRs
>>			.ActiveConnection = loConn
>>			.CursorLocation = 3  && adUseClient
>>			.CursorType = 0  && adOpenForwardOnly
>>			.LockType = 1  && adLockReadOnly
>>
>>			lnSec = Seconds()
>>			.Open(lcSql)
>>			?'Execute: ', Seconds() - lnSec
>>
>>			lnSec = Seconds()
>>			Do While !.Eof
>>				lcXml = lcXml + .Fields(0).Value
>>				.MoveNext()
>>			EndDo
>>			?'String:  ', Seconds() - lnSec
>>		EndWith
>>
>>	Case lnMethod = 2
>>		loConn.Open('DSN=;UID=;PWD=')
>>		With loRs
>>			.ActiveConnection = loConn
>>			.CursorLocation = 3  && adUseClient
>>			.CursorType = 0  && adOpenForwardOnly
>>			.LockType = 1  && adLockReadOnly
>>
>>			lnSec = Seconds()
>>			loRs.Open(lcSql)
>>			?'Execute: ', Seconds() - lnSec
>>
>>			lnSec = Seconds()
>>			lcXml = .GetString()
>>			?'String:  ', Seconds() - lnSec
>>		EndWith
>>
>>	Case lnMethod = 3
>>		loConn.Open('PROVIDER=SQLOLEDB.1;DATA SOURCE=;DATABASE=;UID=;PWD=')
>>		loStr.Open()
>>		With loCmd
>>			.ActiveConnection = loConn
>>			.CommandText = lcSql
>>			.Properties("Output Stream") = loStr
>>
>>			lnSec = Seconds()
>>			.Execute(,,1024)
>>			?'Execute: ', Seconds() - lnSec
>>		EndWith
>>		lnSec = Seconds()
>>		lcXml = loStr.ReadText()
>>		?'String:  ', Seconds() - lnSec
>>
>>EndCase
>>?''
>>