Optimize Query?

Ray Beller (JGR) 2017-06-07 18:28:57

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.

Aaron Bertrand 2017-06-08 12:03:11
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.
Ray Beller (JGR) 2017-06-08 14:16:15
Thank you Aaron. Looking into reworking the schema changing the storage of this data.