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