Recommended index causing query to run twice as long / HASH vs NESTED LOOP

Question

Recommended index causing query to run twice as long / HASH vs NESTED LOOP

Viewing 6 posts - 16 through 21 (of 21 total)

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

So overall, I personally haven't had much experience with using a date table or a date dimension. We're moving forward with some large changes in the near future; however my own personal reference ... is there any other better way to handle date aggregates outside of using a date table? I've always been so used to simply adding the date part functions within the grouping; however an experience like this has definitely taught me to think twice. Just curious if there is anything I can be doing differently to better myself.

Thanks again everyone!

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

Adam Bean (3/14/2013)
...is there any other better way to handle date aggregates outside of using a date table? I've always been so used to simply adding the date part functions within the grouping...

Untill you get your date table built, one possibility to consider, depending on how often you need to do this kind of grouping, would be to use computed columns on the ftInvoice table InvoiceDate column for Year and Month functions. Computed columns can be indexed, as demonstrated in this article:

http://blog.sqlauthority.com/2010/08/22/sql-server-computed-columns-index-and-performance/

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 3

Adam Bean (3/13/2013)
Sorry for the confusion, see attached.
Thanks

I wouldn't exactly call myself a 733t execution plan analyst (and I tremble to even post on a thread where Grant Fritchey has already made an appearance), but I can tell you that the query with the "old" index seems to make better use of parallelism than the query with the "new" index. Both execution plans have parallel sections, but the execution plan with the "new" index actually assigns all 35 million rows coming out of the index seek to a single thread. The execution plan with the "old" index splits those 35 million rows up between two threads, so even if it would not be as efficient as the "new" index plan when running on a single thread, its more effective use of parallelism probably gives it an edge in time to results.

Now, explaining why the parallelism works out this way and how to modify your queries to make better use of both parallelism and indexes is getting closer to ninja level, and I'm not quite there yet!

Jason Wolfkill

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Thanks for the responses, definitely great suggestions and gives me more ideas to work with.

Currently though, thanks to a friend of mine, I began playing with column store indexes and holy jeez, this may be my answer. So far, I'm seeing amazing results. We know we have to rewrite our db and app, but for now I'm just trying to stop the bleeding and I think this may be the answer for now.

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 5

Adam Bean (3/14/2013)
Thanks for the responses, definitely great suggestions and gives me more ideas to work with.
Currently though, thanks to a friend of mine, I began playing with column store indexes and holy jeez, this may be my answer. So far, I'm seeing amazing results. We know we have to rewrite our db and app, but for now I'm just trying to stop the bleeding and I think this may be the answer for now.

Column store indexes have some pretty significant restrictions on their use - be sure to read this first!

http://msdn.microsoft.com/en-us/library/gg492088.aspx#Restrictions

Most notably:

A table with a columnstore index cannot be updated

I'd be interested to hear how this works out for you.

Jason Wolfkill

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

Yeah, been actively reading while playing. Not worried about the lack of being able to modify data.

We're a heavy ETL shop, so I would drop the index at time of load and recreate dynamically at the end. I do this as it is for most of my non-clustered which I can now part ways with!