November 21, 2008 at 10:26 am
I have the following multi-join query:
SELECT …
FROM Table1 t1
JOIN Table2 t2 on t1.col1 = t2.col1
JOIN Table3 t3 on t2.col2 = t3.col2
JOIN Table4 t4 on t3.col3 = t4.col3
I want to performance tune this query.
Which of the below two scenarios would work better in this case:
1. Create six single-column indexes on :
t1.col1; t2.col1;
t2.col2; t3.col2;
t3.col3; t4.col3
2. Create one covered index on t1.col1+t2.col2+t3.col3
I understand that the final word always belongs to Query Optimizer :); but is there any recommendations/benchmarks in general case?
November 21, 2008 at 11:13 am
Well, you can't create an index that contains columns from different tables, so that's not an option.
Try something like this:
t1 (col1)
t2 (col1, col2)
t3 (col2, col3)
t4 (col3)
Include columns would vary depending on what's in the select.
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
November 21, 2008 at 11:25 am
Sorry, my bad 😉 - of course I can’t. Fixing my typo ...
I was trying to compare the one table to multiple table-joins, like below:
SELECT …
FROM Table1 t1
JOIN Table2 t2 on t1.col1 = t2.col1
JOIN Table3 t3 on t1.col2 = t3.col2
JOIN Table4 t4 on t1.col3 = t4.col3
Which of the below two scenarios 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.col3
Is there any advantages in one over another one in general case?
November 24, 2008 at 9:38 am
Here's what I suggest: Create a ProofOfConcept database on one of your dev boxes (or your only dev box if you just have one, I guess 🙂 ). Create test tables exactly as you have named them for this sample, load them up with a few million rows of test data, and test both index ideas.
You'll learn more from trying it out than from just asking.
If you want the fast answer, the single index will probably be better in most cases, most of the time, but the only way to be sure is to test.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2008 at 9:48 am
You may also want to look at the possibility of using indexed views.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply