Introduction

In this chapter we will be adding those thoughts that do not fit in anywhere else in the book. We will also be adding a few concluding remarks on what we consider to be the big picture regarding SQL.

Common Rules

One of the most important rules to follow when designing any query or table is to consider how the information will be used. While we have shown that there is a lot of power in SQL, we have glossed over the important aspects of table and query optimization. The most important thing about SQL is that its only purpose is to access data. It is a tool, not the end result. If a query runs slowly because of poor design or because table linkages are not thought out, the user will become frustrated with the amount of effort needed to get the information and will be less inclined to use the program. It doesn’t matter if you have a beautiful query if the user does not use it. Remember that the user does not see the code, just the end result.

To this end, there are a few basic rules about query design that need to be stressed. First, it is always better to filter first, then perform needed calculations on the data. There is no need to perform the calculations and waste time when the calculation is not going to be used.

Second, filter first, then link secondary tables. The thoughts regarding calculations apply here, too.

Third, temporary tables and views are extremely powerful when you are using a subset of the data over and over. Why bother to rerun filters and calculations when you can have the data put away in a temporary location for very quick access?

Fourth, just because you can grab all the fields in all the tables does not mean you should grab all the fields. If you only need one field from a 200-column table, it makes far more sense to only take the field you need and reduce your overhead by an order of magnitude. Sure, you can use the * shorthand to grab all the fields, but you will find that what you save in laziness will cost you in processing.

Fifth, if you need to do a calculation more than once, store the result and don’t repeat the calculation. For example, if you need the first occurrence of a particular string after a specific character and you use this same value more than once, set a temporary field to the value and keep using it rather than recalculating it every time. It is amazing how much faster queries go with this type of optimization.

Summary

In this book we have covered the basics of SQL and how it can be used in Access. But there is far more to the SQL story. Microsoft and Oracle have developed versions of SQL that are designed to get every possible degree of speed out of accessing data. While most of the optimization tricks and special features of Oracle and Microsoft SQL are beyond the scope of this book, there is a wealth of power in these programs that we have not begun to address. This book can be used to reach a good plateau of expertise that will greatly improve your skill with databases. It can also be used as a stepping-stone to additional knowledge.