Viewing "pages" within a large table

Using CF and a very large Oracle table, how do I view "pages" of say 20 records at a time without having to retrieve the entire table each time? I am trying to solve a performance issue here.

Using the SELECT * FROM TABLENAME query may return several thousand records. I need to limit the query somehow by "page" number. eg. I want page 3 of that same query, showing 20 records.

Please help.
LVL 1
TimelessAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rod_nolanCommented:
Hi Timeless,

Well, you can limit the number of records returned from a query by setting the maxrows attribute of the cfquery tag to a number (20, for example) but it sounds like you want to create a "next n of m records" interface.

There's a section in Ben Forta's book (the ColdFusion Web Application Construction Kit, p. 498) on that very topic but as far as I understand, you need to execute the query each and every time you click the "Next 20 Records" button and you use a local variable as a pointer to determine which 20 record section of the total recordset to display on the next/previous page.

I know that this doesn't solve your problem but if you want to know more about this approach, let me know.

Good Luck,
Rod
0
TimelessAuthor Commented:
Thanks for the response. I don't have access to that book at the moment. Any chance of explaining the key points to how it works?

Any other comments on how to do this are also still welcome.
0
rod_nolanCommented:
OK, here's an example of a simple template that uses this technique. I'd give a more detailed explanation but the laptop battery is running low and I'm running out of time...

Here's it is, the stripped down version of the list page for a simple contact application that I created for personal use with an Access database.

[ start list.cfm ]


<cfset MaxRows = 5>
<cfparam name="START" default="1">

<cfquery name="getContacts" datasource="#Application.DSN#">
SELECT FIRSTNAME, LASTNAME
FROM Contacts
ORDER BY LastName
</cfquery>

<!--- the important part is in the cfoutput tag, not the cfquery tag, which stays the same --->

<cfoutput query="getContacts" startrow=#start# maxrows=#MaxRows#>
#getContacts.FirstName# #getContacts.LastName#</a> <br>
</cfoutput>


<cfset PrevStart = Start - MaxRows>
<cfset NextStart = Start + MaxRows>

<!--- note that the action attribute of the form tags for the next and previous buttons just points back on itself --->


<table><tr><td>
<!--- only display PREVIOUS button if you're not at the beginning and there previous records to display --->
<cfif PrevStart GTE 1>
<cfoutput>
<form action="list.cfm" method="post">
<input type="hidden" name="start" value="#PrevStart#">
<input type="submit" value="Previous #MaxRows#">
</form>
</cfoutput>
</cfif>
</td>

<td>
<!--- only display NEXT button if you're not at the end and there next records to display --->
<cfif NextStart LTE getContacts.RecordCount>
<cfoutput>
<form action="list.cfm" method="post">
<input type="hidden" name="start" value="#NextStart#">
<input type="submit" value="Next #MaxRows#">
</form>
</cfoutput>
</cfif>
</td></tr></table>


[ end list.cfm ]

If you have questions or other concerns that are related to your specific situation (I admit, this is a very simple example) then we can deal with them as they arise. And if you can get your hands on a copy of Forta's Web Application Construction Kit, 3rd ed., (ISBN 0-7897-1809-x) I'd recommend pages 498-506 or all of Chapter 21 for a fuller, more detailed example.

Good Luck,
Rod
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimelessAuthor Commented:
Thanks for your valuable time. With your assistance I have been able to significantly reduce the database access times. I appreciate your help and keep up the good work!
0
rod_nolanCommented:
My pleasure! I'm glad I could help.
Rod
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.