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