Which is faster/more efficient?

[PeterM]

Is it faster or more efficient to run SQL on a local table thru an SQL
statement embedded in VBA or to call a predefined query?

I need to process a lot of records and obviously I want the most effecient
way of doing it. Can anyone tell me which is better?

thanks in advance!

 

[(PeteCresswell)]

Per PeterM:

Is it faster or more efficient to run SQL on a local table thru an SQL
statement embedded in VBA or to call a predefined query?

I need to process a lot of records and obviously I want the most effecient
way of doing it. Can anyone tell me which is better?

Create a dummy DB, write some code to fill up a table with bazillions of junk
records and try it.

My experience is that, even though predefined query objects are technically
faster (pre-compiled and all that), I've never been able to notice a speed diff.

OTOH, I've only tried benchmarking a couple of times.

OTOOH, I'm highly partial to predefined query objects for
documentation/readability purposes. I find it a *lot* easier to track down
references to things and figure out what the code is doing when named queries
are used instead of in-line SQL.

 

[Tony Toews]

OTOOH, I'm highly partial to predefined query objects for
documentation/readability purposes. I find it a *lot* easier to track down
references to things and figure out what the code is doing when named queries
are used instead of in-line SQL.

Agreed with a slight difference.

What I've been doing though is putting my action queries in VBA code.
I first create and save the SELECT query with all the joins, etc,
required. Without using any parameters. This way I can look at the
query and see what data is being displayed. Then I create the action
query, without saving it, with the criteria. I switch to SQL view
mode and copy the query to my code and insert the criteria using VBA
variables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[david epsom dot com dot au]

When you save a query, Access looks at the database
and decides how the query is going to run. This
information is saved with the query.

If your data changes, then the query plan may be
out of date, and your query might run more slowly
than it would if the query plan matched your
actual data.

Calculating the plan from the database can be
very slow (checking the indexes, record counts
etc) across a slow network.

Sometimes it is faster to run a new query plan,
sometimes it is faster to run an old query plan.

If the data is local, and the query plan is
simple, it might make no difference at all.

(david)