Nested Select Statement Optimization

• Jeff Moden

  SSC Guru

  Points: 1004250

  More actions

  December 28, 2007 at 11:04 am

  #763350

  Then it's simple, Jason... Split what get's built for @SQL2 into two or 3 parts based on the number of RunStatuses (60 total) and change the execution to EXEC (@SQL1+@SQL2a+@SQL2b+@SQL2c+@SQL3)... 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004250

  More actions

  December 28, 2007 at 11:14 am

  #763352

  Koji Matsumura (12/28/2007)

  ---

  Jeff Moden (12/28/2007)

  ---

  Exactly what I did in my cross-tab code... nice, job, Koji...

  Jeff,

  with SET STATISTICS IO ON

  SELECT TV.Feature, TV.RunStatus, 100.00 * COUNT(*) / B.FeatureCount

  :

  GROUP BY TV.Feature, TV.RunStatus, B.FeatureCount

  :

  Mine above shows

  Table '#398D8EEE'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0.

  SELECT TV.Feature, TV.RunStatus, 100.00 * COUNT(*) / MAX(B.FeatureCount)

  :

  GROUP BY TV.Feature, TV.RunStatus

  :

  Yours above shows

  Table 'Worktable'. Scan count 5, logical reads 21, physical reads 0, read-ahead reads 0.

  Table '#398D8EEE'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0.

  Execution plans are also different.

  I wonder if it makes a difference when data volume is large.

  Regards

  Heh... I was wrong... you did it slightly differently that I did... here's my code...

  SELECT r.Feature,r.RunStatus AS RS,

  100.0*COUNT(*)/MAX(fc.FeatureCount) AS P

  FROM Raw r

  INNER JOIN

  (

  SELECT Feature, COUNT(*) AS FeatureCount

  FROM Raw

  GROUP BY Feature

  )fc

  ON r.Feature = fc.Feature

  GROUP BY r.Feature,r.RunStatus

  ...and here's your code (reformatted to match mine to make the comparison easier)...

  SELECT TV.Feature, TV.RunStatus,

  100.00 * COUNT(*) / B.FeatureCount

  FROM @raw AS TV

  INNER JOIN

  (

  SELECT Z.Feature, FeatureCount = COUNT(*)

  FROM @raw Z

  GROUP BY Z.Feature

  ) B ON B.Feature = TV.Feature

  GROUP BY TV.Feature, TV.RunStatus, B.FeatureCount

  ORDER BY TV.Feature, TV.RunStatus

  See the difference? I did a MAX on the FeatureCount where you did a GROUP BY on it... apparently, MAX is a wee bit more expensive than the GROUP BY... Thanks for the testing, Koji.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004250

  More actions

  December 28, 2007 at 11:25 am

  #763356

  Jeff Moden (12/28/2007)

  ---

  Koji Matsumura (12/28/2007)

  ---

  Jeff Moden (12/28/2007)

  ---

  Exactly what I did in my cross-tab code... nice, job, Koji...

  Jeff,

  with SET STATISTICS IO ON

  SELECT TV.Feature, TV.RunStatus, 100.00 * COUNT(*) / B.FeatureCount

  :

  GROUP BY TV.Feature, TV.RunStatus, B.FeatureCount

  :

  Mine above shows

  Table '#398D8EEE'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0.

  SELECT TV.Feature, TV.RunStatus, 100.00 * COUNT(*) / MAX(B.FeatureCount)

  :

  GROUP BY TV.Feature, TV.RunStatus

  :

  Yours above shows

  Table 'Worktable'. Scan count 5, logical reads 21, physical reads 0, read-ahead reads 0.

  Table '#398D8EEE'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0.

  Execution plans are also different.

  I wonder if it makes a difference when data volume is large.

  Regards

  Heh... I was wrong... you did it slightly differently that I did... here's my code...

  SELECT r.Feature,r.RunStatus AS RS,

  100.0*COUNT(*)/MAX(fc.FeatureCount) AS P

  FROM Raw r

  INNER JOIN

  (

  SELECT Feature, COUNT(*) AS FeatureCount

  FROM Raw

  GROUP BY Feature

  )fc

  ON r.Feature = fc.Feature

  GROUP BY r.Feature,r.RunStatus

  ...and here's your code (reformatted to match mine to make the comparison easier)...

  SELECT TV.Feature, TV.RunStatus,

  100.00 * COUNT(*) / B.FeatureCount

  FROM @raw AS TV

  INNER JOIN

  (

  SELECT Z.Feature, FeatureCount = COUNT(*)

  FROM @raw Z

  GROUP BY Z.Feature

  ) B ON B.Feature = TV.Feature

  GROUP BY TV.Feature, TV.RunStatus, B.FeatureCount

  ORDER BY TV.Feature, TV.RunStatus

  See the difference? I did a MAX on the FeatureCount where you did a GROUP BY on it... apparently, MAX is a wee bit more expensive than the GROUP BY... Thanks for the testing, Koji.

  Guess I was wrong about that, too... when I both code snippets against a "Real" table instead of a table variable, here's the results I get...

  Table 'Raw'. Scan count 7, logical reads 7, physical reads 0, read-ahead reads 0.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 2 ms.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 2 ms.

  ====================================================================================================

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  Table 'Raw'. Scan count 7, logical reads 7, physical reads 0, read-ahead reads 0.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 1 ms.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 4 ms.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 4 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  Not sure why your runs showed so differently, especially in the reads, but the runs I did against a real table show that my MAX code is twice as fast as your GROUP BY code 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)