Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help me to create useful index (while using dynamic query) Expand / Collapse
Author
Message
Posted Friday, February 22, 2013 1:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, 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.
Post #1422901
Posted Friday, February 22, 2013 3:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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.
Post #1422969
Posted Friday, February 22, 2013 6:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, 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)
Post #1423010
Posted Saturday, February 23, 2013 10:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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
Post #1423366
Posted Saturday, February 23, 2013 9:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, 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
Post #1423391
Posted Sunday, February 24, 2013 10:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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.
Post #1423424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse