RE: SQL Server 2000 and Pagination

[Mike Townend]

the one we actually ues it for is quite slow, couple of seconds for a 10000
record set, but it is doing more than just select * from foo


HTH

-----Original Message-----
From: Calvin Ward [mailto:[EMAIL PROTECTED]
Sent: Monday, December 22, 2003 13:55
To: CF-Talk
Subject: Re: SQL Server 2000 and Pagination

Mike,

That looks pretty good, what's the performance like on large recordsets, do
you happen to know?

Thanks,
Calvin
  ----- Original Message -----
  From: Mike Townend
  To: CF-Talk
  Sent: Monday, December 22, 2003 8:33 AM
  Subject: RE: SQL Server 2000 and Pagination

  i tend to use this...

  CREATE PROCEDURE [dbo].[spr_PagedItems]
  @Page   int,
  @RecsPerPage int,
  @Tournement  int
  AS

  -- We don't want to return the # of rows inserted
  -- into our temporary table, so turn NOCOUNT ON
  SET NOCOUNT ON

  --Create a temporary table
  CREATE TABLE #TempItems
  (
  tempID int IDENTITY,
  ScoreID int,
  GameID int,
  PlayerID int
  )

  -- Insert the rows from tblItems into the temp. table
  INSERT INTO #TempItems (ScoreID, GameID, PlayerID)
  SELECT ScoreID, GameID, PlayerID FROM Scores WHERE tID = @Tournement ORDER
  BY ScoreTotal DESC

  -- Find out the first and last record we want
  DECLARE @FirstRec int, @LastRec int
  SELECT @FirstRec = (@Page - 1) * @RecsPerPage
  SELECT @LastRec = (@Page * @RecsPerPage + 1)

  -- Now, return the set of paged records, plus, an indiciation of we
  -- have more records or not!
  SELECT *, (SELECT COUNT(tempID) FROM #TempItems TI WHERE TI.tempID >=
  @LastRec) AS MoreRecords
  FROM #TempItems
  WHERE tempID > @FirstRec AND tempID < @LastRec

  -- Turn NOCOUNT back OFF
  SET NOCOUNT OFF

  -----Original Message-----
  From: Calvin Ward [mailto:[EMAIL PROTECTED]
  Sent: Monday, December 22, 2003 13:30
  To: CF-Talk
  Subject: SQL Server 2000 and Pagination

  Happy Holidays!

  I'm curious how folks are implementing pagination using SQL Server 2000
and
  avoiding returning entire recordsets to CF.

  In other words, if I have a search result set of 1,000 rows, and the user
  will be walking through that recordset 25 rows at a time using <prev>
  <pagenumbers> <next> methods, how are folks limiting the result set to
only
  the current 25 rows needed to display.

  Thanks,
  Calvin
    _____
  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---