Need something as quick as ADO's .Seek

[Simon Morris]

In ADO Classic, the .Seek method is a very quick way to find one record in a
Jet table. In a 500,000 record table that has a primary key index on the
value sought, ADO can seek a single record in around .001 of a second and
allow the application to grab the column values from that record for later
processing.

My application needs to gather into a result table anything from a few dozen
to several thousand related records, where the next record required depend
on the record(s) previously retreived.

I've created an ADO.NET version of the same thing that uses SQL Selects into
a DataReader and then populates my result table. The trouble is that it is
nearly 6 times slower using the same data as the old .Seek.

I've been checking and I'm certain the slowdown is the Select, ie:

SELECT id, ParentID FROM MyTable WHERE id = ... which will either return 0
or 1 records.

I'm very keen to change over to ADO.NET, but need some help to get the
required performance. I've added a reference to ADO Classic in my VB.NET
experiment and using .Seek there produces the required results, but I'd like
to see if there's an ADO.NET solution.

Any help appreciated,
Simon

 

[Miha Markic]

Hi Simon,

In ADO Classic, the .Seek method is a very quick way to find one record in a
Jet table. In a 500,000 record table that has a primary key index on the
value sought, ADO can seek a single record in around .001 of a second and
allow the application to grab the column values from that record for later
processing.

My application needs to gather into a result table anything from a few dozen
to several thousand related records, where the next record required depend
on the record(s) previously retreived.

I've created an ADO.NET version of the same thing that uses SQL Selects into
a DataReader and then populates my result table. The trouble is that it is
nearly 6 times slower using the same data as the old .Seek.

I've been checking and I'm certain the slowdown is the Select, ie:

SELECT id, ParentID FROM MyTable WHERE id = ... which will either return 0
or 1 records.

I'm very keen to change over to ADO.NET, but need some help to get the
required performance. I've added a reference to ADO Classic in my VB.NET
experiment and using .Seek there produces the required results, but I'd like
to see if there's an ADO.NET solution.

What is your code? Are you using Fill method to populate DataTable?
Is DataTable schema already created? etc.
I don't think that select should be slow.
Pay attention that .Seek only finds the record and not fetches it.

 

[Simon Morris]

Hi Miha

Thanks for your reply.

What is your code? Are you using Fill method to populate DataTable?

No, I get one record at a time using a DataReader and fill the datatable one
record at a time from that. Its tricky to explain, but I'm building a
datatable with essentially unrelated records that need to be got one at a
time.

Here's some pseudo-code to help show what I'm doing ...

For lc = 1 to 1024
strSQLQuery = "SELECT ID, ParentID FROM Test WHERE ID = " &
dt.Rows(lc)("ID")
objCommand.CommandText = strSQLQuery
objDataReader = objCommand.ExecuteReader()
objDataReader.Read()
While objDataReader.Read
dt.Rows(lc)("ID") = 0 + objDataReader.GetValue(0)
dt.Rows(lc)("ParentID") = 0 + objDataReader.GetValue(1)
... and some code here that populates other records further down the
datatable if required
End While
objDataReader.Close()
Next lc

Is DataTable schema already created? etc.

Yes, it is.

I don't think that select should be slow.

You're right, of course, but the process above takes 6.4 seconds to iterate
through the 1024 loop, but using the .Seek method takes just 0.7 of a second
(both times are from non-first time runs to ensure that caching is taking
place). The line that is slow is

objDataReader = objCommand.ExecuteReader()

Changing the code to use ADO Classic replaced this line with

objRecordsetADO.Seek(dt.Rows(i)("ID"), ADODB.SeekEnum.adSeekFirstEQ)

and sped the routine up by a factor of 10. Of course the connection and
other stuff changed too, but its this one that is repeated 1024 times.

Pay attention that .Seek only finds the record and not fetches it.

True enough, but as I can read the record just like I would read the
DataReader, the effect is the same.

Thanks very much for your comments. You never know when another point of
view can give that breakthrough!

- Simon

 

[Cor]

Hi Simon,

Did you look for the datarowcollection.find method ?

