sql-serverperformancequery-optimizationcpu-time

SQL Server Query Tuning: why CPU Time is higher than Elapsed Time ? Are they relevant to set operation?

I have two query to filter some userid depend on question and its answers.

Scenario

Query A is (the original version):

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

I use 'set statistics time on' and 'set statistic io on' to check the cpu time and io performance.

the result is:

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

I rewrote Query A via using Set Operation, let me name it 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;

the CPU Time and Elapsed Time is:

CPU time = 8480 ms,  elapsed time = 18509 ms

My Simple Analysis

As you can see from up result, Query A have CPU Time more than 2 times of Elapsed time

I search for this case, mostly people say CPU time should less than Elapsed time, because CPU time is how long the CPU running this task. And the Elapsed time include I/O time and other sort of time cost. But one special case is when the Server has multiple Core CPU. However, I just checked the development db server and it has one single core CPU.

Question 1

How to explain that CPU time more than Elapsed time in Query A in a single core CPU environment?

Question 2

After, using set operation, Is the performance really improved?

I have this question because logical reads of Query B is 280627 which is higher than Query A's 241885

Brad McGehee said in his article that '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.'

Than, does it correctly say that even Query B have higher logical reads than Query A, but CPU time is significantly less than Query A, Query B should have a better performance.

Solution

  • If CPU is greater than elapsed, you do have a multi core or hyper-threaded CPU

    The CPU time is where the SQL Server Engine is installed. It isn't for a local Management Studio install.

    As for logical IO vs CPU, I'd go with lower CPU. If this runs often and overlapping, you'll run out of CPU resource first. I'd try a WHERE EXISTS (UNION ALL) construct and make sure I have good indexes.

    Edit, after comments

    Try EXEC xp_msver