Ask a Jedi: Concerns about CFGRID performance

This post is more than 2 years old.

Brad asked:

I am just wondering if you know of any way for the html cfgrid on CF9 to only hit the database once, instead of hitting it for every separate sorting/pagination request. I know extJS can do all the processing for you, but it appears cfgrid is requesting the database every time which seems to not be the best way to do things as this could become resource intensive. I have the query it uses caching the roughly 4,000 rows, but I would imagine CF constantly having to hit the cache is still not very performance friendly?

Ok, so first off, we have two separate things going on here. We have the performance of the front end - in this case the HTML grid. On the other hand we have the performance of the back end. There are things I want to bring up about both of these. Let's first though focus on your first question. Is there a way to get all of the data into the grid at once. The answer is yes. Instead of using binding to load data into a grid you can use a cfquery on the same page and pass that query right into the cfgrid tag. This works - but I strongly recommend against this. 4000 rows of data will be quite a lot to push into the client all at once. I can understand your server side concerns - and we're going to get to that soon - but preloading all of the data at once on the front end is not going to scale well.

So - what about paging on the server? Brad pointed out that it seemed like on every hit (sorting, paging), he was getting 4000 rows from the database. Many examples of the Ajax-based grid demonstrate that. You'll see a simple query followed up by the queryConvertForGrid function. This function will "slice" a query into a page and return data in the form that cfgrid desires. However - you do not need to make use of this function. You can do paging in the database itself (trivial with MySQL) and then return the data in the same way as queryConvertForGrid.

queryConvertForGrid returns a structure with two keys. The first key, query, is the "page" of data. The second key is totalrowcount which represents how many records total exist. (Again, MySQL provides a way to do this: http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html)

Dave followed up with:

Do you recommend caching this data if it is hit continually? I normally cache queries that have 4k rows, but found that if i had 8 columns, i'd have 17 different cache's due to filtering by each column. Any thoughts?

If you start paging it on the DB side, I probably wouldn't bother with the caching. You could consider caching the first few pages if you want - but I'd start with just doing the database paging. What and when to cache is very application specific.

Finally - Brad asked me what I used for grids in jQuery - and for that I always recommend jqGrid. While I don't have the URLs handy (hopefully my readers will), I know someone in our community has done a few blog posts about ColdFusion and jqGrid integration.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Steve &aposCutter&apos Blades posted on 1/30/2011 at 12:08 AM

Can you show me an example of that linked method with a scripted query?

Comment 2 by Raymond Camden posted on 1/30/2011 at 12:10 AM

Sorry - linked method?

Comment 3 by Steve &aposCutter&apos Blades posted on 1/30/2011 at 12:14 AM

The link you provided in the post.

I came up with this

<code>
function getEntries(numeric start = 0,numeric numRec = 10,string orderby = "posted asc") {
var retVal = {};
retVal['success'] = true;
var sql = "SELECT SQL_CALC_FOUND_ROWS id,
title,
posted,
views
FROM tblblogentries
ORDER BY :orderby
LIMIT :start,:numRec";
var q = new Query(name="entries",datasource=VARIABLES.instance.dsn,sql=sql);
q.addParam(name = "start", value = ARGUMENTS.start, cfsqltype = "cf_sql_integer");
q.addParam(name = "numRec", value = ARGUMENTS.numRec, cfsqltype = "cf_sql_integer");
q.addParam(name = "orderby", value = ARGUMENTS.orderby, cfsqltype = "cf_sql_varchar");
try {
var result = q.execute().getResult();
q.setSql("SELECT FOUND_ROWS() as TotalCount");
var totResult = q.execute().getResult();
if (result.recordCount){
retVal['totalCount'] = totResult.totalCount;
retVal['result'] = result;
} else {
throw(type="My_Custom",errorcode="0001",message="No records were returned");
}
} catch (any excpt) {
retVal['success'] = false;
retVal['message'] = excpt.message;
}
return retVal;
}
</code>

Comment 4 by Raymond Camden posted on 1/30/2011 at 12:21 AM

Looks right to me.

Comment 5 by Steve &aposCutter&apos Blades posted on 1/30/2011 at 9:40 PM

