SQL Optimization Tips.
In honor of International Talk Like A Pirate Day, Pew Pew Laser Blog brings you this message from the SQL Pirate.
Yar me heaties! I were diggin' around in me old booty-chest o' code and I found this here venerable list of SQL tips. These here tips be for keepin' yer SQL queries runnin' faster than me crew towards the last bottle o' rum. Keep it close to ye vest.
- Obviously, putting indexes on your destination tables will help your queries to run faster. Remeber to also check your source and datamart tables for appropriate indexes.
- You can have several columns indexed separately, or combine them into a single index. The SQL engine will typically combine indexes as needed to run queries.
- “Be positive in your queries.” WHERE IN is faster than WHERE NOT IN. You can use MINUS (similar to UNION ALL) to exclude rows from your results. MINUS is also good for comparing the differences between 2 tables or result sets.
- Suppose you have a large subquery, and some excluding conditions such as account number or date that require a join. It is much more efficient to use those excluding conditions inside your subquery rather than outside it.
- Functions are your friend. They are fast, fast, fast and reuseable.