O'Reilly logo

The Excel® Analyst's Guide to Access® by Michael Alexander

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required