0

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
4
  • 1
    Can you post the execution plan? Do you get performance issues SELECTing the view or the view with a WHERE clause? What version and edition of SQL Server? Row counts for the view and underlying tables?
    – HandyD
    Commented Jul 12, 2019 at 0:13
  • 3
    you might want to check out dba.stackexchange.com/questions/20759/…
    – SQLRaptor
    Commented Jul 12, 2019 at 3:28
  • 1
    please post the table schema with indexes. and the execution plan, as HandyD requested. Commented Jul 12, 2019 at 3:46
  • 1
    Please post a minimal reproducible example as per the link - more information is needed here. Commented Jul 12, 2019 at 10:29

1 Answer 1

1

You could try UnPivoting your data and then Pivoting the results:

Tables:

Create Table #tbl
(
Id Int,
[Name] VarChar(20),
attr1 Int,
attr2 Int,
attr3 Int,
attr200 Int
)
Insert Into #tbl Values
(1,'First',1,2,4,2),
(2,'Second',3,3,5,1)

Create Table #StringMap
(
AttrName VarChar(25),
[Value] Int,
[Label] VarChar(25)
)
Insert Into #StringMap Values
('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')

Query uses CTEs to UnPivot your table to have a single join to your StringMap table. It then Pivots back the results

;With cte As
(
  Select 
        id, [name], attr, vals 
  From #tbl
  UnPivot
    (
      vals
      For attr IN (attr1, attr2, attr3, attr200)
    ) As u
  ), cte1 As
(
  Select cte.id, cte.[Name], cte.attr, s.[Label] 
  From cte 
  Inner Join #StringMap s 
    On s.[Value] = cte.vals And s.AttrName = cte.attr
)
Select [Name] As [Name],
       [attr1], [attr2], [attr3], [attr200]
From 
(Select [Name], [Label], attr From cte1) As src
Pivot
(
Max(Label) For attr IN ([attr1], [attr2], [attr3], [attr200])

) As piv

Result:

Name    attr1   attr2   attr3   attr200
First   Small   B       NULL    Blue
Second  Large   C       NULL    Purple

Note: With so many columns, you'll want to search the site for using dynamic Pivots Such as: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query

1
  • I was thinking of something like this but the scale of it makes me think that the pivot/unpivot would perform worse (testing needed, of course) than the original. I was thinking that perhaps a columnstore index might help as well. But without the original execution plan and DDL I can't really say. Commented Jul 12, 2019 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.