Need Records other than month end dates from a table

• R.P.Rozema

  SSChampion

  Points: 12317

  More actions

  October 14, 2010 at 9:14 pm

  #1237283

  It was a very minimal test. The table didn't have any indexes, not even a primary key. After I've added an index on the LockEndDate column, the results are more favor of the Tally solution:

  create index ixTestData_LockEndDate on dbo.TestData(LockEndDate)

  -----------

  966881

  (1 row(s) affected)

  Table 'TestData'. Scan count 1, logical reads 2358, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

  Table '#Tally______________________________________________________________________________________________________________000000000011'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:

  CPU time = 172 ms, elapsed time = 216 ms.

  SQL Server parse and compile time:

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

  -----------

  966881

  (1 row(s) affected)

  Table 'TestData'. Scan count 1, logical reads 2358, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:

  CPU time = 312 ms, elapsed time = 300 ms.

  SQL Server Execution Times:

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

  ---

  
  Posting Data Etiquette - Jeff Moden[/url]
  Posting Performance Based Questions - Gail Shaw[/url]
  Hidden RBAR - Jeff Moden[/url]
  Cross Tabs and Pivots - Jeff Moden[/url]
  Catch-all queries - Gail Shaw[/url]
  

  ---

  
  If you don't have time to do it right, when will you have time to do it over?

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  October 14, 2010 at 11:17 pm

  #1237304

  R.P.Rozema (10/14/2010)

  ---

  (tnx Jeff)

  You bet. Thanks for the feedback and thanks for the code to play with. It's always nice to see when folks gen more than 10 rows of data to test with.

  --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)