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......
No comments:
Post a Comment