Sql-server – Performance of a View with Hundreds of Left Joins

sql server

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

Best Answer

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

Related Solutions

SQL Server – Optimize SELECT on Subquery with COALESCE

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong).

Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages:

The optimizer has less transformation options available for FULL joins than for (the more common) INNER and LEFT joins.
The Z derived table can be removed (you can do that anyway) from the view definition.
The NOT(pe.ThisThing = 1 AND se.OtherThing = 0) will be needed only on the INNER join part.
Minor improvement, the use COALESCE() will be minimal if any at all (I assumed that se.SEId and pe.PEId are not nullable. If more columns are not nullable, you'll be able to remove more COALESCE() calls.)
More important, the optimizer may push down any conditions in your queries that involve these columns (now that COALESCE() is not blocking the push.)
All the above will give the optimizer more options to transform/rewrite any query that uses the view so it may find an execution plan that indexes on the underlying tables can be used.

In all, the view can be written as:

SELECT 
    se.SEId + '-' + pe.PEId AS Id,
    se.SEId, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    COALESCE(pe.EventType, se.EventType) AS EventType,
    pe.Duration,
    COALESCE(pe.Data, se.Data) AS Data,
    COALESCE(pe.Field, se.Field) AS Field,
    pe.ThisThing, se.OtherThing,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe INNER JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1 AND se.OtherThing = 0) 

UNION ALL

SELECT 
    '0-0',
    NULL, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    pe.EventType,
    pe.Duration,
    pe.Data,
    pe.Field,
    pe.ThisThing, NULL,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe LEFT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1)
  AND se.StaffName IS NULL

UNION ALL

SELECT 
    '0-0',
    se.SEId, NULL,
    se.StaffName, 
    se.EventTime,
    se.EventType,
    se.Duration,
    se.Data,
    se.Field,
    NULL, se.OtherThing, 
    DATEADD(minute, se.Duration, se.EventTime) AS EventEndTime
FROM PE pe RIGHT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (se.OtherThing = 0)
  AND pe.StaffName IS NULL ;

SQL Server – How Many LEFT JOINS Make a VIEW Unusable?

The link provided by Martin Smith to an existing SO question provided me with the answer:

The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process

Best Answer

Related Solutions

SQL Server – Optimize SELECT on Subquery with COALESCE

SQL Server – How Many LEFT JOINS Make a VIEW Unusable?

Related Question