My only worry with that solution is thread safety from MySQL perspective. We can run multiple queries in a single prepared statement (if it's added to the settings for the datasource), but I haven't found a way to retrieve the results of the additional queries when using a scripted query. By executing my sample code in two separate hits, my worry in a high traffic environment would be in getting the 'count' from a different session's query. I don't know if that would be the case (and can't really test it here). Any ideas on how one would get the additional results of a multi-statement query natively?

Comment 6 by Raymond Camden posted on 1/30/2011 at 9:47 PM

You could just skip the 'special' SQL and do a normal select(count) type SQL. Cache it and when you page over it you won't be rerunning it. Shoot, even if you don't cache it's just two queries per request which won't take more than a gnat's fart.

Comment 7 by Andy Sandefer posted on 1/31/2011 at 5:57 AM

@Ray
If you're ever looking for a rainy day blog topic I would love you see a CF/jqGrid blog entry.

Comment 8 by Raymond Camden posted on 1/31/2011 at 5:59 AM

Someone in our community already did - and if I remember right - a good 3 parter. Last time I googled I couldn't find it out - but it has been done - and done well if I remember right.

Comment 9 by Raymond Camden posted on 1/31/2011 at 6:00 AM

And of course, now that I look _again_ I find it immediately: http://www.coldfusion-ria.c...

Comment 10 by Dan Parker posted on 2/1/2011 at 1:43 AM

I've used jqGrid w/CF and MS SQL pagination using info from:
http://blog.pengoworks.com/...

<code>
<cfparam name="form.page" default="1">
<cfparam name="form.rows" default="20">
<cfparam name="form.sidx" default="">
<cfparam name="form.sord" default="desc">
<cfparam name="form.export" default="false">

<cfquery name="myquery" datasource="#APPLICATION.Datasource#">
with pagination as
(SELECT row_number() over (ORDER BY
<cfif len(form.sidx)>#form.sidx# #form.sord#<cfelse>Default</cfif>) as rowNo,
...query...
)
select *, (select count(*) from pagination) as totalResults
from pagination
<cfif NOT form.export>where
RowNo between #NumberFormat((form.rows*form.page)-form.rows+1,99999999999999999999)# and #NumberFormat(form.rows*(form.page),99999999999999999999)#
</cfif>
order by
rowNo
</cfquery>
</code>

export was our own option, for using the tabletogrid method w/o pagination, or reusing the query for csv export, etc. Probably not the best code, but should hopefully be a start for someone.

Comment 11 by Dan Parker posted on 2/1/2011 at 1:49 AM

We also have a wrapper for jqGrid that takes a CF Query, and a list of columns to create the JSON for the grid. Again probably not the best code, but maybe useful to someone as a start.
(Also has a debug variable check to optionally show the query and results in page for troubleshooting, we add it to our URLs)

<code>
<cfsetting enablecfoutputonly="yes" showdebugoutput="no">
<cfparam name="MyQuery" type="query">
<cfparam name="Columns" default="#MyQuery.ColumnList#">
<cfif isDefined("Columns") and Len(Columns)>
<!--- <cfheader name="Pragma" value="public">
<cfheader name="Cache-control" value="private">
<cfheader name="Expires" value="-1"> --->

<cfset jsonstr = createObject("java","java.lang.StringBuffer")>
<cfscript>
jsonstr.append("{" & Chr(13));
if(MyQuery.Recordcount gt 0) {
jsonstr.append('"total": ' & ceiling(MyQuery.TOTALRESULTS/form.rows) & ',' & Chr(13));
} else {
jsonstr.append('"total": 0,' & Chr(13));
}
jsonstr.append('"page": ' & form.page & ',' & Chr(13));
if(MyQuery.Recordcount gt 0) {
jsonstr.append('"records": ' & val(MyQuery.TOTALRESULTS) & ',' & Chr(13) & '"rows": [' & Chr(13));
} else {
jsonstr.append('"records": 0' & ',' & Chr(13) & '"rows": [' & Chr(13));
}
</cfscript>
<cfloop query="MyQuery">
<cfscript>
if(IsDefined("qCounter")){qCounter = IncrementValue(qCounter);}else{qCounter = 1;}
jsonstr.append('{"id":"' & rowno & '","cell":[');
rc = false;
</cfscript>
<cfloop index="i" list="#Columns#">
<cfscript>
if(IsDefined("vCounter")){vCounter = IncrementValue(vCounter);}else{vCounter = 1;}
if(rc){jsonstr.append(",");}
</cfscript>
<cfset jsonstr.append('"' & replace(replace(replace(Evaluate(i),Chr(13),"\n","all"),Chr(10),"","all"),"""","\""","all") & '"')>
<cfscript>rc=true;</cfscript>
</cfloop>
<cfscript>
jsonstr.append("]}");
if (qCounter != MyQuery.RecordCount) jsonstr.append("," & Chr(13));
</cfscript>
</cfloop>
<cfscript>
jsonstr.append("]" & Chr(13) & "}");
</cfscript>
<cfif NOT isDefined("debug")>
<cfcontent type="application/json" variable="#ToBinary(ToBase64(jsonstr.toString()))#" reset="yes" />
<cfelse>
<cfsetting enablecfoutputonly="no" showdebugoutput="True">
<cfoutput>#jsonstr.toString()#</cfoutput>
<cfdump var="#MyQuery#">
</cfif>
<cfelse>
Columns empty or not defined!
</cfif>
</code>