Recordset method v/s query method

[Anand]

Hello,
Am on A2k. I have an app that uses the following method extensively to work
with data:

Set dbs = currentdb
set rst = dbs.currentdb(stsql)

With rst
....
....

I want to know if this method is efficient. Is there a better/faster way of
working with data (inserting/updating/fetching). Would using action queries
lead to faster data insertions and updates?

TIA
Anand

 

[Allen Browne]

There is no practical difference for data stored in JET.

Theoretically, a saved query already has an execution plan saved, so there's
a marginal benefit. But it the data has changed markedly since the query was
saved, it may actually execute more slowly (i.e. the saved plan is
sub-optimal.) In any case, calculating an execution plan is insignificant
for a simple query (as many action queries are.)

In practice, I find the dynamic query (SQL string) is much more flexible
(e.g. putting literal values directly into the string for your criteria),
and often simplifies the query (particularly where you offer lots of
optional criteria) so it is generally my preferred approach.

 

[Dirk Goldgar]

Hello,
Am on A2k. I have an app that uses the following method extensively to
work
with data:

Set dbs = currentdb
set rst = dbs.currentdb(stsql)

With rst
...
...

I want to know if this method is efficient. Is there a better/faster way
of
working with data (inserting/updating/fetching). Would using action
queries
lead to faster data insertions and updates?

If you are updating multiple records in a way that can be represented by a
single update query, then it will be faster and more efficient to run such
an update query than to open a recordset and loop through the records,
updating each. It's similar with append queries, if you can write one
append query that inserts multiple records.

However, if you have to run a separate action query for each update, so you
have to execute multiple queries and can't act on many records at once, I
don't know whether that's significantly more efficient than using a
recordset or not.

 

[Mike Painter]

Hello,
Am on A2k. I have an app that uses the following method extensively
to work with data:

Set dbs = currentdb
set rst = dbs.currentdb(stsql)

With rst
...
...

I want to know if this method is efficient. Is there a better/faster
way of working with data (inserting/updating/fetching). Would using
action queries lead to faster data insertions and updates?

TIA
Anand

If you are looping through the recordset a query will be faster.
For a few records it probably will not be noticed but I once replaced
looping with a set of complex queries and went from several seconds to less
than a second.