Simple • Straightforward

So easy a Caveman
Programmer can do it.

›› Let's Get Started

Utilizing Indexes

Now You Have Them, Make Sure They Are Used

The interesting thing about having indexes is that some people forget to make sure they are used. We go through the trouble to create them and then write our queries in such a way as to not use them. Below, you will find how to identify when an index is needed and how to make sure it is used.



Identify Need of an Index

When we have used the execution plan to identify slow sections of a query, a good place to start with adding indexes is on table scans. In the below plan you will see a table scan on the join between Items and Lookups.

Utilizing Indexes

SQL that generates the above execution plan:
SELECT *
FROM Items

SELECT *
FROM Items it
JOIN Lookups itype
  ON itype.PK_Lookups = it.FK_Lookups_ItemType

Looking for table scans and adding indexes to the table columns will typically speed up the queries. On this example, we could add the following indexes to remove both table scans:

CREATE INDEX IDX_FKItemType ON Items(FK_Lookups_ItemType)
CREATE INDEX IDX_PK ON Lookups(PK_Lookups)



Converting Columns

Let's say we are importing data from one system to another and we are looking to run an update statement. The other caveat is that the data being imported still needs to be scrubbed. Here is a typical (incorrect) update statement:

UPDATE WareHouseSizes
SET LastmodifiedDate = GETDATE(),
Size = s.Size
FROM StagingImport_WareHouseSizes s
JOIN WareHouseSizes whs
  ON whs.WareHouseName = s.WareHouseName
WHERE LTRIM(RTRIM(s.Size)) <> whs.Size

If there is an index on the staging table for the size column, it will no longer be utilized because the column is being converted to make sure there are no white spaces before or after the size. There are 2 ways to resolve this issue.

  1. Scrub the data before joining by doing an update on the staging table to make sure the Size column has the white spaces removed.
  2. Similar to the first option, put the staging table data into a temp table and scrub the column. Then add an index to the temp table. Example below:

SELECT WareHouseName,
  LTRIM(RTRIM(Size)) AS Size
INTO #Temp
FROM StagingImport_WareHouseSizes

CREATE INDEX #IDX_Size ON #Temp(Size)

UPDATE WareHouseSizes
SET LastmodifiedDate = GETDATE(),
Size = s.Size
FROM #Temp s
JOIN WareHouseSizes whs
  ON whs.WareHouseName = s.WareHouseName
WHERE s.Size <> whs.Size





Comments

Name:
Title:   
Commenting Currently Disabled


Jokeg
8/25/2012 5:30:51 PM
WqSTEfwDdmIRFR


That's rlealy thinking at an impressive level
Florence
8/4/2011 6:00:20 PM



Not bad at all fellas and gaalls. Thanks.