Hi All!
Porting an application from using Access as a backend using the Jet driver to using SqlServer as a backend using the SqlOleDb ADO driver.
In the old code, we used to use the RecordSet's Seek method, which was blindingly fast. As SqlOleDb doesn't support seek, we switched to using Find instead, as most of our queries are "ObjectId = 3", looking for a very specific object.
Now, as I said, in Access days, the Seek returned almost instantaneously. When using Find however, I am getting times that make me believe that the index is being ignored.
Case in point is the following. We have an Objects table that has an integer field, ObjectId, that is the primary key for the table. The table itself has 16133 entries at the current moment. Using Sql Query Analyzer and test code, a straight SQL statement comes back almost instantaneously in all cases. Using the Find method, a "ObjectId = 5" query will come back with the same response, but a "ObjectId = 16393" query will take between 1.5 and 2.0 seconds.
For clarity, we are opening the RecordSet using
Open( "Objects", vtConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect );
and the connection itself is opened as normal, (assuming server side cursors are the defaults) adConnectUnspecified specifying a syncronous connection.
The current model has the objects bound to the record set, and I would prefer to not have to rewrite large portions of code because of this.
any help anyone?
thanks in advance,
Jack
Jack De Winter
Software Developer
MedTel Software
Porting an application from using Access as a backend using the Jet driver to using SqlServer as a backend using the SqlOleDb ADO driver.
In the old code, we used to use the RecordSet's Seek method, which was blindingly fast. As SqlOleDb doesn't support seek, we switched to using Find instead, as most of our queries are "ObjectId = 3", looking for a very specific object.
Now, as I said, in Access days, the Seek returned almost instantaneously. When using Find however, I am getting times that make me believe that the index is being ignored.
Case in point is the following. We have an Objects table that has an integer field, ObjectId, that is the primary key for the table. The table itself has 16133 entries at the current moment. Using Sql Query Analyzer and test code, a straight SQL statement comes back almost instantaneously in all cases. Using the Find method, a "ObjectId = 5" query will come back with the same response, but a "ObjectId = 16393" query will take between 1.5 and 2.0 seconds.
For clarity, we are opening the RecordSet using
Open( "Objects", vtConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect );
and the connection itself is opened as normal, (assuming server side cursors are the defaults) adConnectUnspecified specifying a syncronous connection.
The current model has the objects bound to the record set, and I would prefer to not have to rewrite large portions of code because of this.
any help anyone?
thanks in advance,
Jack
Jack De Winter
Software Developer
MedTel Software