Think-Lab

LIMIT for MS SQL and ColdFusion

Posted by spiraldev, Posted on December 11, 2008 So I was added paging to a report and I didn't what to use <cfoutput startrow="1" maxrows="25">because I didn't want to run the whole which is about 100,000 records. I have used the LIMIT feature in MySQL and I could not find anything similar fo MS SQL. But I did find this. SELECT    TOP 10 *
FROM table TT1
WHERE   TT1.id NOT IN (
   SELECT TOP 10 t1.ID FROM table t1
   ) Which is cool to get the second set of ten. But I need to be able to do this dynamically and be able to get the first ten as well. So to get the first ten you need to remove the NOT. SELECT    TOP 10 *
FROM table TT1
WHERE   TT1.id IN (
   SELECT TOP 10 t1.ID FROM table t1
   ) And to get the second ten you add the NOT in SELECT    TOP 10 *
FROM table TT1
WHERE   TT1.id NOT IN (
   SELECT TOP 10 t1.ID FROM table t1
   ) Now to get the third set of ten you need to add 10 more to the subquery. SELECT    TOP 10 *
FROM table TT1
WHERE   TT1.id NOT IN (
   SELECT TOP 20 t1.ID FROM table t1
   ) So there you have it your own LIMIT for MS SQL. So obviously you don't want to do this manually, so I wrote a ColdFusion Function. <cffunction name="findRecords" access="public" output="false" returntype="query">
   <cfargument name="startRow" required="false" type="string" default="1" hint="The row offset">
   <cfargument name="maxRows"    required="false" type="string" default="0" hint="The max rows to retrieve">
   <cfset var qSelect = "">
   <cfquery name="qSelect" datasource="#dsn#">
       <cfif arguments.maxRows GT 0>
      SELECT    TOP #arguments.maxRows# *
       FROM TT1
      WHERE   TT1.id
         <cfif arguments.startRow gt 1>
            NOT
         </cfif>
         IN (
           </cfif>         
           SELECT
         <cfif arguments.maxRows gt 0>
          TOP
          <cfif arguments.startRow gt (arguments.maxRows*2)>
         #arguments.startRow +arguments.maxRows#
          <cfelse>
         #arguments.maxRows#
          </cfif>
          t1.ID
       <cfelse>
      *
       </cfif>
       FROM   t1
         
       <cfif arguments.maxRows GT 0>
       )
       </cfif>
   </cfquery>
   <cfreturn qSelect/>
</cffunction> Alright what am I doing in this function? First I check to see if the maxRows parameter is being passed. Second I am if the startRow is greater than one because if it is, it means we are not looking for the first set of maxRows <cfif arguments.startRow gt 1> Then I check to see if we need to get the third or greater dataset. <cfif arguments.startRow gt (arguments.maxRows*2)> Now comes the speed test.

as you can see even with doing the top 90,000 in the subquery its still faster than pulling all 90,000 and then doing <cfoutput startrow="1" maxrows="25"> One more thing I created an Illudium template which you can download here.

3 comments - Posted by spiraldev at 12:00 PM - Categories: SQL | Illudium | Coldfusion