yet another coldfusion blog

I use MySQL. I love MySQL. According to a great deal of feedback I've received re: Meld Forums being MySQL only, I seem to be in the minority (at least in the former). So, in making Meld Forums MS SQL-friendly, I made the startling, depressing discovery that MS SQL *doesn't* *have* *LIMIT* functionality. You can use TOP to get the first X rows of a query, but you cannot specify a start position.

This all comes down to the need for paging records in an application. In MySQL, getting a block of records is as easy as "LIMIT 50,50" to get records 51-100. In MS SQL the most common solution to this is to nest an identical child query within a parent query, with the aim of removing a subset of records from the returned results.

For example, say you have a list of products that you are searching. You only want to return 10 at a time, so in MySQL you would write the following query:

<cfquery name="myproducts"> SELECT id,name,price FROM products LIMIT 0,10 </cfquery>

This will return the first 10 records, starting at record 0. LIMIT 10,10 will return records 11-20. In MS SQL, you cannot use TOP 10,10 ... it does not accept a start position as an argument. The standard way to replicate this functionality is as follows:

<cfquery name="myproducts"> SELECT TOP 20 id,name,price FROM products WHERE id NOT IN ( SELECT TOP 10 id FROM products ) </cfquery>

So, we are returning the first 20 records, minus the first 10 records. This looks pretty straight forward, but once you start throwing in ordering, groups, multiple joins etc. things get ugly and complicated very quickly. It also makes the process of development much more complicated, which is something I absolutely hate.

A very good balance between ease of development and performance is to employ Query of Queries to do the LIMIT replication. The process is as follows:

<!--- I want records 51-60 This means my "start" point is 50, and my record "count" is 10 ---> <cfset request.dbtype = 'MSSQL'> <cfset request.start = 50> <cfset request.count = 10> <cfquery name="myproducts"> SELECT <!--- if this is a MS SQL db, we want to return the top start + count ---> <cfif request.dbtype eq "MSSQL"> TOP #(request.start + request.count)# </cfif> id,name,price FROM products <!--- if this is a MYSQL db, we can use LIMIT to get our start + count total and we are finished ---> <cfif request.dbtype eq "MYSQL"> LIMIT #request.start#,#request.count# </cfif> </cfquery> <!--- if this is a MS SQL db, we have more work to do ---> <cfif request.dbtype eq "MSSQL"> <!--- first, get the first 50 (start) we are excluding. We only need the primary key. ---> <cfquery name="myproducts_exclude" dbtype="query"> SELECT TOP #request.start# id FROM myproducts </cfquery> <!--- next, we get the 10 (count) records we actually want returned ---> <cfquery name="myproducts_keep" dbtype="query"> SELECT TOP #request.count# * FROM myproducts WHERE <!--- now we use the primary key to exclude the 50 'start' records we retrieved above ---> id NOT IN (<cfqueryparam value="#valueList(myproducts_exclude.id)#" list="true" />) </cfquery> <!--- to finish, we re-assign the QoQ to the initial query variable ---> <cfset myproducts = myproducts_keep> </cfif>

The process is that we a) select the block of 'skipped' records plus the block of 'returned' records from the database, then b) select the block of records that we don't want returned, and finally c) remove the unwanted records from our initial set by excluding them.  Technically we don't need to limit the return set in the final query as we should only have our block of returned rows left over, but I leave it in just in case the limit in the initial query was forgotten (it's also a good 'debug' technique because it ensures only the requested block of rows is returned, and any deviation from that will be instantly noticeable).

So, we have essentially duplicated the standard "exclude query within the query" methodology, but have saved ourselves a ton of development time by not having to replicate what might be a very complex query. The db engine is still doing the majority of the heavy lifting in our initial query, and for MS SQL we use QoQ's to limit our results.

Now, I'll be the first to admit that this isn't always going to be as performance-efficient as doing this all in the database query, but depending upon the complexity of the query this method can actually be faster! The performance improvement can come when you have a very complex query that includes sorting and grouping ... in these cases the standard 'nested' method in effect doubles the 'heavy' work your db server has to do (once in the outer query, and repeated again in the exclude sub-query). In the QoQ we are doing a simple exclusion on the primary key, which is fast and highly optimized. Balancing this method against the complexity of writing the nested queries, I take the minor performance hit as an acceptable balance against the loss of LIMIT functionality in MS SQL.