Optimize Query?

Developer here at shop is asking how to increase performance of this query. I have looked at this and have made adjustments to the original (mine is faster) adding group by instead of using distinct and adding a couple indexes.

Plan.pesession (108.2 kB)
avatar image By Ray Beller (JGR) 1 asked Jun 07, 2017 at 06:28 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jun 08, 2017 at 12:03 PM

Have you considered performing the unpivot at the presentation layer instead of in SQL? Or storing this data using an EAV (entity-attribute-value) model instead of an explicit column per data point? It's clear that SQL Server has a very hard time estimating how many tuples/rows will come out where the value is NULL or NOT NULL - with EAV you could have a filtered index that ignored those.

avatar image Ray Beller (JGR) Jun 08, 2017 at 02:16 PM

Thank you Aaron. Looking into reworking the schema changing the storage of this data.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Jun 07, 2017 at 06:28 PM

Seen: 340 times

Last Updated: Jun 08, 2017 at 02:16 PM