My take on CF/SQL pagination
Raymond Camden has an entry on ColdFusion and Pagination explaining how to do result set pagination with CF code.
In a coment and following up with a post on his blog Rob Gonda sugests that for large recordsets the pagination should be done in the SQL rather than with CF code, utilizing stored procedures.
So Ray's method is easy to use/understand and works with any DB, but is not suitable for large recordsets, and also the code is in the "view" part of your application. Rob's method is good when performance becomes a problem because of large recordsets, but is tied to the db, and requires knowledge of stored procedures.
In all fairnes, I should get moving and learn the more advanced workabouts with SQL, but I've not yet.
So a while ago I was faced with the problem that I needed to deliver paged recordsets from a MSSQL db from a cfc to a Flash UI. So the method I've used earlier (very similar to Ray's) was unsuitable. In MySQL it can easily be done with use of LIMIT and OFFSET, but it was a MSSQL db, and I did not have access (or knowledge) enough to do it with stored procedures (or other zillion lines long SQL examples I found online for solving this).
I ended up with a fairly simple, yet very usable way of doing it. I'm not sure how it would hold with very large recordsets, but in the given situation that was not an issue.
Here's the (pseudo) code:
In a coment and following up with a post on his blog Rob Gonda sugests that for large recordsets the pagination should be done in the SQL rather than with CF code, utilizing stored procedures.
So Ray's method is easy to use/understand and works with any DB, but is not suitable for large recordsets, and also the code is in the "view" part of your application. Rob's method is good when performance becomes a problem because of large recordsets, but is tied to the db, and requires knowledge of stored procedures.
In all fairnes, I should get moving and learn the more advanced workabouts with SQL, but I've not yet.
So a while ago I was faced with the problem that I needed to deliver paged recordsets from a MSSQL db from a cfc to a Flash UI. So the method I've used earlier (very similar to Ray's) was unsuitable. In MySQL it can easily be done with use of LIMIT and OFFSET, but it was a MSSQL db, and I did not have access (or knowledge) enough to do it with stored procedures (or other zillion lines long SQL examples I found online for solving this).
I ended up with a fairly simple, yet very usable way of doing it. I'm not sure how it would hold with very large recordsets, but in the given situation that was not an issue.
Here's the (pseudo) code:
<cffunction access="remote" name="getNews"> <cfargument default="5" type="numeric" name="maxrows"> <cfargument default="0" type="numeric" name="startrow">
<cfquery datasource="#application.dsn#" name="getNews"> SELECT TOP #arguments.maxrows# *
FROM news
WHERE objectid NOT IN (
SELECT TOP #arguments.startrow# objectid
FROM news
ORDER BY datetimecreated DESC
)
ORDER BY datetimecreated DESC
</cfquery>
<cfreturn getNews>
</cffunction>
<cfquery datasource="#application.dsn#" name="getNews"> SELECT TOP #arguments.maxrows# *
FROM news
WHERE objectid NOT IN (
SELECT TOP #arguments.startrow# objectid
FROM news
ORDER BY datetimecreated DESC
)
ORDER BY datetimecreated DESC
</cfquery>
<cfreturn getNews>
</cffunction>
If you have some code samples illustrating your sugested method that'd be really great.