Friday, September 30, 2011

Search Criteria and index field order

I was working on some complex workflows the other day and after several days I completed the logic, ran the simulator a thousand times and was finally satisfied with the outcome.

Then I deployed it into test...performance was terrible.

I checked the indexes on the S_EVT_ACT table and found a custom index that matched the columns in the query. We had added a new column called "X_CHANNEL" to indicate how the activity was completed (email, fax, phone, etc). My query was looking for [Status] = 'Done' and [Channel] = 'Email' (with a sort on the started date).
Everything should be running fine...after scratching my head for quite a while, creating and dropping indexes on the table.

The existing (custom) index on the table was:

CREATE NONCLUSTERED INDEX [ABC_S_EVT_ACT_X9_X] ON [dbo].[S_EVT_ACT]
(
    [X_CHANNEL] ASC,
    [EVT_STAT_CD] ASC,
    [TODO_ACTL_START_DT] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


I changed the order of the search expression to [Channel] = 'Email' and [Status] = 'Done', and the query took 62ms instead of 5 minutes (300000ms). I had never put much thought into the order of the fields in the index until now. I'll be keeping that in mind from now on...

No comments:

Post a Comment