Optimize this MS SQL query with a lot of joins-CodePudding

I'm using this query with joins to select specific values from one view:

SELECT DISTINCT
qir.[Portfolio Company Key] AS portfolio_company_id,
qir.[Quarter Date Key] AS quarter_date_id,
realized_value.Value AS realized_value,
unrealized_value.Value AS unrealized_value,
total_fair_value.Value as total_fair_value,
multiple.Value as multiple,
gross_irr_percentage.Value as gross_irr_percentage,
multiple_used_in_valuation.Value as multiple_used_in_valuation,
net_financial_debt.Value as net_financial_debt,
net_financial_debt_ebitda.Value as net_financial_debt_ebitda,
enterprise_value.Value as enterprise_value,
fund_investment_cost.Value as fund_investment_cost

FROM [dbo].[vQIRData] qir

LEFT JOIN [dbo].[vQIRData] realized_value
ON qir.[Portfolio Company Key] = realized_value.[Portfolio Company Key]
AND qir.[Quarter Date Key] = realized_value.[Quarter Date Key]
AND realized_value.[MeasureType] = 'Fair Market Valuation'
AND realized_value.[Measure Name] = 'Realized Value'

LEFT JOIN [dbo].[vQIRData] unrealized_value
ON qir.[Portfolio Company Key] = unrealized_value.[Portfolio Company Key]
AND qir.[Quarter Date Key] = unrealized_value.[Quarter Date Key]
AND unrealized_value.[MeasureType] = 'Fair Market Valuation'
AND unrealized_value.[Measure Name] = 'Unrealized Value'

LEFT JOIN [dbo].[vQIRData] total_fair_value
ON qir.[Portfolio Company Key] = total_fair_value.[Portfolio Company Key]
AND qir.[Quarter Date Key] = total_fair_value.[Quarter Date Key]
AND total_fair_value.[MeasureType] = 'Fair Market Valuation'
AND total_fair_value.[Measure Name] = 'Total Fair Value'

LEFT JOIN [dbo].[vQIRData] multiple
ON qir.[Portfolio Company Key] = multiple.[Portfolio Company Key]
AND qir.[Quarter Date Key] = multiple.[Quarter Date Key]
AND multiple.[MeasureType] = 'Fair Market Valuation'
AND multiple.[Measure Name] = 'Multiple'

LEFT JOIN [dbo].[vQIRData] gross_irr_percentage
ON qir.[Portfolio Company Key] = gross_irr_percentage.[Portfolio Company Key]
AND qir.[Quarter Date Key] = gross_irr_percentage.[Quarter Date Key]
AND gross_irr_percentage.[MeasureType] = 'Fair Market Valuation'
AND gross_irr_percentage.[Measure Name] = 'Gross IRR%'

LEFT JOIN [dbo].[vQIRData] multiple_used_in_valuation
ON qir.[Portfolio Company Key] = multiple_used_in_valuation.[Portfolio Company Key]
AND qir.[Quarter Date Key] = multiple_used_in_valuation.[Quarter Date Key]
AND multiple_used_in_valuation.[MeasureType] = 'Fair Market Valuation'
AND multiple_used_in_valuation.[Measure Name] = 'Multiple used in valuation'

LEFT JOIN [dbo].[vQIRData] net_financial_debt
ON qir.[Portfolio Company Key] = net_financial_debt.[Portfolio Company Key]
AND qir.[Quarter Date Key] = net_financial_debt.[Quarter Date Key]
AND net_financial_debt.[MeasureType] = 'Fair Market Valuation'
AND net_financial_debt.[Measure Name] = 'Net Financial Debt'

LEFT JOIN [dbo].[vQIRData] net_financial_debt_ebitda
ON qir.[Portfolio Company Key] = net_financial_debt_ebitda.[Portfolio Company Key]
AND qir.[Quarter Date Key] = net_financial_debt_ebitda.[Quarter Date Key]
AND net_financial_debt_ebitda.[MeasureType] = 'Fair Market Valuation'
AND net_financial_debt_ebitda.[Measure Name] = 'Net Financial Debt / EBITDA'

LEFT JOIN [dbo].[vQIRData] enterprise_value
ON qir.[Portfolio Company Key] = enterprise_value.[Portfolio Company Key]
AND qir.[Quarter Date Key] = enterprise_value.[Quarter Date Key]
AND enterprise_value.[MeasureType] = 'Fair Market Valuation'
AND enterprise_value.[Measure Name] = 'EV'

