December 18, 2009 at 3:05 pm
Hi, I am stumped by the following:
I have a fact table of about 60Million rows called search_fact.
I also have a dimension table day_dim that has columns:
day_id,
month_id,
year_id..
The fact table has a foreign key into day_dim day_id
When I run the query:
SELECT TOP 100
*
FROM
search_fact ks,
day_dim dd
WHERE
(ks.week_id = 500 AND
ks.day_id = dd.day_id)
It returns resuls very quickly, under 1 sec.
If I add "dd.month_number = 7" and run
SELECT TOP 100
*
FROM
search_fact ks,
day_dim dd
WHERE
ks.week_id = 500 AND
ks.day_id = dd.day_id AND
dd.month_number = 7
The query now runs very slowly.
I added index day_id on both tables and still same result.
Please help with your ideas.
Sincerely,
Alexander.
December 18, 2009 at 4:27 pm
Execution Plans would help here and full table schemas (including indexes), without this information any suggestions like the one I'm about to make will just be shots in the dark or educated guesses based on experience.
Since this looks like a data warehouse I'm going to suggest an index on the day_dim table that is day_id, month_number. Adding month_number to the query is now causing the optimizer to choose an index scan of day_dim instead of a seek like it was before.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 21, 2009 at 5:21 am
single column indexes rarely have the desired effect. You'll need an index to cover the three columns in the where clause as a first step.
make the first column the most selective, if that works then try making the other two columns inlcuded.
OR
have a look to see which index it is using and add the other two columns as included.
It's tricky without seeing the plans. schema etc.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 22, 2009 at 3:26 pm
My vote is that the optimizer is estimating a small number of rows and using index seek/bookmark lookup plan and/or nested loop join.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply