Strange behaviour on Adventure Works DW

Question

Strange behaviour on Adventure Works DW

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Allright, thanks for the information.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Jonathan Kehayias (6/4/2012)
Koen Verbeeck (6/4/2012)
The hardware is just a standard Dell laptop, with 8Gb RAM and i7 Intel processor.
I'm at work now, so I'll post the details later.
I created the columnstore index and now the query runs less than one second, so the problems are very likely IO related.
If you are using the standard dell SATA disk, that is really the most likely cause of your differences given the information you've provided so far. SSDs really make the difference in laptop performance these days for being able to test server like workloads against SQL.

It's indeed a standard Seagate SATA disk:

http://www.seagate.com/internal-hard-drives/laptop-hard-drives/momentus-laptop/?sku=ST9250410AS

CPU is Intel i7 Core @ 1.73GHz with 64-bit Windows 7.

8Gb RAM

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

To wrap it up:

I changed the query so I don't join on the Date dimension but I filter on OrderDate directly, as Kevin suggested. I created a covering index by putting an index on OrderDate and including the other columns of the fact table.

Now the query runs in 24 seconds, which is very acceptable for 30 million rows and my apparently crappy hardware 😀

Thanks everyone for their assistance!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP