Search This Blog & Web

Wednesday, August 5, 2009

Optimize a query using Indexes (Index Options)

Recently i got a query that is running on more then 10 million records and join 2 tables. Simple query returns result in more than 1.30 minutes. For much search i find solution to recduce its time to 0 seconds and i do following to do that.

Step1: Change the query paging logic from 2 quries to one query using WITH clause.

Step2: Execute the query and save profiler of that query. Analyze it in Database Tunning Advisor.

Step3: It offers me more than 5 solution by creating 2 indexes on 2 tables and different columns combinations and also creating staistics for all selecting columns.

Step4: I might not do this because if i create staistics then their must be some job who will regularly update that satistics so i decided to create 1 index to improve performence.

Step5: I got 2 types of Indexes:

              ONLINE: Which slows down result during rebuilding.

              OFFLINE: Which locks all table during rebuilding.

I use following query to create Index

CREATE NONCLUSTERED INDEX [_dta_index_IndexName] ON Table
(
[Column list] [order] ,
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

more detail is in next post......

Post a Comment