SQL tuning is a complicated task and to cover it adequately requires a full-length book of its own – actually, perhaps several if you use multiple DBMS products. That said, there are some good high-level SQL tuning suggestions that should apply regardless of the DBMS you are using. Well, as long as it supports SQL!
Here are some helpful rules of thumb:
- Create indexes to support troublesome queries.
- Whenever possible, do not perform arithmetic in SQL predicates. Use the host programming language (Java, COBOL, C, etc.) to perform arithmetic.
- Use SQL functions to reduce programming effort.
- Look for ways to perform as much work as possible using only SQL; optimized SQL typically outperforms host language application code.
- Build proper constraints into the database to minimize coding edit checks.
- Do not forget about the “hidden” impact of triggers. A DELETE from one table may trigger many more operations. Although you may think the problem is a poorly performing DELETE, the trigger is really the culprit.
Furthermore, a large part of the task of tuning SQL is identifying the offending code. A SQL performance monitor is the best approach to identifying poorly performing statements. Such a tool constantly monitors the DBMS environment and reports on the resources consumed by SQL statements.
Some DBMSs provide rudimentary bundled support for SQL monitoring, but many third-party tools are available. These tools provide in-depth features such as the ability to identify the worst-performing SQL without the overhead of system traces, integration to SQL coding and tuning tools, and graphical performance charts and triggers. If you find yourself constantly being bombarded with poor performance problems, a SQL monitor can pay for itself rather quickly.
At a high-level then, the guidance I want to provide is as follows:
- Ingrain the basics into your development environment. Make sure that not just the DBAs, but also the application developers understand the high-level advice in the bulleted list above.
- Build applications with database performance in mind from the beginning.
- Make sure that you have an easy, automated way of finding and fixing poorly performing SQL code.
Sound simple? Maybe it is, but many organizations fail at these three simple things…