Hardcore SQL Performance Tuning

I manage performance improvements project. The main goal is to achieve good performance for large data (up to 1,000,000 records in a single table). I hope some tips will help you to improve database response time.

UNION ALL instead OR
You can use UNION ALL instead OR in queries. UNION does not perform the SELECT DISTINCT function, which saves a lot of server resources from being using. Moreover, some DB servers could optimize such queries (or even execute them in parallel). However, query become more complex.

For example

SELECT name FROM users WHERE level > 0 or is_admin = 1

May be rewritten as

SELECT name FROM users WHERE level > 0
UNION ALL
SELECT name FROM users WHERE is_admin = 1

The second query works much more faster on large tables. This is true for SQL Server and Oracle as well.

Denormalized tables (or views)
Denormalized table or view combines data from different tables into a single. As a result, no JOINs required in queries and this improves performance. This tip helpful for various lists.

Tables splitting
Sometimes one large table could be splitted on several smaller tables. This method may work pretty well with UNION ALL. Sure, queries become more complex and there will be several places to store almost the same data, however performance will be increased. If you don’t know other ways – try this.

Hints and indexes
Hints are great, especially for Oracle. It is not recommended in general to use hints for SQL Server, since it may even slower query execution time. Hint tells DB server what index should be used for particular query.

Oracle hint example:

SELECT /*+ INDEX (users USERS_H_R) */ name FROM users

These techniques are unusual. But if you stuck, try them.

One thought on “Hardcore SQL Performance Tuning”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s