Common Pitfalls

Many common mistakes can cause unnecessary bottlenecks when you are running queries. Following is a list of these common mistakes and what to do to correct them.

Including expressions in query output. Placing expressions, such as an IIf on an output field of a query can cause optimization problems if the query is used as the input to another query. For example, consider the following two queries, where the first query is saved as Q1:
```
SELECT IIf(ShipVia = 2, 'United Package', 'Other') 
	AS ShipperName 
FROM Orders;

SELECT * 
FROM Q1 
WHERE ShipperName = 'United Package';
```
Because Microsoft Jet can’t optimize the IIf expression in the first query, it can’t optimize the second query. Expressions can get buried so far down in a query tree that it’s easy to forget about them. If expressions are necessary in the output, try to place the expression in a control on a form or report.

The following query replaces the previous two queries and is the optimal way to write this query:
```
SELECT * 
FROM Orders 
WHERE ShipVia = 2;
```
Using GROUP BY on too many fields. When you’re creating a totals query, use the GROUP BY clause on as few fields as necessary to achieve the query’s goal. The more fields in the GROUP BY clause, the longer the query takes to execute. Make sure you don’t include extraneous fields.

Using GROUP BY in same query as a join. If possible, place a GROUP BY clause on a table and then join it to another table, rather than joining the two tables and using the GROUP BY clause in the same query as the join. For example, instead of this query

SELECT 
	Orders.CustomerID, 
	Count(Orders.OrderID) AS CountOfOrderID
FROM Customers 
INNER JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID;

you may benefit by using two separate queries, as follows, where the first query is saved as SelectWithGroupByQ1:

SELECT Customers.CustomerID 
FROM Customers
GROUP BY Customers.CustomerID;

SELECT 
	Orders.CustomerID, 
	Count(Orders.OrderID) AS CountOfOrderID
FROM SelectWithGroupByQ1, INNER JOIN Orders
ON Q1.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID;

Not including indexes on join fields. When you’re joining tables, always try to index the fields on both sides of a join. This can speed up query execution by allowing more sophisticated join strategies such as index and index-merge joins. The use of indexes also provides better statistics. For more information, see the “Join Strategies” section earlier in this chapter.
Under-indexing fields. In appropriate circumstances, for example where the database is used solely for decision-support types of applications, you can place an index on all fields that have unique values and are used in a join or as a restriction. By using Rushmore query optimization, Microsoft Jet can take advantage of multiple indexes on a single table. This makes indexing many fields advantageous. However, keep in mind that adding indexes to fields can cause performance to suffer.
Using Count(fieldname) instead of Count(*). When you have to determine the number of records, you should use Count(*) rather than Count(fieldname) because there are Rushmore optimizations that allow Count(*) to be executed much more quickly than Count(fieldname).