efficient db indexing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

efficient db indexing

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-21 : 09:22:28

Hi all,

I need some help understanding a particular indexing issue and would be grateful for any help.

I have a table that has...
myPrimaryKeyID
RuleID (that is related to a lookup of table of about 200 rules)
ProductID
ProductVersionID

Some points...

1. There could potentially be 10 million records within this table
2. All columns will be used to look up with different combinations
WHERE myPrimaryKeyID=?
WHERE RuleTypeID=?
WHERE ProductID=?
WHERE ProductVersionID=?
WHERE RuleTypeID=? AND ProductID=?
WHERE RuleTypeID=? AND ProductVersionID=?
WHERE RuleTypeID=? AND ProductID=? AND ProductVersionID=?
3. Once a record is INSERTED, DELETIONS will not occur very often.
4. The table will be queried (where the query will include the INNER JOIN relationships of a couple of tables) at least once on each request (no more than twice probably)

My question then is, what indexes should I have on this table that will maximise performance?

Currently I am thinking that for each of the WHERE scenarios above, there should be an appropriate INDEX added, then make each of the keys unique. Or, maybe only one index consisting of the columns RuleTypeID, ProductID and ProductVersion should do the trick.

Your time with this is greatly appreciated.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 09:35:10

Pretty much you want to index on each of the colums you show above but it will depend on the cardinality. If you only have 2 or 3 values though you will get little benefit from an index on 10 million rows. You could consider partitioning on those but again, it depends. If you have an multi column index it can still be used if only the first column apears useful. e.g. an index on RuleTypeID, ProductID will help with
WHERE RuleTypeID=? AND ProductVersionID=?
WHERE RuleTypeID=? AND ProductID=? AND ProductVersionID=?
but not with
WHERE ProductID=?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-21 : 10:28:15

Look at using the INCLUDES statement to include extra columns without the same overhead as additional indexes.
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!402.entry
Also an indexed view could include columns / indexes on the other tables it will join on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources