Here are initial tips for writing efficient/ cost-effective 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 Delhi and born before January 1, 1960, you could write the following query:
SELECT FirstName, LastName FROM EMPLOYEE WHERE State = ‘Delhi’ AND DateOfBirth < ‘1960-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 = Delhi will occur a number of times and the database system will go on to check the second condition, DateOfBirth < ‘1960-01-01’. If you swap the conditions around, the least likely condition (DateOfBirth < ‘1960-01-01’) is evaluated first:
SELECT FirstName, LastName FROM MemberDetails WHERE
DateOfBirth < ‘1960-01-01’ AND State = ‘Delhi’;
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
= ‘Delhi’ OR DateOfBirth < ‘1960-01-01’;
If Delhi is true, and it is true more often than DateOfBirth < ‘1960-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; -
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 EMPLOYEE WHERE
EMPID = (SELECT EMPID 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 EMPLOYEE WHERE EMPID IN (SELECT EMPID 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 VIRENDRA YADUVANSHI and whose MemberId is 101, then it makes sense to search via the MemberId because it’s much faster.
WikiDBA 