Here are some things that can improve the performance of your SQL queries:

DISTINCT

SELECT DISTINCT, which removes duplicates from the selected data, is expensive, because it requires sorting, and maybe even a temporary table. Only use it if there really can be duplicates in the returned data. Sometimes you get duplicates because the query is incorrect, for example when a join condition is missing. Using DISTINCT in that case hides the problem, with a big performance penalty.

UNION ALL

Use UNION ALL instead of UNION if your server supports it and you don't need to filter out duplicates. The reason is the same as with DISTINCT.

SELECT *

Avoid selecting all fields using *. If you don't need all fields, then you request more data than necessary, which causes unnecessary network traffic and CPU load. It also takes time for the server to determine the field names, so even if you want the data from all fields you should not use *. Also, if you are only interested in if the query returns data or not, then select only one field.

Thanks mblase for reminding me about the time it takes to look up field names

Viewing the execution plan

This is a powerful command that tells the database server to explain how it executes a query. You can find out if there are full table scans (usually a Bad Thing), and check the effect of adding or removing indexes. The actual command varies depending on which server you use. For example, Microsoft SQL Server has a Query Optimizer program.

Log in or registerto write something here or to contact authors.