Re: .Execute vs. .RunSQL - Speed vs. Size

From: Marshall Barton (marshbarton_at_wowway.com)
Date: 03/01/05 

Date: Mon, 28 Feb 2005 21:16:20 -0600

Randy Fritz wrote:
> 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

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. 

-- 
Marsh
MVP [MS Access]

Relevant Pages

  • Re: Newbie question about formula based values
    ... if the query hangs or if Access crashes or is someone restarts ... If you want to execute the code from your web site, you can do that as well. ... If your web site can execute SQL code against an Access database, ... In Windows scheduler, create a new scheduled task based on Microsoft ...
    (comp.databases.ms-access)
  • TIP #308: Twylites concerns
    ... If, as you say, the simple change of adding a place for [$db execute] ... likely the result of a query against a system catalog. ... > especially for a pure-Tcl implementation for a specific database. ... 'execute' functionality that you discussed above address this concern? ...
    (comp.lang.tcl)
  • Re: disable index while doing insert into
    ... source and target table have multiple indexes, ... to execute this query but when i've deleted the indexes on target ... This is a price you have to pay on any database not just Sybase. ...
    (comp.databases.sybase)
  • Re: Execute queries in back-end database via ADO !?!
    ... > I found that by defining an ADO connection to my back end database, ... execute a SQL query stored in my back end database. ... As long as your data is stored in an MDB file all query processing is ... What program would you expect to execute the query on the back end? ...
    (microsoft.public.access.queries)
  • Re: .Execute vs. .RunSQL - Speed vs. Size
    ... I didn't know about the recordsaffected property. ... > Execute a sequence of action queries. ... > parameters in your code before executing the query. ... > can gain orders of magnitude better improvement by making ...
    (microsoft.public.access.formscoding)