[AccessD] SQL vs DAO -- rattling cages

On Mon, Mar 31, 2003 at 03:20:33PM -0500, Susan Harkins wrote:
> I'm not really concerned about bloat at this point -- I'm just interested in
> opinions about which performs faster: DAO or SQL.

Susan, 

Optimized, compiled SQL is *much* faster than DAO for processing large 
counts of rows. Unoptimized SQL still seems to be faster on the few 
occasions when I've bothered to time it. My rule of thumb is to go ahead 
& use ADO or DAO if I already have a recordset open and I'm only working 
with a few rows. Otherwise, I go with SQL.

The difference between optimized and unoptimized SQL is important. A 
sufficiently complex SQL statement operating on reasonably large tables 
may take all night to run if it is executed with the database Execute 
method or in a temporary TableDef. Put the same SQL statement in a query 
so the Rushmore optimizer can tweak it, and it will take about ten 
minutes. 

SQL is a specialized sub-language that does only a few things, and has 
been bummed to do them well and fast. VBA, in combination with the 
object hierarchy of your choice, is a general purpose language that 
would probably break if it were as tense as SQL. SQL is like a Peterbilt,
good for hauling lots a data long distances over open roads but awkward 
for narrow streets and tight corners. VBA is like a Mitsubishi; not as 
efficient as a Peter for long hauls, but able to take smaller amounts of 
data more places and do more things with it. 

Bob Hall