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: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Ajas Mohammed Sent: Sunday, February 22, 2009 1:49 PM To: discussion@acfug.org 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 http://www.fusionlink.com -------------------------------------------------------------