SQL Improving Query Performance
1. Use Indexes.
2. Use All instead of UNION, OUTER UNION, EXCEPT and INTERSECT when you know there are no duplicate rows or when you know it does not matter whether duplicate rows are returned.
3. Omit the ORDER BY clause unless necessary.
4. Inline VIEWS or Temporary Tables?
If the query is “long” and you are accessing the results from multiple queries, then use a Temporary Table to store the result from the query so that you can reuse it. Since a VIEW is just a select statement, the results need to be generated each time the view is called. Although subsequent runs of the view may be efficient because the results are stored in the cache, a temporary table actually stores the data.
5. Use WHERE expressions with JOINS. JOINS without WHERE clause are often time consuming because of the ‘multiplier effect’ of the Cartesian Product.
6. Replace multiple references to the DATE, TIME, DATETIME and ToDay functions in a single procedure. When the proc SQL ConstDateTime option or the SQLConstDateTime system option is set, proc SQL evaluates the date-time functions once through and uses the same result once throughout the whole query.