脚本

查询A是(原始版本)：

SELECT userid
FROM mem..ProfileResult
WHERE ( ( QuestionID = 4
          AND QuestionLabelID = 0
          AND AnswerGroupID = 4
          AND ResultValue = 1
        )
        OR ( QuestionID = 14
             AND QuestionLabelID = 0
             AND AnswerGroupID = 19
             AND ResultValue = 3
           )
        OR ( QuestionID = 23
             AND QuestionLabelID = 0
             AND AnswerGroupID = 28
             AND ( ResultValue & 16384 > 0 )
           )
        OR ( QuestionID = 17
             AND QuestionLabelID = 0
             AND AnswerGroupID = 22
             AND ( ResultValue = 6
                   OR ResultValue = 19
                   OR ResultValue = 21
                 )
           )
        OR ( QuestionID = 50
             AND QuestionLabelID = 0
             AND AnswerGroupID = 51
             AND ( ResultValue = 10
                   OR ResultValue = 41
                 )
           )
      )
GROUP BY userid
HAVING COUNT(*) = 5

我使用’set statistics time on’和’set statistic io on’来检查cpu时间和io性能.

结果是：

cpu time = 47206 ms,elapsed time = 20655 ms.

我通过使用Set Operation重写了Query A,让我把它命名为Query B：

SELECT userid
FROM ( SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 4
            AND QuestionLabelID = 0
            AND AnswerGroupID = 4
            AND ResultValue = 1
       INTERSECT
       SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 14
            AND QuestionLabelID = 0
            AND AnswerGroupID = 19
            AND ResultValue = 3
       INTERSECT
       SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 23
            AND QuestionLabelID = 0
            AND AnswerGroupID = 28
            AND ( ResultValue & 16384 > 0 )
       INTERSECT
       SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 17
            AND QuestionLabelID = 0
            AND AnswerGroupID = 22
            AND ( ResultValue = 6
                  OR ResultValue = 19
                  OR ResultValue = 21
                )
       INTERSECT
       SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 50
            AND QuestionLabelID = 0
            AND AnswerGroupID = 51
            AND ( ResultValue = 10
                  OR ResultValue = 41
                )
     ) vv;

cpu时间和经过时间是：

cpu time = 8480 ms,elapsed time = 18509 ms

我的简单分析

从最高结果可以看出,查询A的cpu时间超过了经过时间的2倍

我搜索这种情况,大多数人都说cpu时间应该小于Elapsed time,因为cpu时间是cpu运行这个任务多长时间.经过的时间包括I / O时间和其他类型的时间成本.但是一个特例是当服务器有多个核心cpu时.但是,我刚刚检查了开发数据库服务器,它有一个单核cpu.

问题1

如何在单核cpu环境中解释cpu时间超过查询A中的经过时间？

问题2

之后,使用set操作,性能是否真的提升了？

我有这个问题,因为查询B的逻辑读取是280627,高于查询A的241885

Brad McGehee在他的文章中说‘The fewer the logical reads performed by a query,the more efficient it is,and the faster it will perform,assuming all other things are held equal.’

或者,它是否正确地说即使查询B具有比查询A更高的逻辑读取,但cpu时间明显小于查询A,查询B应该具有更好的性能.