TargetProcess. Agile Project Management Software

Jan 12, 2005 / 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.

posted by Michael Dubakov @ 8:36 AM