I use a combination of 2 queries, which are VERY fast.
<cfset start = 50>
<cfset end = 75>
<cfquery name="ID">
SELECT t.TicketID
FROM Tickets t
WHERE 1 = 1 AND whateverelse....
</cfquery>
then
SELECT t.TicketID, etc.
FROM Tickets t
WHERE 1=1 AND same whatever else from above
AND t.TicketID > #ID["TicketID"][Start]# AND
t.TicketID < #ID["TicketID"][End]#
Basically, this gets ALL the primary keys from the table I'm grabbing from.
Then the second query grabs only the page you are on. So if you wanted to
grab rows 50-75, the first query would return all rows (can be a lot, but
should be fast), and then the second query would only select the ones where
the ID was greater than the 50th entry of ID's and smaller than the 75th
entry of ID's. This seems to work great for us, and executes in under 1
second for even large queries.
Cedric
> Subject: SQL Server 2000 and Pagination
> From: "Calvin Ward" <[EMAIL PROTECTED]>
> Date: Mon, 22 Dec 2003 08:29:43 -0500
> Thread:
http://www.houseoffusion.com/cf_lists/index.cfm/method=messages&threadid=29454&forumid=4#147856
>
> 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]
