Tech Blog

SQL statements are used to retrieve data from databases. To optimize/tune the process of retrieving data, we must consider performance. There are many different statements that can be written to retrieve the same data, and in order to optimize or tune to performance of retrieval, there are some different queries we can choose to us.

Below is a list of ten dos and don'ts in order to optimize your SQL performance:

1. SELECT actual column names instead of using the * function

   ie.    
           SELECT column_name 
           FROM title_name   
   
             instead of  
   
           SELECT * 
           FROM title_name

2. Use HAVING as a clause to filter selected data

   ie.    SELECT column_name  
         FROM title_name 
         HAVING specific_attributes 

3. Try to minimize the amount of sub query within in the query
4. Be careful using the operators EXIST, IN and table joins correctly
   • IN is one of the slowest operators- so avoid using it
   • IN is most appropriate when most of the information is held within the subquery
   • EXISTS is most appropriate when most of the information is held in the query
5. In tables that have one-to-many relationships, use EXIST instead of DISTINCT
6. Use UNION ALL instead of UNION
7. Be careful using the WHERE clause, and try to make WHERE statements less specific so it has less to process
8. Use DECODE to avoid repetitively scanning the same rows
9. Avoid storing large binary objects directly into the database, instead create a separate file and store the file path to minimize processing
10. Syntax Rules:
    • Single space all SQL verbs
    • Single case for all SQL verbs
    • Being all SQL verbs on a new line (unlike my examples above)
    • Align SQL verbs left or right all starting with the verb

These ten basic rules, courtesy of http://beginner-sql-tutorial.com/sql-query-tuning.htm will help optimize and tune your SQL so that the data can be retrieved and processed most efficiently.