Store queries instead of using dynamic SQL text - especially low memory machines

DAO makes it easy to create SQL statements on the fly. As a general guideline, it is better to create stored queries and to open those directly rather than use SQL text in your code. The advantages include:

• faster performance on low memory machines because the query compiler won't be needed and hence won't take away valuable memory from your application. Machines with plenty of memory will find that query compilation step is fairly fast, so don't be mislead by this if you intend the final application to be used on low memory machines (or high memory machines with lots of other applications running);

• faster performance because the query compilation is avoided;

• better maintainability because the query is not buried in amongst the rest of your source code;

• better maintainability because you can modify the query to adapt to data layout changes without modifying the source code;

• easier auditing because the queries are easier to find; and

• better security capabilities because you can take advantage of "Run with Owner Access permissions" to grant users limited access to tables that they otherwise reach.

The example code allows you to time a stored versus a dynamic query. However it is not really designed to help you evaluate the hit on the first query compilation, for example. However, running it on a low memory machine (e.g. 6-8Mb) should give you good feel for the trade-offs involved.

See also tip #8 and tip #19.