sql - 查询需要长时间的调整建议

Question

我收藏了SQL Server包含调查信息的表格，我想运行一个查询，其中每个调查占一行，每个答案占一列。

结构设置如下:

ResponseHeader:
 ID (record for each survey)

Responses:
 ID 
 responseHeaderID
 questionID
 answerItemsID

AnswerItems:
 ID   (This is the value needed in each answer column)
 answerText

以下是我提出的查询:

SELECT  ResponseHeader.id
    ,   ResponseHeader.YOS
    ,   ResponseHeader.rankID
    ,   ResponseHeaderunitID
    ,   ResponseHeader.age
    ,   ResponseHeader.gender
    ,   MAX(CASE WHEN Responses.questionID = 42 THEN AnswerItems.id END)
    ,   MAX(CASE WHEN Responses.questionID = 43 THEN AnswerItems.id END)
    ,   MAX(CASE WHEN Responses.questionID = 44 THEN AnswerItems.id END)
    , --.....there are 79 of these lines in the query, one for each question (Responses.questionID)
        MAX(CASE WHEN Responses.questionID = 48 THEN AnswerItems.id END)
    ,   MAX(CASE WHEN Responses.questionID = 48 THEN AnswerItems.id END)
    ,   MAX(CASE WHEN Responses.questionID = 50 THEN AnswerItems.id END)
    ,   MAX(CASE WHEN Responses.questionID = 49 THEN AnswerItems.id END)
FROM ResponseHeader
LEFT OUTER JOIN Responses ON ResponseHeader.id = Responses.responseHeaderID
LEFT OUTER JOIN AnswerItems ON Responses.answerItemsID = AnswerItems.id
WHERE completed = 1
GROUP BY
        ResponseHeader.id
    ,   YOS
    ,   rankID
    ,   unitID
    ,   age
    ,   gender
ORDER BY ResponseHeader.id;

它确实返回了正确的结果集，但在查询分析器中执行需要 18 秒。它返回 4592 行，有 85 列。不幸的是它到达了 IIS因此，缓冲区限制在经典 ASP 页面中被认为是耗时的。增加缓冲区限制不是该作业的选项。我不确定如何以其他方式获取此信息。

我正在阅读有关数据透视查询的内容，但不了解它们。有没有办法使用这种方法或任何其他方法更快地获得我的结果？

Answer 1

尝试这样的事情 -

DECLARE @Responses TABLE (responseHeaderID INT, questionID INT, answerItemsID INT)
DECLARE @ResponseHeader TABLE (id INT, YOS INT, rankID INT, unitID INT, age INT, gender CHAR(1), completed BIT)

SELECT * 
FROM (
    SELECT DISTINCT 
          rh.id
        , rh.YOS
        , rh.rankID
        , rh.unitID
        , rh.age
        , rh.gender
    FROM @ResponseHeader rh
    WHERE completed = 1
) rh
LEFT JOIN (
    SELECT *
    FROM (
        SELECT 
              r.responseHeaderID
            , r.questionID 
            , r.answerItemsID
        FROM @Responses r
    ) t
    PIVOT 
    (
        MAX(answerItemsID) FOR questionID IN ([42], [43], [44], [48], [50], [49])
    ) p
) t2 ON rh.id = t2.responseHeaderID
ORDER BY rh.id

输出 -