Cor

 

[Miha Markic]

Hi Simon,

Hi Miha

Thanks for your reply.


No, I get one record at a time using a DataReader and fill the datatable one
record at a time from that. Its tricky to explain, but I'm building a
datatable with essentially unrelated records that need to be got one at a
time.

Here's some pseudo-code to help show what I'm doing ...

For lc = 1 to 1024
strSQLQuery = "SELECT ID, ParentID FROM Test WHERE ID = " &
dt.Rows(lc)("ID")
objCommand.CommandText = strSQLQuery
objDataReader = objCommand.ExecuteReader()
objDataReader.Read()
While objDataReader.Read
dt.Rows(lc)("ID") = 0 + objDataReader.GetValue(0)
dt.Rows(lc)("ParentID") = 0 + objDataReader.GetValue(1)
... and some code here that populates other records further down the
datatable if required
End While
objDataReader.Close()
Next lc

Comment here - you are calling Read twice. Is this OK? (i know it could be
just a typo).
Next, you are calling GetValue which returns an object. Rather, call
GetInt32 for example.

Yes, it is.


You're right, of course, but the process above takes 6.4 seconds to iterate
through the 1024 loop, but using the .Seek method takes just 0.7 of a second
(both times are from non-first time runs to ensure that caching is taking
place). The line that is slow is

objDataReader = objCommand.ExecuteReader()

Changing the code to use ADO Classic replaced this line with

objRecordsetADO.Seek(dt.Rows(i)("ID"), ADODB.SeekEnum.adSeekFirstEQ)

and sped the routine up by a factor of 10. Of course the connection and
other stuff changed too, but its this one that is repeated 1024 times.


True enough, but as I can read the record just like I would read the
DataReader, the effect is the same.

Thanks very much for your comments. You never know when another point of
view can give that breakthrough!

I've just started.
I guess seek is faster because it has already an open cursor, while execute
reader has to open it.

The ado.net code seems slow because you are doing the same as with ado.
You could improve your task in many ways. Here are three of them:
You might consider using batch read - multiple stataments in one execute.
You might also consider reading more rows at once using IN statement.
Also, you might consider using DataAdapter.Fill method.

There are plenty of ways to rewrite your code to make it more effective.

 

[Simon Morris]

Hi Miha

Comment here - you are calling Read twice. Is this OK? (i know it could be
just a typo).

Yes, sorry, a typo.

Next, you are calling GetValue which returns an object. Rather, call
GetInt32 for example.

I'll fix that to gain every ounce of speed I can, but commenting out those
lines barely doesn't seem to change the loop times any if at all.

The ado.net code seems slow because you are doing the same as with ado.
You could improve your task in many ways. Here are three of them:
You might consider using batch read - multiple stataments in one execute.
You might also consider reading more rows at once using IN statement.
Also, you might consider using DataAdapter.Fill method.

There are plenty of ways to rewrite your code to make it more effective.

Thank you for your ideas, they'll give me some new directions to look.

- Simon

 

[Simon Morris]

Hi Cor

No, I didn't, but I will now. Thanks.

- Simon

 

[Simon Morris]

Hi Miha & Cor

Thanks for your suggestions earlier. In a interesting twist, I've combined
Miha's suggestion regarding the "IN" keyword and Cor's suggestion to use the
datarowcollection.find method, plus a twist in between and come up with a
cool solution.

Rather than finding the records I want in the order I want them, I now bulk
find the records with a much smaller number of DataReaders and store the
found, but unordered, records in a temporary table. I then use the .Find
method to get the records I want in the order I want them. Works like a
charm!

Here's the timings

Method, 1000 iterations
Lots of single row DataReaders - 10.375 seconds
Lots of ADODB .Seeks - 0.68 seconds
A few DataReaders and lots of .Finds - 0.53 seconds.

Smaller runs aren't quite so hot, with the ADODB.Seek method being a 1/100
of a second quicker, but I've still got some fine-tuning to do.

Kudos to Miha & Cor, with a little self-congratulatory warmth to me too.

- Simon