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 http://www.fusionlink.com
-------------------------------------------------------------
