MSSQL and Pagination
Ray posted a nice entry about ColdFusion and Pagination, useful and easy to implement. I commented that it should only be used for small recordsets, because even if you do not show all records, transferring 100k entries from the DB to the application server to only display 10 or 20 is not efficient at all.
Some people suggested using LIMIT and OFFSET, which can only by done with mysql.... I hate to admit it, but mssql is so much better than mysql, expect for that ... I can't believe that we still don't have that functionality... anyways, Adam also posted a link to an msdn article explaining a couple of different approaches to tackle this problem.
I, on the other hand, have been using a stored procedure for years to achieve this goal. I have it in production retrieving pages out of a table with over 5 million records... at it works flawlessly. Let me add that you do need indexes, defragging, maintenance, lots of RAM ... this SP doesn't do miracles :)
You can download the SP here. It works perfectly with mssql 2000 / 2005. Let me emphasize that I did not write this. I got it from a sql exchange article a few years ago. Yes, it can be improved I guess, since one of its major limitations is lacking of a way to query over left/right outer joins. You can query aggregated functions, embedded selects, inner joins (ansi format) ... You can still get around them using views, which is how I've done it in the past... in fact, if the query is too complicated, you might as well write a view for it anyways.
As far as the ColdFusion side, here's an invocation I have to one of my tables:
get multiple users in pagination
--->
<cffunction name="getUsers" access="public" output="No" returntype="struct">
<cfargument name="UserName" required="No" type="string" default="" />
<cfargument name="Alias" required="No" type="string" default="" />
<cfargument name="ageFrom" required="No" type="numeric" default="0" />
<cfargument name="ageTo" required="No" type="numeric" default="0" />
<cfargument name="gender" required="No" type="string" default="0" />
<cfargument name="Race" required="No" type="string" default="0" />
<cfargument name="Admin" required="No" type="numeric" default="0" />
<cfargument name="groupNumber" required="No" type="numeric" default="1" />
<cfargument name="groupSize" required="No" type="numeric" default="5" />
<cfset var returnStruct = structNew() />
<cfset var qGetUsers = '' />
<cfset var recordcount = '' />
<cfset var SqlCols = '' />
<cfset var SqlWhere = '' />
<cfset var OrderBy = '' />
<!--- columns --->
<cfsavecontent variable="SqlCols">
pk_users, UserName, Alias, gender, Age, race
</cfsavecontent>
<!--- condition --->
<cfoutput>
<cfsavecontent variable="SqlWhere">
0 = 0
<cfif arguments.Admin>
AND Admin = 1
<cfelse>
AND Admin = 0
</cfif>
<cfif len(arguments.UserName)>
AND UserName like '#arguments.UserName#%'
</cfif>
<cfif len(arguments.Alias)>
AND Alias like '#arguments.Alias#%'
</cfif>
<cfif arguments.ageFrom>
AND AGE >= #arguments.ageFrom#
</cfif>
<cfif arguments.ageTo>
AND AGE <= #arguments.ageTo#
</cfif>
<cfif arguments.gender neq '0'>
AND gender like '#arguments.gender#%'
</cfif>
<cfif arguments.Race neq '0'>
AND Race = '#arguments.Race#'
</cfif>
</cfsavecontent>
</cfoutput>
<!--- order --->
<cfsavecontent variable="OrderBy">
UserName
</cfsavecontent>
<!--- query --->
<cfstoredproc procedure="sp_selectnextn" datasource="#variables.instance.dsn#">
<cfprocresult name="qGetUsers" resultset="1">
<cfprocresult name="recordcount" resultset="2">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="TableName" value="users">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="Columns" value="#SqlCols#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="IdentityColumn" value="pk_users">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="GroupNumber" value="#arguments.groupNumber#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="GroupSize" value="#arguments.groupSize#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlWhere" value="#SqlWhere#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlOrderBy" value="#OrderBy#">
</cfstoredproc>
<cfset returnStruct.rs = qGetUsers />
<cfset returnStruct.rc = recordcount.countAll />
<cfreturn returnStruct />
</cffunction>
It basically takes the table(s), columns, primary key to use for pagination, page size and number, where statement, and order by statement. There is a limitation of size of your statements ... You won't be able to use a Where clause larger than 4000 characters.
The original SP did not have the full recordcount, which allows to calculate the number of pages. It does require more CPU, but I didn't want to just guess if there's a next page given the count in one particular page.
If you have any comments or suggestions, feel free to add them below.
Enjoy!
http://www.robgonda.com/blog/trackback.cfm?D44EF3CA-3048-7431-E45806503C72CE2B
