.Execute vs. .RunSQL - Speed vs. Size

  • Thread starter Thread starter Randy Fritz
  • Start date Start date
R

Randy Fritz

Hello NG,

I have a question - I am trying to speed up my Database and would like
to know which is faster - Dim a database and then use the db.execute method
or just use the docmd.RunSQL Method

TIAFAH

Randy
 
I don't know if one is faster than the other. This may be bad practice on
my part, but I've been using CurrentDB.Execute instead of DIMming a database
object and setting an object variable to it (like you need to when you use
db.Execute). I haven't had any problems yet...

I like the .Execute method because you can use the dbFailOnError option to
trap any errors that occur during execution. Besides, if you use RunSQL on
an action query and don't want Access to pop up a warning that you're about
to run a query that will modify your data, you have to turn off Access
warnings before you run it and hope you remember to turn the warnings back
on. With the .Execute method, no warnings pop up, not even to tell you the
execution has completed.
 
Randy said:
I have a question - I am trying to speed up my Database and would like
to know which is faster - Dim a database and then use the db.execute method
or just use the docmd.RunSQL Method
Click to expand...


I think db.Execute might be marginally faster, but probably
not enough to notice.

It is a good idea to Set db = CurrentDb() because you can
then use the RecordsAffected property to find out how many
records were added, deleted or modified. Certainly it's
somewhat faster to use the Set db ... if you are going to
Execute a sequence of action queries.

Note that Execute requires you to resolve any query
parameters in your code before executing the query. OTOH,
when Access processes the RunSQL method it will take care of
the parameters for you.

All this is mostly irrelevant if you really need to improve
performance. As far as query performance is concerned you
can gain orders of magnitude better improvement by making
sure you have all the Join, Where and Order By fields
indexed (but don't duplicate the automatic index on a
foreign key used in a Relationship). Another important
thing is to remove any **unnecessary** keywords such as
DISTINCT, etc.
 
Thank you Mark,

I didn't realize that the execute didn't give warnings - But I guess I
really never paid it that much attention. And just out of habit I have
almost always started out with DIMming db as DAO.Database and rs as
DAO.Recordset. even though when I have used the Docmd Objects they were not
even needed.

Randy
 
TY Marsh,

I didn't know about the recordsaffected property. they may become useful to
me later but for right now - I am just trying to pick up the speed in my
application without to much increase in the size.

Randy
 
Back
Top