File Library

Achieving good SQL performance can seem often seem like a magic act where you never know if the trick is going to work or not. Sometimes it works (performance is good) and sometimes it doesn't (performance is bad). What can you do to eliminate the guesswork and create SQL that consistently performs as desired? It's not magic but simply a matter of understanding how to best write (or rewrite) your SQL to help the Optimizer produce the best executions possible. It's also a matter of supporting our SQL with an effective set of indexes.

Part of the reason producing SQL that "just works" seems so hard is that we, as developers, often hold onto our coding styles long after they have become outdated or unwieldy. Particularly when maintaining SQL that was written by someone else, we often continue to use/reuse code that is no longer maintainable because it still works in some way and we are afraid to modify it. But is that really effective? Obviously, the answer is "no". What we need to do is to remove redundancy, eliminate unused functionality, and rejuvenate obsolete designs and constructs in our SQL through refactoring. Refactoring shouldn't happen just once but instead should happen every time the code is reviewed or modified in order to eliminate inefficiencies and increase quality. The original formulation can be a good guide post, but may now be obsolete or simply proven to be inefficient when measured against the requirements of your production environment.

It may also be necessary to evaluate indexing strategy in order to achieve the best performance footprint. Just like your SQL, index effectiveness needs to be evaluated again and again over time. Indexes that worked well for one set of SQL may need to be adjusted to provide the best performance as SQL is modified and added. How do you know which indexes are "best"? How do you determine when to use single column indexes or multi-column indexes?

In this session you will learn:
* Common ways to rewrite SQL that make it perform better and more consistently
* How to make your SQL easy to identify and test
* How and when to add or modify indexes
* How to determine the best choice of columns to include in an index and what order to place them
* How to determine the trade-offs of creating the "best" index