Practical Ways to Improve the Performance of SQL Queries

Knowing how to improve SQL query performance is an important skill, especially when working with with large databases. In this article, you'll learn how to write more efficient SQL queries to get results faster.

The biggest difference between SQL and other languages is that SQL is a non-procedural language. In a non-procedural language you specify the results that you need but not the methods used to get it. The advantage of a non-procedural language is that it is easier to write programs, therefore it is common for non-programmer business users to generate reports from SQL queries. As the expertise of users improves and the complexity of reports increases, the performance of queries can quickly deteriorate in terms of memory use and speed. For this reason it is important to understand a few methods to improve the performance of reports in SQL.

Query optimizers

There are two major query optimizers that come with an SQL database. Historically databases used syntax-based query optimizers in which the syntax of the SQL query determines the performance of the query. More recent versions of SQL use statistics-based query optimizers, in which the method used to execute a query is automatically based on the volume of data in the tables used in the SQL query. By default most of SQL databases use statistics-based query optimizers. Under certain circumstances, however, if the query is not performing well a database like Oracle allows a syntax-based query optimizer to be used, giving the developer better control over the way that a query is executed. Writing the query in a specific manner can improve the query.

Five tips to improve the performance of SQL queries

1. Every database has a different way of executing a query, called a query execution plan. If a complex query is taking an inordinate amount of time to run, research the query execution plan of your particular implementation of SQL. If you see a full table scan on a large table try to index the attribute through which the table is accessed. Indexing the table helps to locate a row quickly, but can actually decrease performance for smaller tables as it may lead to reading both the index table and data table.
2. Use the actual column names in the SQL query instead of selecting all columns (using SELECT *) FROM a table, so that only necessary columns are selected.
3. Try to avoid correlated subqueries, because these can significantly decrease the speed of execution. Instead of a correlated subquery, you can use tables with a JOIN operator. Of course, decision about a choice: a correlated subquery or a JOIN depend on data you want to retrieve from a database.
4. Avoid the use of IN, use the EXISTS clause in the main query instead of a subquery, and when you use UNION, try to use the UNION ALL clause instead of UNION alone.
5. Use the WHERE clause effectively. If you are trying to avoid certain column values, do not use the NOT condition and instead use BETWEEN operator. Similarly if you are using a LIKE clause with a wild card, be sure to design the statement so that it selects the fewest number of rows.

If in doubt always use the query execution.

Five tips to improve report performance

1. Reports use lot of CPU resources so it is important that the resource availability is taken into account before running reports. Reports should not disrupt mission critical online jobs. It is important to balance workloads by running time-intensive reports during off business hours to optimize resource utilization.
2. While most of reports are read-only queries, make sure the database concurrency does not become a bottleneck when running reports. Some databases perform read locks and some may perform page-level locks. Locks impact the concurrency. Avoid locking data tables in reports.
3. If the database supports report queues, make sure the long-running report queue is different from the short-running report queue. There should not be a clash between an invoice report that is required immediately to generate an invoice and a daily sales report that is probably not mission critical. The customer-facing report queue should be different and should be run at a higher priority.
4. Avoid multiple run backs to the database from a report process. Your report depends on database access and every time the database is accessed as much data as possible should be obtained for the reports. Round trips to the database may slow down report performance, so these should be minimized.
5. If the enterprise allows you, try to use a separate reporting server that has real-time replication of data from the live database. A separate report database server helps improve online critical reports, improving the speed of decision making for the enterprise.

Conclusion

While the above tips specifically talk about SQL query and report performance, it is also important that the database is tuned for performance. For example, an Oracle database gives what is known as an AWR report on performance, identifying all parameters that need to be tuned, and a good database administrator will be able to perform the tuning much better. Implementation of the recommendations through is an important aspect of maintaining performance levels and has to be done by the database administrator continuously. Continuous improvement is the key to database tuning.