Rodrigo,
I see you're using oracle. I haven't done it, but can you make
it a procedure and pass it the rownum values for the next or previous
pages? Should be fairly simple. That should make the DBA feel better.
Let me know if you need a start writing it.
Greg
-----Original Message-----
From: Rodrigo Cohen [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 03, 2003 12:23 PM
To: CF-Talk
Subject: Pagination issues
Hi all !
I have a simple question about pagination for queries with big
resultsets...
:o)
I have a query that retuns about 1000 rows. But i show only 10 rows per
page. I read at many articles, to use startrow and endrow in CFOUTPUT
QUERY tag to make paginations but i dont like that way...
So i am making the same query with some new sql commands, that can
return to me, only the 10 rows per page that i want... (depending which
page i am). The original query with 1000 rows is: SELECT field_1,
field_2, field_3, field_4, field_5
FROM my_table
WHERE field_1 = 'aaaa'
AND field_2 = 'bbbb'
ORDER BY field_5
The new query with only 10 rows, showing the 3th page for example (from
row 21 to row 30), is: SELECT * FROM
(SELECT query_result.*, rownum r
FROM
(SELECT field_1, field_2, field_3, field_4, field_5
FROM my_table
WHERE field_1 = 'aaaa'
AND field_2 = 'bbbb'
ORDER BY field_5) query_result
where (rownum) < 31 )
where r > 20
At my ColdFusion Debug, the execution time of second query is a half of
the first query. To me, its perfect ! But my DBA MAN, sad to me, that
running Trace File (from Oracle), the attribute DISK (number of physical
reads of buffers from disk) increase with 25% from the frist to the
second...
So i wanna help ! :)
I am doing the right thing ? Or ColdFusion has another way to make
paginations with no all rows returned (like VB, DELPHI, or any other
language that i can use a pointer to database object, like DBGRID and
read only some rows from DB at time) ?
Note1: i cant use the MAXROWS attribute at my CFQUERY because i have
many pages and i need the start and end row...
Note2: i dont want to use cached queries because i have too many
paginations, with too many filters, with too many users and those
paginations filters are always changing.
Thanks a lot !
Rodrigo Cohen.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