LEFT JOIN [dbo].[vQIRData] fund_investment_cost
ON qir.[Portfolio Company Key] = fund_investment_cost.[Portfolio Company Key]
AND qir.[Quarter Date Key] = fund_investment_cost.[Quarter Date Key]
AND fund_investment_cost.[MeasureType] = 'Fair Market Valuation'
AND fund_investment_cost.[Measure Name] = 'Fund Investment Cost'

WHERE qir.[MeasureType] = 'Fair Market Valuation'

ORDER BY portfolio_company_id

Everything is selected from one view. I'm creating new columns with different namings. I don't have access to that view. I can only select values from there. This query is selecting values properly but I want to optimize it so it runs more quickly.

Is there a way I can optimize its execution time?

CodePudding user response：

Just use PIVOT...

SELECT
  pvt.*  
FROM   
(
  SELECT
    [Portfolio Company Key] AS portfolio_company_id,
    [Quarter Date Key]      AS quarter_date_id,
    [Measure Name],
    [Value]
  FROM
    [dbo].[vQIRData]
  WHERE
    [MeasureType] = 'Fair Market Valuation'
)
  AS qir  
PIVOT  
(  
  MAX([Value]) FOR [Measure Name] IN (
    [Realized Value],
    [Unrealized Value],
    [Total Fair Value],
    etc,
    etc
  )
)
  AS pvt
ORDER BY
  pvt.portfolio_company_id

CodePudding user response：

Fix to @Alexy's answer, as he seemingly refuses to do so...

SELECT
  [Portfolio Company Key] AS portfolio_company_id,
  [Quarter Date Key] AS quarter_date_id,
  MAX(IIF([Measure Name] = 'Realized Value', Value, NULL)) AS realized_value,
  MAX(IIF([Measure Name] = 'Unrealized Value', 
  Value, NULL)) AS unrealized_value,
  MAX(IIF([Measure Name] = 'Total Fair Value', Value, NULL)) as total_fair_value,
  MAX(IIF([Measure Name] = 'Multiple', Value, NULL)) as multiple,
  MAX(IIF([Measure Name] = 'Gross IRR%', Value, NULL)) as gross_irr_percentage,
  MAX(IIF([Measure Name] = 'Multiple used in valuation', Value, NULL)) as multiple_used_in_valuation,
  MAX(IIF([Measure Name] = 'Net Financial Debt', Value, NULL)) as net_financial_debt,
  MAX(IIF([Measure Name] = 'Net Financial Debt / EBITDA', Value, NULL)) as net_financial_debt_ebitda,
  MAX(IIF([Measure Name] = 'EV', Value, NULL)) as enterprise_value,
  MAX(IIF([Measure Name] = 'Fund Investment Cost', Value, NULL)) as fund_investment_cost
FROM
  [dbo].[vQIRData]
WHERE
  [MeasureType] = 'Fair Market Valuation'
GROUP BY
  [Portfolio Company Key],
  [Quarter Date Key]
ORDER BY
  portfolio_company_id

Note; DISTINCT is removed, and the logic is Functionally identical to using PIVOT, but slightly more repetitive while also being generally more flexible to more complex situations.

CodePudding user response：

You do not need JOIN at all, as you filter the view by single condition WHERE qir.[MeasureType] = 'Fair Market Valuation' and retrieving the same column (Value) value with different names according to Measure Name column value. So you can use simple conditions, like that:

SELECT DISTINCT
[Portfolio Company Key] AS portfolio_company_id,
[Quarter Date Key] AS quarter_date_id,
MAX(IIF([Measure Name] = 'Realized Value', Value, NULL)) AS realized_value,
MAX(IIF([Measure Name] = 'Unrealized Value', Value, NULL)) AS unrealized_value,
MAX(IIF([Measure Name] = 'Total Fair Value', Value, NULL)) as total_fair_value,
MAX(IIF([Measure Name] = 'Multiple', Value, NULL)) as multiple,
MAX(IIF([Measure Name] = 'Gross IRR%', Value, NULL)) as gross_irr_percentage,
MAX(IIF([Measure Name] = 'Multiple used in valuation', Value, NULL)) as multiple_used_in_valuation,
MAX(IIF([Measure Name] = 'Net Financial Debt', Value, NULL)) as net_financial_debt,
MAX(IIF([Measure Name] = 'Net Financial Debt / EBITDA', Value, NULL)) as net_financial_debt_ebitda,
MAX(IIF([Measure Name] = 'EV', Value, NULL)) as enterprise_value,
MAX(IIF([Measure Name] = 'Fund Investment Cost', Value, NULL)) as fund_investment_cost

FROM [dbo].[vQIRData]
WHERE [MeasureType] = 'Fair Market Valuation'
GROUP BY [Portfolio Company Key], [Quarter Date Key]
ORDER BY portfolio_company_id