April 15, 2009 at 7:27 am
Hi,
Assuming I have a table with 4 int columns and 4 datetime columns and that all can be used for filtering data, at the same time or not, what’s the best index usage?
a) One index with all 8 columns and if some are not used by the user to filter the data use them with a “neutral” value (if all int data is positive use > 0 for those columns).
CREATE INDEX idx_Data_01 ON data (i1, i2, i3, i4, d1, d2, d3, d4)
select … from data where i2 ‘20090103’ and d2 < ‘20090301’ -- when running sql server tell me to create and index with (i2, d1, d2)
but if use
select … from data where i1 > 0 and i2 ‘20090103’ and d2 < ‘20090301’ -- assuming all ints are positive, it does an index seek with idx_Data_01
b) Have an index for each column individually?
Using select … from data where i2 ‘20090103’ and d2 < ‘20090301’ it makes an index seek with a key lookup….
We have no way of knowing what combinations end users will use…
Thanks,
Pedro
April 15, 2009 at 7:31 am
Is this an existing app, or one in planning?
If it's existing, I would suggest you trace the server for a couple hours and see what the most common queries are, and optimise for those. This articl may help
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2009 at 9:32 am
Hi,
This is an existing app but our database holds information for classified ads from all around the world and categories (real estate, vehicles, boats, ...) and all possible combinations are used in the queries…
Everyone is searching for anything… the date columns aren’t very used unless the user specifies he only wants to see ads from the last X days…
April 15, 2009 at 9:55 am
Is there any combination of columns that's frequently filtered on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2009 at 10:00 am
The most common used columns are i1, i2 and i3… i4, d1 and d2 are used not very often but are used…
should I create and index on i1, i2, i3 with i4, d1, d2 in the include statement?
April 15, 2009 at 10:04 am
Maybe. Could you show a few sample queries? (use profiler/SQL Trace to get a sample workload if you don't already have one)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2009 at 10:12 am
The queries are very much alike the ones I putted on the first post.
They return 7 columns (id, name, i1, i2, i1value [join with another table], i2value [join with another table], d1) and has only ANDs between i1, i2, i3, i4, d1 and d2, being i1, i2 used almost 80% of the times, i3 70%, d1 50%, i4 and d2 less than 30%...
April 15, 2009 at 2:09 pm
Are the filters always inequality, as you showed in the first post, or will some be equality?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2009 at 2:53 pm
Hi,
The filters can be >, <, = and between.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply