Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jun 11th, 2012  |  syntax: None  |  size: 2.00 KB  |  hits: 10  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. SQL Server Query Tuning: why CPU Time is higher than Elapsed Time ? Are they relevant to set operation?
  2. SELECT  userid
  3. FROM    mem..ProfileResult
  4. WHERE   (
  5.          (
  6.           QuestionID = 4 AND
  7.           QuestionLabelID = 0 AND
  8.           AnswerGroupID = 4 AND
  9.           ResultValue = 1
  10.          ) OR
  11.          (
  12.           QuestionID = 14 AND
  13.           QuestionLabelID = 0 AND
  14.           AnswerGroupID = 19 AND
  15.           ResultValue = 3
  16.          ) OR
  17.          (
  18.           QuestionID = 23 AND
  19.           QuestionLabelID = 0 AND
  20.           AnswerGroupID = 28 AND
  21.           (ResultValue & 16384 > 0)
  22.          ) OR
  23.          (
  24.           QuestionID = 17 AND
  25.           QuestionLabelID = 0 AND
  26.           AnswerGroupID = 22 AND
  27.           (ResultValue = 6 OR ResultValue = 19 OR ResultValue = 21)
  28.          ) OR
  29.          (
  30.           QuestionID = 50 AND
  31.           QuestionLabelID = 0 AND
  32.           AnswerGroupID = 51 AND
  33.           (ResultValue = 10 OR ResultValue = 41)
  34.          )
  35.         )
  36. GROUP BY userid
  37. HAVING  COUNT(*) = 5
  38.        
  39. CPU time = 47206 ms,  elapsed time = 20655 ms.
  40.        
  41. SELECT userid FROM(
  42. SELECT  userid
  43. FROM    mem..ProfileResult
  44. WHERE   QuestionID = 4 AND
  45.         QuestionLabelID = 0 AND
  46.         AnswerGroupID = 4 AND
  47.         ResultValue = 1
  48. INTERSECT
  49. SELECT  userid
  50. FROM    mem..ProfileResult
  51. WHERE   QuestionID = 14 AND
  52.         QuestionLabelID = 0 AND
  53.         AnswerGroupID = 19 AND
  54.         ResultValue = 3
  55. INTERSECT
  56. SELECT  userid
  57. FROM    mem..ProfileResult
  58. WHERE   QuestionID = 23 AND
  59.         QuestionLabelID = 0 AND
  60.         AnswerGroupID = 28 AND
  61.         (ResultValue & 16384 > 0)
  62. INTERSECT
  63. SELECT  userid
  64. FROM    mem..ProfileResult
  65. WHERE   QuestionID = 17 AND
  66.         QuestionLabelID = 0 AND
  67.         AnswerGroupID = 22 AND
  68.         (ResultValue = 6 OR ResultValue = 19 OR ResultValue = 21)
  69. INTERSECT
  70. SELECT  userid
  71. FROM    mem..ProfileResult
  72. WHERE   QuestionID = 50 AND
  73.         QuestionLabelID = 0 AND
  74.         AnswerGroupID = 51 AND
  75.         (ResultValue = 10 OR ResultValue = 41)
  76. )vv
  77.        
  78. CPU time = 8480 ms,  elapsed time = 18509 ms
create a new version of this paste RAW Paste Data