Assign Query to Form or Orderby

[Thomas Kroljic]

All,

Do I gain any speed by assigning a query that is already created with the
proper sort order to the record source of a form vs. using the orderby on a
form? I'm wondering if I should create multiple queries with the
sort orders I need vs. using the orderby property on the form.
(Note: I set both parameters, RecordSource and Orderby on the Open Event of
the form using VBA)

I'm trying to increase the time it takes to open up a list of records based
on a users choice of sorting.

Thank You,
Thomas j. Kroljic

 

[Roger Carlson]

I assume you want to decrease the time it takes to open a list of records,
rather than increase, correct? ;-)

Yes, having the Order By in the query will be faster than imposing a sort at
the form level. And yes, for maximum efficiency, your best bet is to have
several stored queries with different sort orders. Once these queries have
been run, the Optimizer will store the most efficient way to run the query.

Ordinarily in a case like this, I would programatically create the SQL for
the RecordSource, but the optimizer does not optimize queries created in
code unless they are stored. So if time is a problem, stored queries are
better. The only problem lies in the case where the stored query is somehow
deleted. An elegant solution to this is to have code in the error trapping
that creates and saves whatever query is missing.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Thomas Kroljic]

Roger,
Thank you for your quick and informative reply. This is what I thought
but I wanted to hear it from someone else with more knowledge in Access than
me.
I will create the necessary queries ahead of time and associate the
appropriate one to the form's record source when the form opens.

As for the Optimizer, I'll run the queries ahead of time so the Optimizer
can store "the most efficient way to run the query".

Questions: are there any parameters (Settings) that I can/should adjust
for the Optimizer? Can I even see or get at the Optimizer?

Thank you,
Thomas j. Kroljic

 

[Roger Carlson]

Questions: are there any parameters (Settings) that I can/should
adjust

for the Optimizer? Can I even see or get at the Optimizer?

No, not really. You can help the optimizer by setting up your indexes
properly. There's really good information in the ADH (Access Developer's
Handbook by Ken Getz at al.) Otherwise, it just does what it does.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Thomas Kroljic]

Roger,
One last question on the Optimizer: Do I need to define the relationships
between tables within the Access Database (tools/relationships) for the
Optimizer to work? Currently I do not have anything defined in this window.

Thomas j. Kroljic

 

[Roger Carlson]

Yes and no. When you create relationships in the Relationship Window,
Access automatically creates indexes on the linking fields. This aids in
optimization. However, creating the indexes yourself will do the same
thing.

HOWEVER, in my opinion, EVERY database should have relationships established
in the Relationship Window (WITH REFERENTIAL INTEGRITY ON). If it doesn't,
it really isn't a relational database. With out relationships, it's just a
collection of tables waiting for inevitable data integrity issues to arise.
Creating the "relationships" in the queries is not good enough.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L