Combo Box and Form's Efficiency in Access 2007

[spenz]

Just a quicky question, Which is significantly faster when used as a rowsource for a combo box or a form; a query based inside the form(SQL) or a separately made query? Thanks in advance

Note: Row Source Type are both Table/Query

 

[pbaldy]

Should be no difference. SQL in the rowsource property is saved as a hidden query anyway.

 

[spenz]

Should be no difference. SQL in the rowsource property is saved as a hidden query anyway.

Thanks for replying. Yes, that's exactly what i thought but a friend of mine said he had read a book that clearly states using a saved query as a row source is much more efficient than using the SQL SELECT since if you use the SQL as your row source it will be optimized by Rushmore on the fly thus degrading performance. I don't know anything about rushmore so, i just said, well ok i got to consult some expert here.

 

[pbaldy]

Perhaps he meant SQL built and executed from VBA, which would never be optimized. I can say for certain that SQL in the rowsource property gets saved as a hidden query. I assume, but am not certain that since it is saved as a query, it is also optimized.

 

[LPurvis]

Just to be clear though - an assigned SQL statement would still be optimised, but it would have to be done so at runtime, rather than having the pre-compiled execution plan of a saved query.
(The plan creation time is usually small compared to common disk fetches of data - but every bit can help... though out of date plans can hamper a fetch. Stored plans are reset with a compact)

 

[LPurvis]

FWIW Paul's right though. The hidden queries are created and are even readily identifiable by their name (it's a decent naming convention

 

[spenz]

Just to be clear though - an assigned SQL statement would still be optimised, but it would have to be done so at runtime, rather than having the pre-compiled execution plan of a saved query.
(The plan creation time is usually small compared to common disk fetches of data - but every bit can help... though out of date plans can hamper a fetch. Stored plans are reset with a compact)

Thanks for the feedback mate. I'm not sure if i get your opinion right mate but basing from your statement above this could be what my friend meant..

SQL statement = Optimization happens at runtime.
Saved Query = This one is already optimized in its compiled state.

So which explains there is indeed a slight speed advantage when using a saved query.

 

[spenz]

Perhaps he meant SQL built and executed from VBA, which would never be optimized. I can say for certain that SQL in the rowsource property gets saved as a hidden query. I assume, but am not certain that since it is saved as a query, it is also optimized.

No,not an SQL executed from a VBA. He said it's an SQL Select statement in the Row Source which is as you described earlier is a hidden query.

 

[LPurvis]

>> So which explains there is indeed a slight speed advantage when using a saved query

Potentially - but that would also include the hidden saved query definitions that Access creates when using a SQL statement in the design of a form (using and executing that is).
SQL statements assigned at runtime would be optimised at runtime.