?
Solved

Viewing "pages" within a large table

Posted on 1999-11-19
5
Medium Priority
?
143 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:Timeless
  • 3
  • 2
5 Comments
 
LVL 1

Expert Comment

by:rod_nolan
ID: 2219595
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
 
LVL 1

Author Comment

by:Timeless
ID: 2220055
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
 
LVL 1

Accepted Solution

by:
rod_nolan earned 1600 total points
ID: 2222168
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
 
LVL 1

Author Comment

by:Timeless
ID: 2225521
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
 
LVL 1

Expert Comment

by:rod_nolan
ID: 2225871
My pleasure! I'm glad I could help.
Rod
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question