Query compilation

[Mike]

I vaguely remember reading somewhere that MS Access compiles Saved Queries,
or their execution plan. Can anyone confirm whether this is the case?

I'm specifically interested in saved queries that are called via ADO or
ADO.NET from a web page, using Access 2000-2003 and the Jet 4.0 provider, as
opposed to ad hoc SQL.

Thanks

Mike

 

[Douglas J. Steele]

My understanding is that it's true (sort of) that queries are compiled, but
that it doesn't really make much of a difference. You can use saved queries,
though, externally: simply refer to them by name. (Note that queries that
are calling VBA function usually cannot be called from outside of Access)

What's the issue you're hoping to avoid?

 

[Mike]

I wasn't hoping to avoid any particular issue. It was more of an academic
enquiry. I tend to use saved queries if working with Access from web pages
because I like the fact that the SQL is all in one place - and they provide
protection from SQL Injection in a simpler (for me) fashion than other
methods. I was just wondering whether I could add compilation and improved
perfomance to the list of benefits when people ask me what on earth I am
doing.....

 

[Pat Hartman \(MVP\)]

The time to compile an individual query one time is not noticeable.
However, in a heavily used application or on a heavily used server, every
efficiency counts.