Stored Queries in Access

Marcus has written an article at 15Seconds which highlights the increase in performance that you can get when you use Stored Queries in the Access database rather than using Dynamic Queries.

Ofcourse everyone has heard about Stored Procedures in databases like MSSQL & Oracle etc., but what you may ask what are stored queries in Access databases. Its not a surprise that many ASP developers, including those who’ve been programming in ASP for quite a time now, are unaware of Stored Queries. 😉

To put it simply, stored queries are just SQL statements stored in an Access database. Now these Stored Queries can be of two types. Either they are plain that have just fixed SQL & values. Second type of stored queries can be SQL statements which accept values for conditions like the WHERE clause, at run time. Now the second type of stored queries is a replacement for dynamic queries, since these kind of queries are mostly used in database applications, be it for Web or Desktops.
Since these queries are already compiled & stored in the database, they tend to execute faster than the queries which are passed on to the database through a web script like ASP, because the database engine then compiles the SQL passed on to it to produce the results.

Though Stored Queries of Access are not as powerful as Stored Procedures in MSSQL or Oracle, they are still a way to go, & a better option than hardcoding SQL in the script & then passing it on to the Access database.

One of the tutorials that got me started onto this is the one by Faisal Khan at Star Developer. Incidently, Marcus also recommends reading this tutorial in his article.

So, I’d suggest to those who are into ASP programming, whether you  know or not about Stored Queries, Marcus’ Article is worth reading. 😀