I am developing a laboratory database query which compares test results (testResults) to specification conditions (specCond). I cannot get SQL Server to retrieve the results efficiently.

Schema: test (1-*, test_id) samp

The execution plan for the query below shows SQL Server retrieves testResults for ALL tests before filtering on test.testName. I assume this is because of the complexity of my testResults view.

SELECT test_id, stat, testVal
  FROM testResults tr
  JOIN test ON tr.test_id = test.test_id
 WHERE test.testName = 'someTestName'

testResults Option 1: CROSS JOIN

-- testResults_CROSSJOIN
SELECT t.test_id,
    CASE specStat.stat
        WHEN 'MIN'          THEN tMin
        WHEN 'MAX'          THEN tMax
        WHEN 'AVG'          THEN tAvg
        WHEN 'STDEV'            THEN tStdev
        WHEN 'EV'           THEN tMax - tMin
    END AS testVal
    SELECT test.test_id,
        MIN(samp.result) AS tMin,
        MAX(samp.result) AS tMax,
        AVG(samp.result) AS tAvg,
        STDEV(samp.result) AS tStdev
    FROM test
    INNER JOIN samp ON test.test_id = samp.test_id

    WHERE (samp.status = 'OK')
    GROUP BY test.test_id
    ) AS t
CROSS JOIN specStat -- Contains stat <= ('MIN', 'MAX', 'AVG', 'STDEV', 'EV')

testResults Option 2: UNPIVOT

select i.test_id, i.stat, i.testVal
from (
        SELECT test.test_id,
            CAST (MIN(samp.result) AS Numeric(18,9)) AS "MIN",
            CAST (MAX(samp.result) AS Numeric(18,9)) AS "MAX",
            CAST (AVG(samp.result) AS Numeric(18,9)) AS "AVG",
            CAST (STDEV(samp.result) AS Numeric(18,9)) AS "STDEV",
            CAST (MAX(samp.result) - MIN(samp.result) AS Numeric(18,9)) AS "RANGE"
        FROM test
        INNER JOIN samp ON test.test_id = samp.test_id
        WHERE samp.status = 'OK'

        GROUP BY test.test_id
) t
unpivot ( testVal for stat IN ("MIN", "MAX", "AVG", "STDEV", "RANGE")) i

join test on i.test_id = test.test_id
where test.testName = 'P1STD'

How can I rewrite my testResults view so that it ONLY retrieves results for the joined test(s)? (Ideas: query hints, another transformation type, etc)

  • One thing to consider: How much more efficient would filtering by testName before or during the join be? For example, what percentage of the table matches your example filter testName = 'someTestName', and is there an index that supports this search? – James L Nov 13 '15 at 20:30
  • 1
    To get a decent answer, I think you will probably need to post the schema for the view and tables (including indexes), a sample query you are having trouble with, and possibly some sample data. – James L Nov 13 '15 at 20:31
  • If you want to guarantee that filtering on test.testName happens first, you can insert result of this query into a temp table and then join the temp table with the view. The actual performance may be worse, or may be better. Without explicit temp table optimizer is free to shuffle operators in the plan as it likes (as long as the overall result set is the same, of course). – Vladimir Baranov Nov 14 '15 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.