SQL Optimizations

Sometimes you need to fasten your sql(s) a bit, or sometimes they just take too much time to be tested.

Working along, there are things you can do that would reduce the run time. It is a shocker how much you can optimize your queries. I have been able to reduce total runtime for large queries of massive data to a 1/10!

A few things that will work on all platforms, but especially proven on Access & SQL Server are:

1-Eliminate all type conversions. Especially on data with a large number of rows, this will help a lot.

2-Do NOT use iif. Terrible performance.

3-ALWAYS put joins inside one another. Do not join two different sets of tables and join the joint tables.
WRONG (a INNER JOIN b) INNER JOIN (c INNER JOIN d)
CORRECT (((a INNER JOIN b) INNER JOIN c) INNER JOIN d)

4- If you have an external source or linked data, try to do the calculations or conditions or joins of external data on the external platform. Say you have Oracle-linked tables a,b, joined with local SQL Server tables c,d. Make sure you do all eliminations and joins of Oracle tables SEPERATELY, not joining with locals at first. Only after you take all you take from Oracle and do the calculations, take the result to the local and do the rest. For example:
CORRECT :
sql1- SELECT FROM (a INNER JOIN b ON whatever) into localTempTable
sql2- SELECT FROM localTempTable,c,d ......
WRONG:
SELECT FROM a,b,c,d WHERE .....

5-Try to use numbers for conditions, selects, comparing etc. rather than text. Numbers are indexed better than texts and looping through them is easier.

6-When doing multiple joins, first join the tables that eliminate most data. Do not put in the first step a join of huge tables with little or no filtering. KEEP YOUR INTERMEDIARY JOIN RESULT TABLES AS SMALL AS POSSIBLE.

7-When doing multiple joins, try not to join two large tables directly together. (actually this is an example of number 6)