Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Index Covering Boosts SQL Server Query Performance : Page 2

Index covering is a quick and easy solution to many query performance problems. By just adding a column or two to an index you can significantly boost a query's performance.


advertisement

Scenario 2: Speed Up Select Queries Using Predicates on a Column

Consider another typical scenario when index covering is very useful: you need to speed up several select queries, like the following two, using predicates on ITEM_ID:

SELECT TURNAROUND, COUNT(*) FROM DBO.ORDERS WHERE ITEM_ID=10000 GROUP BY TURNAROUND SELECT SUM(AMOUNT) FROM DBO.ORDERS WHERE ITEM_ID=10093

The table ORDERS is the same as in the previous scenario. It has a non-clustered index on the ITEM_ID column. Both queries use the non-clustered index on ITEM_ID and access the qualifying rows in the table via bookmark lookups. It would be great to create a clustered index on ITEM_ID (having a clustered index on a foreign key is very common), but that is not an option: a clustered index already exists on CUSTOMER_ID.



Instead, you can go for a covering non-clustered index on (ITEM_ID, AMOUNT, TURNAROUND). Both queries will run much faster, because the need to do bookmark lookups is eliminated. All the columns necessary for both queries are stored in index entries. Also consider dropping the index on (ITEM_ID)—you probably will no longer need it.

The Fine Print

When you add an index on a table, you usually slow down modifications against that table. This is why keeping the number of indexes as small as possible usually (not always) is very important. So under most circumstances you should have just one index on (ITEM_ID, AMOUNT, TURNAROUND), not separate indexes on (ITEM_ID, AMOUNT) and (ITEM_ID, TURNAROUND). This one index will use less space, and it will not slow down the modifications as much as several indexes would.

If you have a clustered index, then every index entry in a non-clustered index also stores all the columns necessary to locate the row via the clustered index (they are called bookmarks). These bookmark columns also count towards index covering. For instance, if the table ORDERS is clustered on CUSTOMER_ID, then the non-clustered index on ORDER_DATE covers the query:

SELECT DISTINCT CUSTOMER_ID FROM ORDERS WHERE ORDER_DT='20050917'

Note that some well-known rules of thumb about non-clustered indexes are not relevant to covering non-clustered ones. For instance, even though ORDER_DT is less selective than ITEM_ID, you can successfully use an index on ORDERS(ORDER_DT, ITEM_ID) to cover a query:

SELECT DISTINCT ITEM_ID FROM ORDERS WHERE ORDER_DT='20050202'

More to the point, putting the more selective column ITEM_ID first in the index will result in worse performance. Another example is the following query:

SELECT DISTINCT LAST_NAME, FIRST_NAME FROM CUSTOMERS WHERE FIRST_NAME LIKE '%eather%'

This query is covered by an index on (LAST_NAME, FIRST_NAME) even though only the second column in the index is used in the WHERE clause and the predicate FIRST_NAME LIKE '%eather%' is not index-friendly (a.k.a. not sargeable).

For SQL Server-to-Oracle Migrants
If you have recently switched to MS SQL Server from Oracle, be aware that MS SQL Server's indexes store entries even for the rows for which all the columns in the index definition are null. For example, even if both LAST_NAME and FIRST_NAME columns are nullable, the index on LAST_NAME, FIRST_NAME will still have entries for all the rows in the table—even for those rows with both LAST_NAME and FIRST_NAME null. So, unlike Oracle, MS SQL Server will consider the index on (LAST_NAME, FIRST_NAME) as covering the query:

SELECT DISTINCT LAST_NAME, FIRST_NAME FROM CUSTOMERS

(Index) Covering the Basics

What you have just learned are only the basics. Learn more about index covering and the Index Tuning Wizard to get the best performance from your queries.

Good luck!



Alexander Kuznetsov has over 10 years of experience in database design, development, troubleshooting, and administration. Currently, he works with DRW Trading Group and concentrates on database design, development, and performance improvements.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap