I have the following multi-join query:SELECT …FROM Table1 t1 JOIN Table2 t1 on t1.col1 = t2.col1 JOIN Table3 t1 on t1.col2 = t3.col2 JOIN Table4 t1 on t1.col3 = t4.col3 I want to performance tune this query. Which of the below two approaches would work better in this case: 1. Create six single-column indexes on : t1.col1; t2.col1; t1.col2; t3.col2; t1.col3; t4.col3 2. Create one covered index on t1.col1+t1.col2+t1.col3Are there any advantages of one approach over another in general case?
It all depends on the query optimizer index usage... Check the following articles... http://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/ http://msdn.microsoft.com/en-us/library/aa964133(SQL.90).aspx
Before sugestion any idea Can you tell us in SELECT clause which columns you are considering (Table1 / Table2 / Table3 / Table4). -Abhijit, MCP
Thank you for your response Abhijit. I was trying to get the generic answer first. However I realize that generic aswer likely is not available. So, if you could show me some scenarious where one way is better than another one it would be what I am looking for. Ususally the column list in select includes some columns from each joined table. Thanks.