Which runs faster DoCmd.RunSQL or db.Execute?

[m6]

Dear Friends,

Which runs faster DoCmd.RunSQL or db.Execute?

I prefer db.Execute because I use strSQL to load a tracer log file, a screen tracer text box. Not that you can do it also with DoCmd.RunSql, but I ran into a Access version bug with docmd and I tend to avoid it.

What's the opinion/consensus?

Tx,
m6

 

[JeremyNYC]

Dunno, but I'd be interested to read some test results. Just set up a loop with each one, and grabthe time before and after the loop starts. Make sure you start the db fresh before running each test. And it'd be helpfuo to see the specs of the system it's running on.


Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[psemianonymous]

CurrentDB.Execute() is recommended by the MVPs because of its error-handling abilities. I doubt that there is any significant difference between the two.

Also consider DoCmd.OpenQuery "qryName" as that may actually benefit your performance, and gives a neat progressbar at the bottom, and is escapable. Note that this should only be used with a pre-set SQL statement, like "DELETE * FROM TEMP_TABLE" or something similar, and not dynamically-generated SQL.


But this performance issue is a nonissue. Look instead to field indexing, INNER JOINs, and the like if you're trying to boost performance. You can get orders of magnitude faster, rather than a 2-10% boost that you (may) be looking at above.