>It appears that ADO is significantly faster than SPT. Below code is just an example how I compared the performance:
>
>time1 = SECONDS()
>oConn = CREATEOBJECT("adodb.connection")
>oConn.Open("bbfsdsn","sa","")
>oCustomerRS = CREATEOBJECT("adodb.recordset")
>oCustomerRS.Open("SELECT * FROM patledger",oconn)
>time2 = SECONDS()
>
>WAIT WINDOW STR(time2-time1,10,2) && SHOWS 91.10 seconds
>
>Time1 = SECONDS()
>oConn = SQLCONNECT("BBFSDSN","sa","")
>cSQL = SQLEXEC(oConn,"SELECT * FROM patledger","oCustomerRS")
>TIME2 = SECONDS()
>
>WAIT WINDOW STR(time2-time1,10,2) && SHOWS 208.17 seconds
>
>BTW, patledger table contains 650,500 records.
>
>Any additional facts that confirms this is very much appreciated.
It's been that way since MDAC 2.1. And as John states, with a few tweaks it can run even faster.
Set the cursorlocation property of the connection to adUseClient and execute the SQL with the connection's Execute:
oRS = oConn.Execute("Select * From patledger")
And if this is to a SQL Server make sure you are using the OLEDB provider instead of a DSN as in your example.