Top Tips for Efficient Queries

Moayed — Thu, 05 Jun 2008 01:17:00 GMT

Top Tips for Efficient Queries

· When using AND, put the condition least likely to be true first.

The database system evaluates conditions from left to right, subject to operator precedence. If you have two or more AND operators in a condition, the one to the left is evaluated first, and if and only if it’s true is the next condition evaluated. Finally, if that condition is true, then the third condition is evaluated. You can save the database system work, and hence increase speed, by putting the least likely condition first. For example, if you were looking for all members living in New State and born before January 1, 1940, you could write the following query:

SELECT FirstName, LastName

FROM MemberDetails

WHERE State = ‘New State’ AND DateOfBirth < ‘1940-01-01’;

The query would work fine; however, the number of members born before that date is very small, whereas plenty of people live in New State. This means that State = New State will occur a number of times and the database system will go on to check the second condition, DateOfBirth < ‘1940-01-01’. If you swap the conditions around, the least likely condition (DateOfBirth < ‘1940-01-01’) is evaluated first:

SELECT FirstName, LastName

FROM MemberDetails

WHERE DateOfBirth < ‘1940-01-01’ AND State = ‘New State’;

Because the condition is mostly going to be false, the second condition will rarely be executed, which saves time. It’s not a big deal when there are few records, but it is when there are a lot of them.

· When using OR, put the condition most likely to be true first.

Whereas AND needs both sides to be true for the overall condition to be true, OR needs only one side to be true. If the left-hand side is true, there’s no need for OR to check the other condition, so you can save time by putting the most likely condition first. Consider the following statement:

SELECT FirstName, LastName

FROM MemberDetails

WHERE State = ‘New State’ OR DateOfBirth < ‘1940-01-01’;

If New State is true, and it is true more often than DateOfBirth < ‘1940-01-01’ is true, then there’s no need for the database system to evaluate the other condition, thus saving time.

· DISTINCT can be faster than GROUP BY.

DISTINCT and GROUP BY often do the same thing:

limit results to unique rows. However, DISTINCT is often faster with some database systems than GROUP BY. For example, examine the following GROUP BY:

SELECT MemberId

FROM Orders

GROUP BY MemberId;

The GROUP BY could be rewritten using the DISTINCT keyword:

SELECT DISTINCT MemberId

FROM Orders;

· Restrict join results.

The less information the database has to pull out, the better. This is particularly true with joins, which are very expensive time-wise. Put the restrictive condition on the outer table of the join. The outer table is the table on the right of the INNER JOIN keyword. For example, in the following code, the table with the restriction—a condition in the WHERE clause restricting results—is the MemberDetails table, which is the outer table in the join:

SELECT MemberDetails. MemberId, FirstName, LastName

FROM Orders INNER JOIN MemberDetails

ON Orders.MemberId = MemberDetails. MemberId

WHERE MemberDetails. DateOfBirth BETWEEN ‘1900-01-01’ AND ‘1970-12-31’;

· Use IN with your subqueries.

When you write a query similar to the following, the database system has to get all the results from the subquery to make sure that it returns only one value:

SELECT FirstName, LastName

FROM MemberDetails

WHERE MemberId = (SELECT MemberId FROM Orders WHERE OrderId = 2);

If you rewrite the query using the IN operator, the database system only needs to get results until there’s a match with the values returned by the subquery; it doesn’t necessarily have to get all the values:

SELECT FirstName, LastName

FROM MemberDetails

WHERE MemberId IN (SELECT MemberId FROM Orders WHERE OrderId = 2);

· Avoid using SELECT * FROM.

Specifying which columns you need has a few advantages, not all of them about efficiency. First, it makes clear which columns you’re actually using. If you use SELECT * and actually use only two out of seven of the columns, it’s hard to guess from the SQL alone which ones you’re using. If you say SELECT FirstName, LastName...then it’s quite obvious which columns you’re using. From an efficiency standpoint, specifying columns reduces the amount of data that has to pass between the database and the application connecting to the database. This is especially important where the database is connected over a network.

· Search on integer columns.

If you have a choice, and often you don’t, search on integer columns.

For example, if you are looking for the member whose name is William Doors and whose MemberId is 13, then it makes sense to search via the MemberId because it’s much faster. In the Film Club database, it’s also the primary key column, so there’s even more of a speed boost.

Moayed Mohammed

Top Tips for Efficient Queries