Who should attend?
- Experienced DBAs should attend all three sessions, but only DBAs should attend Troubleshooting & Tuning.
- Experienced Developers should attend the Writing Optimal SQL and Explain Plan seminars.
What will they learn?
- Developing a global view point and a strategy for minimising work while recognizing the conflict between local and global optimization.
- A quick review of indexes, including some of the less common uses and common errors in index design.
- Appreciating the need to investigate and understand the data and business requirements.
- How to translate business requirements into SQL.
- The need for clarity in code, and a suggested set of standards.
- Writing SQL that does minimal work, and a model for investigating and re-engineering badly performing statements.
- How to use less well-known features of SQL.
- The mechanics (and costing) of nested loop and hash joins.
- Avoiding abuse of views; the problems of predicates and tuning distributed queries.
- How to fix code without changing it.
- Exploiting internal mechanisms.
- The threats and proper use of hints.
Additional Seminar InformationThis tutorial has two targets. First: how do you improve the performance of a production system by attacking inefficient SQL; secondly: how do you design a system so that you can get data into and out of it efficiently. For most people, the design has already happened, nevertheless you need to be aware that the two targets overlap significantly - as you struggle to address a particularly inefficient piece of SQL, you need to be aware that there are structural features of the database that may help you find a cost effective solution despite the problems imposed by the SQL itself.The course will focus on the SQL - falling back to structural issues only as an aid to improving the mechanical efficiency of the SQL solution - but we will spend some time explaining the pros and cons of various structural options as they become relevant to the SQL.
The course will cover methods of reviewing data distribution patterns, use of indexes, use of views (stored and inline), analytic functions, subquery factoring, as well as statistics, hints, and a brief review of execution plans. |
