In VBA, SQL vs. Query

[Magnivy]

Hello,

In my VBA code, would a SQL query run quicker than kicking off an Access
Query? I have a table with 2.5 million records and I have an update query
that seems to take a long time when I run it through VBA. I'm wondering if
can speed it up by using SQL in VBA instead of kicking off a user-defined
query.

Thanks very much for your help.

Magnivy

 

[Robert Morley]

The Access query will pretty much always be the fastest method of doing
things, since Access has had the opportunity to analyze the query and decide
on the fastest way to run it.

On rare occasions, you may get better performance out of a SQL query, due to
a poor query plan on Access' part, and sometimes you can also get better
performance by manually looping through a recordset and updating it
yourself, but both of these occasions are very rare.

You may want to do a Compact & Repair on your database to make sure that
everything's running as efficiently as it can, if you haven't already.

That said, 2.5 million records is a fair bit for Access to handle. Not
beyond its capabilites, certainly, but you should expect it to take some time.



Rob

 

[John W. Vinson]

Hello,

In my VBA code, would a SQL query run quicker than kicking off an Access
Query? I have a table with 2.5 million records and I have an update query
that seems to take a long time when I run it through VBA. I'm wondering if
can speed it up by using SQL in VBA instead of kicking off a user-defined
query.

Thanks very much for your help.

Magnivy

An update query IS a SQL query. All queries use SQL as their language, if
that's what you mean!

If the table is actually stored in SQL/Server, then a Passthrough query
executed on the server (rather than an Access stored query ) would indeed be
faster, but even on really big iron an update query updating 2.5 million
records will take some time.

John W. Vinson [MVP]

 

[Magnivy]

Thanks Robert!

The Access query will pretty much always be the fastest method of doing
things, since Access has had the opportunity to analyze the query and decide
on the fastest way to run it.

On rare occasions, you may get better performance out of a SQL query, due to
a poor query plan on Access' part, and sometimes you can also get better
performance by manually looping through a recordset and updating it
yourself, but both of these occasions are very rare.

You may want to do a Compact & Repair on your database to make sure that
everything's running as efficiently as it can, if you haven't already.

That said, 2.5 million records is a fair bit for Access to handle. Not
beyond its capabilites, certainly, but you should expect it to take some time.



Rob

 

[Magnivy]

Thanks John!

An update query IS a SQL query. All queries use SQL as their language, if
that's what you mean!

If the table is actually stored in SQL/Server, then a Passthrough query
executed on the server (rather than an Access stored query ) would indeed be
faster, but even on really big iron an update query updating 2.5 million
records will take some time.

John W. Vinson [MVP]