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:
FROM Items

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)

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


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


Commenting Currently Disabled

8/25/2012 5:30:51 PM

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

Not bad at all fellas and gaalls. Thanks.
Many apperceive the cast Tag Heuer, which has replica watches uk some arising collectible examples. The added abnormal and colorful, the added absorption they get. This includes the Monaco model, although these models accept rolex replica already acquired abundant cachet to be on the top ancillary in agreement of amount if they accept accustomed able affliction and storage. Other brands with abeyant for beginning collectors are Ulysee-Nardin, Universal Geneve and Longines if in excellent condition, all of replica rolex which are featured in Haines book Vintage Wristwatches.