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
>?''
>