Allen is right that you don't want to execute the entire query against the database every time the display of the data subset changes. But there are two ways you can go about that:
1. Execute the full query once, cache the results, and pull the page data out of the cached results. This is appears to be the approach used by the Pagination.cfc project on RIAForge. 2. Execute a query for just the results you need each time you need them. As Allen points out, if you only use a little bit of the data, getting the full query is inefficient. However, note that if you are going to use more than just a single page of data, every subsequent page will have better performance coming from the application's cache than it will coming from another round trip to the database. On the other hand, you'll of course use more memory with application caching. And if your data is changing rapidly, you should be using LiveCycle Data Services. Wait, that's not what I was going to type! But it's true. But what I was going to type is that if your data is changing rapidly and you're not using LiveCycle Data Services (which can handle the issue for you), doing a round trip to the database for subsequent pages will give you current data and you of course won't get that from cached data. In any event, you can see that there are pros and cons to both approaches. Josh From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Monday, February 23, 2009 7:10 AM To: [email protected] Subject: RE: [ACFUG Discuss] CF 7 Pagination - Query rerun making pagination useless Ajas, if you're using SQL Server 2005 or Oracle, there are much better solutions...the problem with what you showed in the first link is you (the server) is always retrieving ALL rows from the given table and pushing those across the pipe from your SQL server to your web server. There are two things majorly wrong with this - 1. if you're really using paging, you're using only a subset of these rows and throwing the rest out. That means if you have 50,000 records in the table and you're only using 1,000, you're basically wasting your db server's I/O (and possibly CPU if you're doing any special groupings or anything), plus you're eating up bandwidth. 2. You're now forcing the web server to wait to retrieve all of this data, store it up, and then only use a portion of it. A better way (in my opinion) to do this is to use what's available through your db for paging and go with that. If you're using SQL Server 2005 looking into the ROW_NUMBER() OVER functionality. You can retrieve x number of rows at a time which saves I/O on your SQL Server (assuming proper indexing), your Web Server, and your bandwidth...here's a sample of how you'd do it in SQL Server 2005: DECLARE @startRow INT, @endRow INT SELECT @startRow = 11, @endRow = 20 SELECT * FROM( SELECT ROW_NUMBER() OVER (ORDER BY columnA, ColumnB DESC) AS rowNum, columnA, columnB, .... FROM myTable WHERE 1=1 AND columnA = '...' AND columnB='....' )x WHERE x.rowNum BETWEEN @startRow AND @endRow If you're using Oracle (which I have limited experience in) you can use the built in rownum feature to do your paging selects. I'm sure other RDBM's out there have options to accomplish this as well. There are several options out there that can do what you need. The way that was shown in that first link you provided is the old way of doing things that is probably the worst of all worlds. If you have questions, drop me a line. Allen ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Ajas Mohammed Sent: Sunday, February 22, 2009 1:49 PM To: [email protected] Subject: [ACFUG Discuss] CF 7 Pagination - Query rerun making pagination useless Hi, Since these days, I am looking at optimization issues as you can tell from other posts, I have been thinking of pagination for quite sometime now. I have it working for a site and it works fine. There are plenty of solutions out there and one provided by Ben Forta in his CF 7 book. I like these 2 impementations 1. http://www.johnwbartlett.com/CF_tipsNtricks/index.cfm?TopicID=72 2. http://paginationcfc.riaforge.org/ But if the query is run *every time* when you go to next page, inst it defeating the very purpose of pagination i.e. less load in performing query to get 1000's of records at once. Anyone knows how to tackle this problem, so that query is run once and then you can move around in the record set by next previous links. I am curious to know how other people are doing this. Thanks, <Ajas Mohammed /> http://ajashadi.blogspot.com We cannot become what we need to be, remaining what we are. No matter what, find a way. Because thats what winners do. You can't improve what you don't measure. Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives. ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink<http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
