[AccessD] Which is fasted as a list control's RowSourceproperty?

Hi Susan,
A fixed query is precompiled, making it faster than a pure SQL statement.
That's why Access's performance-analyser in older versions recommended using
stored (fixed/persisted) queries as record-sources and control-sources.
Newer versions of Access - I don't remember when, but I think after 97 -
doesn’t make this recommendation.
Reason: not that pure SQL is faster, but because Access creates the
persisted and precompiled query by itself.
I always use pure SQL statements because I don't want to fill up my pane of
query-objects in Access, and because it makes my forms and reports much more
immune to cleaning up of queries: ever tried to delete an "obsolete" stored
query, which was actually used by some component? I guess.

Asger


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Susan Harkins
Sendt: 2. april 2007 01:26
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Which is fasted as a list control's RowSourceproperty?

Well, that's interesting. I couldn't really identify any of mine to a
specific control object though. 

So, do you think there's no appreciable difference between the two then --
as a performance issue? It seems to me, that pure SQL should always be
faster, regardless of how Access communicates it to Jet. 

Susan H. 

Same:
Behind the scenes Access creates a fixed query for the literal string.
You can see the hidden fixed query which is prefixed by a "~" using
following code in a form module:
    Dim dbs As DAO.Database, qdf As DAO.QueryDef
    Set dbs = CurrentDb
    For Each qdf In dbs.QueryDefs
        Debug.Print qdf.Name
    Next qdf

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Susan Harkins
Sendt: 1. april 2007 23:51
Til: 'Access Developers discussion and problem solving'
Emne: [AccessD] Which is fasted as a list control's Row Source property?

A literal SQL string or a fixed query? 

Susan H. 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
 



































































































































































-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.24/741 - Release Date: 3/31/2007
8:54 PM
 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com