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
