Select top x VS Set RowCount

• Roust_m

  SSCoach

  Points: 17410

  More actions

  November 9, 2009 at 6:54 pm

  #211949

  Hi,

  I have an SP which sometimes runs very slow. It has a simple query like this one:

  SET ROWCOUNT @NumberToReturn

  SELECTC.URLSafeCompanyName,

  P.PressReleaseID,

  P.Title,

  P.URLSafeTitle,

  P.ReleaseDate

  FROMMyTable1 (nolock) P

  JOINMyTable2 (nolock) C ON C.CompanyID = P.CompanyID

  WHEREC.IsActive = 1

  ORDER BYP.ReleaseDate DESC

  The @NumberToReturn is passed as a parameter which is between 10 and 100. When I look at the query plan, it seems to join the tables first and then return top 10 or so records, so the lines in the plan are very thick with tens of thousands records/executions etc.

  If I change the query to 'Select top 10 ...' it runs very fast and the query plan shows very small number of records processed. Shouldn't they be the same? How can I make the first query to use a good plan?

  I've tried a workaround putting top 100 into the query:

  SET ROWCOUNT @NumberToReturn

  SELECT top 100 C.URLSafeCompanyName,

  P.PressReleaseID,

  P.Title,

  P.URLSafeTitle,

  P.ReleaseDate

  FROMMyTable1 (nolock) P

  JOINMyTable2 (nolock) C ON C.CompanyID = P.CompanyID

  WHEREC.IsActive = 1

  ORDER BYP.ReleaseDate DESC

  This worked, but it is not a very elegant way.

  Any ideas?

  Thanks.

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  November 9, 2009 at 10:03 pm

  #1077079

  In SQL Server 2005, you can use...

  SELECT TOP (@NumberToReturn)

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