>I have noticed significant speed difference between SPT and CursorAdapter data retrieval for parameterized SQL.
>
>I tried "SELECT * FROM OrderDetail WHERE OrderID = ?VP_OrderID" with SQLEXEC() and CursorAdapter. CursorAdapter.CursorRefresh() takes 2-3 seconds to retrieve 2 rows. While SQLEXEC() retrieves them in no time.
>
>Has anyone noticed this too?
>

Hi Jayesh,

I did some testing with the following code:

CLOSE DATABASES all
CLEAR 
SET TALK OFF
SET NOTIFY OFF
SET NOTIFY CURSOR OFF
SET SAFETY OFF

SET ALTERNATE TO alternate.txt
SET ALTERNATE ON 

?VERSION()

PUBLIC nOrderId

?"----------------------------------------------"
nOrderId=10260
DoTest("=",.F.,10000)
?"----------------------------------------------"
nOrderId=10248
DoTest(">=",.T.,1000)
?"----------------------------------------------"

SET ALTERNATE OFF 
SET ALTERNATE TO 
MODIFY COMMAND alternate.txt NOWAIT 

FUNCTION DoTest(cOperation,lCountOnly,nCount)

LOCAL cSQL

cSQLEx="SELECT * FROM [Order Details] where orderID"+cOperation+"?nOrderId"

? "Test for :",cSQLEx
?[nLoopCount=],nCount 

oCon=SQLSTRINGCONNECT([DRIVER=SQL Server;SERVER=(local);]+;
           [DATABASE=Northwind;Trusted_Connection=Yes])

IF oCon<1
	? "FAILED to connect!!!"
	return
ENDIF
?
? "SPT test-"

nSeconds=SECONDS()

FOR I=1 TO nCount 
	SQLEXEC(oCon,cSQLEx,"SqlRes")
NEXT 	

?? SECONDS()-nSeconds
SELECT SqlRes
IF !lCountOnly
	LIST 
ENDIF
? "RECCOUNT:",RECCOUNT()	

LOCAL oCA as CursorAdapter
oCA=CREATEOBJECT("CursorAdapter")
oCA.SelectCmd=cSQLEx
oCA.FetchSize=-1

oCA.DataSource=oCon
oCA.DataSourceType="ODBC"
oCA.CursorFill()
?
? "CursorAdapter (ODBC) test-"

nSeconds=SECONDS()

FOR I=1 TO nCount 
	oCA.CursorRefresh()
NEXT 	

?? SECONDS()-nSeconds

SELECT (oCA.Alias)
IF !lCountOnly
	LIST 
ENDIF
? "RECCOUNT:",RECCOUNT()	

SQLDISCONNECT(oCon)


LOCAL oConn as ADODB.Connection, oRS as ADODB.Recordset, oComm as ADODB.Command

oConn=CREATEOBJECT("ADODB.Connection")
oConn.Open([Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;]+;
                [Data Source=(local);Initial Catalog=Northwind])

oRs=CREATEOBJECT("ADODB.Recordset")
oRS.ActiveConnection=oConn

oComm=CREATEOBJECT("ADODB.Command")
oComm.ActiveConnection=oConn

oCA.DataSource=oRs
oCA.DataSourceType="ADO"

oCA.CursorFill(,,1,oComm)
?
? "CursorAdapter (ADO) test-"

nSeconds=SECONDS()

FOR I=1 TO nCount 
	oCA.CursorRefresh()
NEXT 	

?? SECONDS()-nSeconds

SELECT (oCA.Alias)
IF !lCountOnly
	LIST 
ENDIF
? "RECCOUNT:",RECCOUNT()	
USE

ENDFUNC

Here is the result:

Visual FoxPro 08.00.0000.2521 for Windows
----------------------------------------------
Test for : SELECT * FROM [Order Details] where orderID=?nOrderId
nLoopCount=      10000

SPT test-          4.947
Record#      ORDERID   PRODUCTID             UNITPRICE    QUANTITY              DISCOUNT
      1        10260          41                7.7000          16                  0.25
      2        10260          57               15.6000          50                  0.00
      3        10260          62               39.4000          15                  0.25
      4        10260          70               12.0000          21                  0.25

RECCOUNT:          4

CursorAdapter (ODBC) test-          4.637
Record#      ORDERID   PRODUCTID             UNITPRICE    QUANTITY              DISCOUNT
      1        10260          41                7.7000          16                  0.25
      2        10260          57               15.6000          50                  0.00
      3        10260          62               39.4000          15                  0.25
      4        10260          70               12.0000          21                  0.25

RECCOUNT:          4

CursorAdapter (ADO) test-         14.180
Record#      ORDERID   PRODUCTID             UNITPRICE    QUANTITY              DISCOUNT
      1        10260          41                7.7000          16                  0.25
      2        10260          57               15.6000          50                  0.00
      3        10260          62               39.4000          15                  0.25
      4        10260          70               12.0000          21                  0.25

RECCOUNT:          4
----------------------------------------------
Test for : SELECT * FROM [Order Details] where orderID>=?nOrderId
nLoopCount=       1000

SPT test-         20.659
RECCOUNT:       2155

CursorAdapter (ODBC) test-         20.770
RECCOUNT:       2155

CursorAdapter (ADO) test-         21.581
RECCOUNT:       2155
----------------------------------------------

- Performance of ODBC based CursorAdapter is about the same as SPT performance.
- ADO based CursorAdapter is about three times slower on small resultsets, but it manages to call CursorRefresh (4 records resultset) 705 times per second.
- The difference in performance becomes smaller as amount of records increases. For 2155 records the difference is less than one second (3.9%) for 1000 calls.

Even though there are areas for improvement, things are not that dramatic as you describe.

Thanks,
Aleksey.