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

  • 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.

  • 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.

  • 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)

  • 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

  • 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

  • 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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply