|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Where to put indexestbl_1 join_id record_id sku_id tbl_2 sku_id sku sku_desc tbl_3 join_id sku_id part_id tbl_4 part_id part part_desc I've joined tbl_1.sku_id to tbl_2.sku_id tbl_2.sku_id to tbl_3.sku_id tbl_3.part_id to tbl_4.part_id ....Now I've got a sproc: SELECT part, part_desc FROM view1 WHERE record_id = @Record_Id My question... What would be the best indexes to set up for this senario? SQL server by default sets up clustered indexes on the join_id on my join tables... but I'm not sure if that's the best. Thanks in advance. totally depends on the size of your data. Are you having specific
performance issues that you can see in the query plan? It really depends on the cardinality of these relationships and how many rows will be returned. For the simple answer I would probably suggest that you index all columns referred to in here: > tbl_1.sku_id to tbl_2.sku_id But this is not a guarantee that it will help. Check the query plan and > tbl_2.sku_id to tbl_3.sku_id > tbl_3.part_id to tbl_4.part_id > > ...Now I've got a sproc: > SELECT part, part_desc FROM view1 WHERE record_id = @Record_Id work on the hotspots that take the most time in the plan. ( I will assume that your names columns designs etc are just for examples, since they definitely need help if not :) -- Show quoteHide quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Bac2Day1" <Bac2D***@gmail.com> wrote in message news:1139521049.980469.247710@g44g2000cwa.googlegroups.com... > Here's my view1: > > tbl_1 > join_id > record_id > sku_id > > tbl_2 > sku_id > sku > sku_desc > > tbl_3 > join_id > sku_id > part_id > > tbl_4 > part_id > part > part_desc > > I've joined > tbl_1.sku_id to tbl_2.sku_id > tbl_2.sku_id to tbl_3.sku_id > tbl_3.part_id to tbl_4.part_id > > ...Now I've got a sproc: > SELECT part, part_desc FROM view1 WHERE record_id = @Record_Id > > > > My question... What would be the best indexes to set up for this > senario? SQL server by default sets up clustered indexes on the > join_id on my join tables... but I'm not sure if that's the best. > Thanks in advance. >  
Other interesting topics
Does dynamic SQL allow table variables?
1 to many relationship between columns trouble using a temp table in another SELECT DBCC SHOWCONTIG question Insert Trigger Problem with Cursor and Union in select Optimizing query with UDF and table vars and IN Reducing 5 values to 1 value Moving indexes from a filegroup to another SELECT problem in stored procedure |
|||||||||||||||||||||||
