Leo,

Have you looked into using the RecordSet.Save() method. This will save the contents of the recordset to a file, then you can read the file into a string. This should be much faster than manually creating the string, however you won't be able to format the XML to your own needs.

loRs.Save('C:\xmldata.xml',1)
lcXml = FILETOSTR('C:\xmldata.xml')

This month's issue of Foxpro Advisor also has an article on speeding up strings with XML by writing large amounts of data out to a file instead of writing to a memory variable, however through my testing, the above method is still much faster.

Hope this helps,
Kurt
>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
>?''
>