Performance of a View with Hundreds of Left Joins

by ovinophile   Last Updated July 12, 2019 00:06 AM

I have a table with 100+ columns, each containing a numeric value that maps to a string value in another table. When trying to create a view for the table that contains these string values, I'm getting a significant performance hit from all the joins. Given that table structures can't be changed, is there a better way to map all these strings in from the other table?

--MyTable--
Id  Name    attr1   attr2   attr3   ... attr200
1   First   1       2       4           2
2   Second  3       3       5           1

--StringMap--
AttrName    Value   Label
attr1       1       Small
attr1       2       Medium
attr1       3       Large
attr2       1       A
attr2       2       B
attr2       3       C
...
attr200     1       Purple
attr200     2       Blue
attr200     3       Green

select
  t.Name,
  s1.Label as attr1name,
  s2.Label as attr2name,
  s3.Label as attr3name,
  ...
  s200.Label as attr200name
from MyTable t
  left join StringMap s1 on s1.AttrName = 'attr1' and s1.Value = t.attr1
  left join StringMap s2 on s2.AttrName = 'attr2' and s2.Value = t.attr2
  left join StringMap s3 on s3.AttrName = 'attr3' and s3.Value = t.attr3
  ...
  left join StringMap s200 on s200.AttrName = 'attr200' and s200.Value = t.attr200
Tags : sql-server


Related Questions


Updated September 03, 2018 18:06 PM

Updated March 16, 2016 08:02 AM

Updated July 10, 2018 15:06 PM

Updated April 17, 2019 14:06 PM

Updated April 18, 2019 11:06 AM