It just doesn't make sense to me for a query to go after 100,000 records just to end up on a web page.
I would prefer to run much smaller (page size) individual queries as this will return much faster than all that unneeded server processing. Then re-query for the next in the series when the visitor clicks "next" Each time you click "next" fresh data is delivered. If the information is changing that often, then I add a "refresh" button on the page. ====================================== Stop spam on your domain, use our gateway! For hosting solutions http://www.clickdoug.com Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1 and all databases. ISP rated: http://www.forta.com/cf/isp/isp.cfm?isp_id=772 Suggested corporate Anti-virus policy: http://www.dshield.org/antivirus.pdf ====================================== If you are not satisfied with my service, my job isn't done! ----- Original Message ----- From: "Joshua Miller" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, September 10, 2003 10:08 AM Subject: RE: Paging Principals | Not necessarily a bad idea even if the content changes. Think about it, | if you pull the entire recordset and page through it, if the content | changes while you're browsing you're still looking at old content. We do | this with large recordsets (as many as 100,000 rows) and what we do is | cache the query for 15 minutes, plenty of time to browse the recordset. | If a user makes a modification then we release the query cache and the | next person to hit the Next-N scenario creates a new query cache based | on the new data. The speed gain for the end-user was tremendous compared | with going back to the database each time and pulling the next however | many records. | | We tried your scenario number 2 and things were considerably slower for | us than just running the whole query outright and caching it. Granted | you need a lot more RAM, but it's cheap. I'd rather buy more RAM and | have the application run quicker for the end-user. | | Joshua Miller | Head Programmer / IT Manager | Garrison Enterprises Inc. | www.garrisonenterprises.net | [EMAIL PROTECTED] | (704) 569-0801 ext. 254 | | ************************************************************************ | ************* | Any views expressed in this message are those of the individual sender, | except where the sender states them to be the views of | Garrison Enterprises Inc. | | This e-mail is intended only for the individual or entity to which it is | addressed and contains information that is private and confidential. If | you are not the intended recipient you are hereby notified that any | dissemination, distribution or copying is strictly prohibited. If you | have received this e-mail in error please delete it immediately and | advise us by return e-mail to [EMAIL PROTECTED] | ************************************************************************ | ************* | | | -----Original Message----- | From: Geoffrey V. Brown [mailto:[EMAIL PROTECTED] | Sent: Wednesday, September 10, 2003 9:47 AM | To: CF-Talk | Subject: RE: Paging Principals | | | Hi, | Good idea. | A cached query wouldn't work for us, unfortunately, as the data changes | almost by the minute. | | Geoff B | | > -----Original Message----- | > From: Ben Doom [mailto:[EMAIL PROTECTED] | > Sent: Wednesday, September 10, 2003 9:43 AM | > To: CF-Talk | > Subject: RE: Paging Principals | > | > | > When I had something like this, I used the full query but only | > retrieved the | > top n, where n is the highest index to display. So, if you are | displaying | > 10 per page... | > | > page 1, n=10 | > page 2, n=20 | > | > and so on. Maybe not a perfect solution, but dropped average | processing | > time a lot, since people would generally either narrow the search or | only | > look at the first page or two. | > | > If you think people might be flipping through a large number of | > pages, look | > at query caching. | > | > | > -- Ben Doom | > Programmer & General Lackey | > Moonbow Software, Inc | > | > : -----Original Message----- | > : From: Geoffrey V. Brown [mailto:[EMAIL PROTECTED] | > : Sent: Wednesday, September 10, 2003 9:18 AM | > : To: CF-Talk | > : Subject: Paging Principals | > : | > : | > : Hi, | > : I have an app that queries a table and displays a subset of the | query | > : results in a paged format, showing only 10 results per page. | > Some queries | > : will return upwards of 1,000 rows. | > : | > : This query joins quite a few tables - say 8 or so, however the | > : query itself | > : only requires one table to obtain the basis of the dataset(the | > rest of the | > : tables in the join are used for display info or 'cosmetic' type | > : information). | > : | > : Generally speaking, which of the following would be more efficient: | > : | > : Option 1: | > : - Query database, joining 8 tables, get entire dataset (1000 rows) | > : - CFOUTPUT / Loop limited to start row (page ordinal) and maxrows | (10) | > : | > : Option 2: | > : - Query database only based on the results of one table (1000 rows) | > : - Cfoutput / Loop limited to start row (page ordinal) and maxrows | (10) | > : - within output loop, perform second query joining the 8 tables, | > : based on a | > : single primary key returned from the initial dataset. (returns | > single row) | > : | > : In essence, the question revolves around whether it is more | expensive to | > : pull an entire 1,000 record joined dataset in one shot, or to make | the | > : initial query lean, and then do ten consecutive queries | > : performing the join. | > : The former likely incurs more database load, while the latter | > would incur | > : more interaction with odbc. | > : | > : Whew. Interested in your answers! | > : | > : Thanks, | > : Geoffrey Brown | > : ________________________ | > : Online Operations Manager | > : Deerfield.com | > : 989.732.8856 ext. 227 (Gaylord) | > : 231.935.4640 ext. 123 (Traverse City) | > : [EMAIL PROTECTED] | > : | > : http://www.deerfield.com | > : Personal Service with a :-) | > : | > : VisNetic (viz-net-ik) 2002: vision of the Internet | http://www.visnetic.com | : | : | | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
