SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help me to create useful index (while using dynamic query)


help me to create useful index (while using dynamic query)

Author
Message
Anuj Rathi
Anuj Rathi
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 176
Hi All,
I have to generate some reports on the basis of several different conditions. So, I am using dynamic query.
I also discussed this problem previously.

http://www.sqlservercentral.com/Forums/Topic1415845-391-1.aspx

But I am not getting desirable output. I just explain my situation in brief. I have more than 20 search filters & user can select one or more than one search filter. That's why I have written dynamic query. Here I am discussing a very simple scenario.

I have country(char(2)), state (int), city (bigint). Below is the foreign key sequence.

Country <-- State <-- City

Question 1: If I am searching for a city(or state) then Should I use
Country = @country AND StateID = @stateID AND CityID = @cityID

or only

CityID = @cityID (for city)
StateID = @stateID (for state)

as cityID is primary key in city table, for any specific city (e.g: cityID = 10), output will be same in both cases, but which one is efficient.

If I create a non clustered index for (contry, stateID, cityID) then it will work for all filters where I search for country or state or city if I write where clause as per first scenario.
But If I consider second one, then I will have to write three different non clustered index (each for country/state/city)


This problem become more complex, If I introduce category1ID, category2ID, category3ID
where foreign key sequence is:
category1ID <-- category2ID <-- category3ID

so, now should I use:

Country = @country AND StateID = @stateID AND CityID = @cityID AND category1ID = @category1ID AND category2ID = @category2ID AND category3ID = @category3ID

if I am searching for results in city & category3ID.
For this case, I will create a non clustered index in sequence (contry, stateID, cityID, category1ID, category2ID, category3ID)

But what about search in state & any category. Above index will not be a useful index.
I have checked all the patterns, there are lots of patterns according to filters selected. One more thing. There is a single common filter in all situations:
ModifiedDate > DATEADD(dd, -90, GETUTCDATE())

means, only last 3 months records in all cases.

these are few simple patters(currently I am using second scenario where I only use cityId if I search for a city) where I search ONLY for city & category. (there are other search filters too but this time I am concentrating on these patterns only)

--WHERE vaa.StateID = @intStateID AND vaa.CategoryID = @intCategoryID AND vaa.ModifiedDate > DATEADD(dd, -90, GETUTCDATE())
--WHERE vaa.StateID = @intStateID AND vaa.SubCategoryIDLevel2 = @intSubCategoryIDLevel2 AND vaa.ModifiedDate > DATEADD(dd, -90, GETUTCDATE())
--WHERE vaa.StateID = @intStateID AND vaa.SubCategoryIDLevel3 = @intSubCategoryIDLevel3 AND vaa.TypeID = @inyTypeID AND vaa.ModifiedDate > DATEADD(dd, -90, GETUTCDATE())
--WHERE vaa.StateID = @intStateID AND vaa.SubCategoryIDLevel4 = @intSubCategoryIDLevel4 AND vaa.Condition = @chvCondition AND vaa.ModifiedDate > DATEADD(dd, -90, GETUTCDATE())
--WHERE vaa.CityID = @inbCityID AND vaa.CategoryID = @intCategoryID AND vaa.ModifiedDate > DATEADD(dd, -90, GETUTCDATE())
--WHERE vaa.CityID = @inbCityID AND vaa.SubCategoryIDLevel2 = @intSubCategoryIDLevel2 AND vaa.ModifiedDate > DATEADD(dd, -90, GETUTCDATE())
--WHERE vaa.CityID = @inbCityID AND vaa.SubCategoryIDLevel3 = @intSubCategoryIDLevel3 AND vaa.ModifiedDate > DATEADD(dd, -90, GETUTCDATE())
--WHERE vaa.CityID = @inbCityID AND vaa.SubCategoryIDLevel4 = @intSubCategoryIDLevel4 AND vaa.ModifiedDate > DATEADD(dd, -90, GETUTCDATE())

can someone please suggest me a right approach.
laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 1272
I had a similar problem. The table included a date, so I made the date a compulsory filter & added an index with date first, then all the other filter columns.

If the user didn't filter by date, I added a filter of "Date between 01 Jan 1980 and today" (ie all dates) at the start of the filter expression.

SQL Server will use an index if the first column is used in the query filter - so this index will always be used when Date is the first column in the filter expression. The degree of benefit will depend on the exact filter conditions, but it's as good as you'll get with 1 index (unless anyone knows better).

The table I indexed was read-only - so index updating was not an issue. I therefore used fill factor = 100% to minimise page reads.

If you think specific filters are going to be used, you could add extra indexes to cover them.

So to recap - I would try a single index with the date first & the other search expressions following, in likely order of most common use.

You would need to check the execution plan to ensure that any functions in the date filter don't disable use of the index. This will also show wether or not the index is being used for various different filters.
Anuj Rathi
Anuj Rathi
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 176
Mate,
Thanks for your reply.
What will you suggest for:


Question 1: If I am searching for a city(or state) then Should I use
Country = @country AND StateID = @stateID AND CityID = @cityID

or only

CityID = @cityID (for city)
StateID = @stateID (for state)

laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 1272
Assuming you're using the single index I suggested, you could accept a search of CityId=@CityId from the user, but to force the query to use theindex you would always add a date filter first - in this case all dates. So:



WHERE Date BETWEEN '01 Jan 1980' AND GETDATE() AND CityId = @CityId




...assuming all you dates are later than Jan 1980
Anuj Rathi
Anuj Rathi
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 176
For the date comparison, I am using
CreatedDate > DATEADD(dd, -90, GETUTCDATE())

Should I change the ">" to "between"?

Currently, I am using below query:

WHERE CategoryID = 1 AND CountryCode = 'GB' AND CreatedDate > DATEADD(dd, -90, GETUTCDATE())
AND StateID = 1737 AND CityID = 86
laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 1272
That should be OK, but you can check using the execution plan. It might be clearer if you put the filters in the order they appear in the index - just for clarity.

If the query doesn't use the index you could create a variable to hold DATEADD(dd, -90, GETUTCDATE()) and compare to that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search