Appendix C. Query Performance, Database Corruption, and Other Thoughts
One of the most important aspects of analyzing data with Access is keeping your database healthy. In this chapter, you will learn some of the best practices around building and maintaining your database, ensuring that it runs efficiently and error free. In addition, this chapter will teach you best ways to get help in Access when you need a push in the right direction.
Optimizing Query Performance
When you are analyzing a few thousand records, query performance is not an issue. Analytical processes run quickly and smoothly with few problems. However, when you are moving and crunching hundreds of thousands of records, performance becomes a huge issue. There is no getting around the fact that the larger the volume of data, the slower your queries will run. Even so, there are steps you can take to optimize query performance and reduce the time it takes to run your large analytical processes.
Understanding Access's Query Optimizer
Most relational database programs have a built-in optimizer to ensure efficient performance, even in the face of large volumes of data. Access also has a built-in query optimizer. Have you ever noticed that when you build a query, close it, and then open it again, Access sometimes shuffles your criteria and expressions? This is because of its built-in query optimizer.
The query optimizer is charged with the task of establishing a query execution strategy. The query execution strategy is a set ...
Get The Excel® Analyst's Guide to Access® now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
