Convert multiple self JOINs to window function or subqueries (SQL)

Question

I have a query with several self joins:

SELECT t2.Name, t2.Date as date, t2.Value as value1, t3.Value as value2, t4.Value as value3
FROM table_t t1
    RIGHT JOIN #tempPredefinedIndicators t2
        ON t1.Name = t2.Name
    JOIN #tempPredefinedIndicators t3 
        ON t2.Name = t3.Name AND t2.Date = t3.Date
    JOIN #tempPredefinedIndicators t4 
        ON t2.Name = t4.Name AND t2.Date = t4.Date    
WHERE t1.ValueName = 'one' AND DATEDIFF(year, ISDATE(t1.Value), GETDATE()) >=10
AND (t2.ValueName = 'two' AND TRY_CONVERT(FLOAT, t2.Value) > 0.15)
AND (t3.ValueName = 'three' AND TRY_CONVERT(FLOAT, t3.Value) BETWEEN 0 AND 0.5)
AND (t4.ValueName = 'four' AND TRY_CONVERT(FLOAT, t4.Value) > 0.15)

Here is a sample table where in yellow I marked the record that is within the boundaries of the where condition:

Here is the expected result view:

Basically I filter one table with several consecutive conditions and show the result as a table.

Is there a way, using window function or subquery to transform it to be faster?

Answer 1

You could improve performance by removing type conversions, adding indicies to the appropriate columns being queried so often, or by replacing a 4-table join with a PIVOT instead.

SELECT * FROM (
  SELECT
    [name],
    [valuename],
    [value]
  FROM table_t
  WHERE 
    (valuename = 'one' and datediff(year, isdate(value), getdate()) > 10)
     or 
    (valuename = 'two' and TRY_CONVERT(FLOAT, Value) > 0.15)
     or 
    (ValueName = 'three' AND TRY_CONVERT(FLOAT, Value) BETWEEN 0 AND 0.5)
     or
    (ValueName = 'four' AND TRY_CONVERT(FLOAT, Value) > 0.15)
) a
PIVOT (
  MAX([value])
  FOR [valuename]
  IN (
    [one], [two], [three], [four]
  )
) AS PivotTable

Teach yourself through examples here: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/

Your query took 30ms to run in sql-fiddle; it only took 17ms to run the pivot w/o type-conversions in the WHERE clause, and 20-22ms to run with all the WHERE clause conversions.