What generic techniques can be applied to optimize SQL queries?

Question

What techniques can be applied effectively to improve the performance of SQL queries? Are there any general rules that apply?

Answer 1

• Use primary keys
• Avoid select *
• Be as specific as you can when building your conditional statements
• De-normalisation can often be more efficient
• Table variables and temporary tables (where available) will often be better than using a large source table
• Partitioned views
• Employ indices and constraints

Answer 2

Learn what's really going on under the hood - you should be able to understand the following concepts in detail:

• Indexes (not just what they are but actually how they work).
• Clustered indexes vs heap allocated tables.
• Text and binary lookups and when they can be in-lined.
• Fill factor.
• How records are ghosted for update/delete.
• When page splits happen and why.
• Statistics, and how they effect various query speeds.
• The query planner, and how it works for your specific database (for instance on some systems "select *" is slow, on modern MS-Sql DBs the planner can handle it).

Answer 3

The biggest thing you can do is to look for table scans in sql server query analyzer (make sure you turn on "show execution plan"). Otherwise there are a myriad of articles at MSDN and elsewhere that will give good advice.

As an aside, when I started learning to optimize queries I ran sql server query profiler against a trace, looked at the generated SQL, and tried to figure out why that was an improvement. Query profiler is far from optimal, but it's a decent start.

Answer 4

There are a couple of things you can look at to optimize your query performance.

1. Ensure that you just have the minimum of data. Make sure you select only the columns you need. Reduce field sizes to a minimum.

2. Consider de-normalising your database to reduce joins

3. Avoid loops (i.e. fetch cursors), stick to set operations.

4. Implement the query as a stored procedure as this is pre-compiled and will execute faster.

5. Make sure that you have the correct indexes set up. If your database is used mostly for searching then consider more indexes.

6. Use the execution plan to see how the processing is done. What you want to avoid is a table scan as this is costly.

7. Make sure that the Auto Statistics is set to on. SQL needs this to help decide the optimal execution. See Mike Gunderloy's great post for more info. Basics of Statistics in SQL Server 2005

8. Make sure your indexes are not fragmented. Reducing SQL Server Index Fragmentation

9. Make sure your tables are not fragmented. How to Detect Table Fragmentation in SQL Server 2000 and 2005

Answer 5

Use a with statment to handle query filtering. Limit each subquery to the minimum number of rows possible. then join the subqueries.

WITH
master AS
(
    SELECT SSN, FIRST_NAME, LAST_NAME
    FROM MASTER_SSN
    WHERE STATE = 'PA' AND
          GENDER = 'M'
),
taxReturns AS
(
    SELECT SSN, RETURN_ID, GROSS_PAY
    FROM MASTER_RETURNS
    WHERE YEAR < 2003 AND
          YEAR > 2000
)
SELECT *
FROM master,
     taxReturns
WHERE master.ssn = taxReturns.ssn

A subqueries within a with statement may end up as being the same as inline views, or automatically generated temp tables. I find in the work I do, retail data, that about 70-80% of the time, there is a performance benefit.

100% of the time, there is a maintenance benefit.

Answer 6

Some other points (Mine are based on SQL server, since each db backend has it's own implementations they may or may not hold true for all databases):

Avoid correlated subqueries in the select part of a statement, they are essentially cursors.

Design your tables to use the correct datatypes to avoid having to apply functions on them to get the data out. It is far harder to do date math when you store your data as varchar for instance.

If you find that you are frequently doing joins that have functions in them, then you need to think about redesigning your tables.

If your WHERE or JOIN conditions include OR statements (which are slower) you may get better speed using a UNION statement.

UNION ALL is faster than UNION if (And only if) the two statments are mutually exclusive and return the same results either way.

NOT EXISTS is usually faster than NOT IN or using a left join with a WHERE clause of ID = null

In an UPDATE query add a WHERE condition to make sure you are not updating values that are already equal. The difference between updating 10,000,000 records and 4 can be quite significant!

Consider pre-calculating some values if you will be querying them frequently or for large reports. A sum of the values in an order only needs to be done when the order is made or adjusted, rather than when you are summarizing the results of 10,000,000 million orders in a report. Pre-calculations should be done in triggers so that they are always up-to-date is the underlying data changes. And it doesn't have to be just numbers either, we havea calculated field that concatenates names that we use in reports.

Be wary of scalar UDFs, they can be slower than putting the code in line.

Temp table tend to be faster for large data set and table variables faster for small ones. In addition you can index temp tables.

Formatting is usually faster in the user interface than in SQL.

Do not return more data than you actually need.

This one seems obvious but you would not believe how often I end up fixing this. Do not join to tables that you are not using to filter the records or actually calling one of the fields in the select part of the statement. Unnecessary joins can be very expensive.

It is an very bad idea to create views that call other views that call other views. You may find you are joining to the same table 6 times when you only need to once and creating 100,000,00 records in an underlying view in order to get the 6 that are in your final result.

In designing a database, think about reporting not just the user interface to enter data. Data is useless if it is not used, so think about how it will be used after it is in the database and how that data will be maintained or audited. That will often change the design. (This is one reason why it is a poor idea to let an ORM design your tables, it is only thinking about one use case for the data.) The most complex queries affecting the most data are in reporting, so designing changes to help reporting can speed up queries (and simplify them) considerably.

Database-specific implementations of features can be faster than using standard SQL (That's one of the ways they sell their product), so get to know your database features and find out which are faster.

And because it can't be said too often, use indexes correctly, not too many or too few. And make your WHERE clauses sargable (Able to use indexes).

Answer 7

I think using SQL query analyzer would be a good start.

Answer 8

In Oracle you can look at the explain plan to compare variations on your query

Answer 9

Make sure that you have the right indexes on the table. if you frequently use a column as a way to order or limit your dataset an index can make a big difference. I saw in a recent article that select distinct can really slow down a query, especially if you have no index.

Answer 10

The obvious optimization for SELECT queries is ensuring you have indexes on columns used for joins or in WHERE clauses.

Since adding indexes can slow down data writes you do need to monitor performance to ensure you don't kill the DB's write performance, but that's where using a good query analysis tool can help you balanace things accordingly.

Answer 11

• Indexes
• Statistics
• on microsoft stack, Database Engine Tuning Advisor