Access 2013 Bible

Appendix C: Understanding Query Performance and Database Corruption

IN THIS APPENDIX

Optimizing query performance

Handling database corruption

One of the most important aspects of working with Access is keeping your database healthy. In this appendix, you'll learn some of the best practices around building and maintaining your database, ensuring that it runs efficiently and error free.

Optimizing Query Performance

When you're analyzing a few thousand records, query performance is not an issue. Analytical processes run quickly and smoothly with few problems. However, when you're 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. That said, there are steps you can take to optimize query performance and reduce the time it takes to run your large analytical processes.

Normalizing your database design

Many users who are new to Access build one large, flat table and call it a database. This structure seems attractive because you don't have to deal with joins and you only have to reference one table when you build your queries. However, as the volume of data grows in a structure such as this one, query performance will take a nose dive.

Understanding the Access 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 ...