OpenRecordset is too slow

  • Thread starter Thread starter E.P.
  • Start date Start date
E

E.P.

Hi,

I've been using Set rs = db.openRecordset(sqlStr) where
sqlStr is a select query that uses data from 2 tables with
about 6,000 records each. However, the line "Set rs =
db.OpenRecordset(sqlStr) is toooooooooo slow. All i want
to do is go through my query and take the data that I
need. I don't need to make any changes to it. Does
anyone know a fast way to do this?
Please help.
Thank you,
Eva
 
You might want to look in VB Help for the OpenRecordset Method, to see the
additional arguments available. You may be able to improve performance by
opening the record as a Snapshot.

hth,
 
I've been using Set rs = db.openRecordset(sqlStr) where
sqlStr is a select query that uses data from 2 tables with
about 6,000 records each. However, the line "Set rs =
db.OpenRecordset(sqlStr) is toooooooooo slow. All i want
to do is go through my query and take the data that I
need. I don't need to make any changes to it. Does
anyone know a fast way to do this?
Click to expand...

Does your sql statement include criteria to limit the rows returned to only
those that you need? If you can get by with only moving through your recordset
using the "MoveNext" method, as opposed to "FindFirst", you can open your
recordset using the dbOpenForwardOnly type argument for the OpenRecordset()
method which should help in speeding things up.

Set rs = db.openRecordset(sqlStr, dbOpenForwardOnly)

It also helps if the field(s) you are using as criteria in your sql statement
are indexed and if you can stick with an "equi-join" (INNER JOIN), as opposed to
a LEFT or RIGHT join between your two tables.
 
Last sentence in my post should read:

You may be able to improve performance by
opening the *recordset* as a Snapshot

instead of:

You may be able to improve performance by
opening the record as a Snapshot
 
E.P. said:
Hi,

I've been using Set rs = db.openRecordset(sqlStr) where
sqlStr is a select query that uses data from 2 tables with
about 6,000 records each. However, the line "Set rs =
db.OpenRecordset(sqlStr) is toooooooooo slow. All i want
to do is go through my query and take the data that I
need. I don't need to make any changes to it. Does
anyone know a fast way to do this?
Please help.
Thank you,
Eva
Click to expand...

In addition to the suggestions others have made, it may be a good idea
to post the value of sqlStr, in case there's something about this
particular query that makes it take a long time to execute. In my
experience, OpenRecordset is *not* slow, but some *queries* may be very
slow indeed.
 
Back
Top