Re: ADO find method performance

I am developing one application where i need to search for record in
the ado recordset. i am giving search crieteria on a indexed(ID)
field. depending upon that record i need to fetch other field values.

my ado record set is created though client side cursor. and it
contains 1 million data and it can be 10/20 millions of record. what i
do is i just create a client side recordset for that ID field along
with required other 3 fields. those 3 field values i need for an ID
value.

ADO find method works fine when record counts are less but it dies for
millions of record.
Is there any performant way to achieve this??

Yes dont do it.

Recordsets are _LOUSY_ containers
I almost never use any of the client-sided methods : find(), sort(),
filter()
They are okay for small-scale and possibly medium-scale in terms of number
of records - but they are bad for large scale - as you have found out.

find() is O(N) and never O(log N)

Instead you either should either

- read into a different container like something for C, C++, VB and do
searching within that, preferably binary searches
(for C using bsearch(), for C++ using a sorted vector and then
lower_bound())

- or alternatively, use SQL, dont build up a recordset but do the queries as
you need them.
For single objects, I try and use Record objects.
And Stored Procedures as well.
It is nearly always a mistake to replace a SQL query with a Recordset _AND_
glue code (filter, find, sort) to do the equivalent. The SQL query is nearly
always superior. The database engine is designed to retrieve data fast - so
what not put its power to good use? If you do not know SQL - now is a good
time to learn.

Cheers

Stephen Howe









.

Relevant Pages

  • ADO find method performance
    ... the ado recordset. ... i am giving search crieteria on a indexed ... depending upon that record i need to fetch other field values. ... my ado record set is created though client side cursor. ...
    (microsoft.public.data.ado)
  • Re: Using recordset as table
    ... Your SQL query text is being sent to the SQL server. ... >> ADO recordset can't be referenced in the query because the SQL ... process is the client. ...
    (microsoft.public.scripting.jscript)
  • Re: Using recordset as table
    ... > Your SQL query text is being sent to the SQL server. ... An ADO recordset can't ... > about ADO recordsets being held on the client side. ...
    (microsoft.public.scripting.jscript)