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

Question

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.

Answer 1

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

• there are parallelism operators in the plan = more than one logical processor visible to the OS and SQL Server. So it's either multiple core or hyper-threaded

Try EXEC xp_msver

Answer 2

In my case- SQL Server Execution Times: CPU time = 671 ms, elapsed time = 255 ms.

CPU time was nearly three times bigger than the elapsed time for query. Because the query was processed in parallel, the CPU burden was very high, and the CPU could become a bottleneck for this scenario.

SQL Server 2012 brings a solution to the CPU burden problem. It introduces iterators that process batches of rows at a time, not just row by row.

For query optimization you can Create columnstore index on your table-

CREATE COLUMNSTORE INDEX idx_cs_colname ON dbo.Tablename(feild1,feild2);