Good points. Thanks for your comments. :) Dave
-----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 31, 2005 3:06 PM To: CF-Talk Subject: RE: Paging through records > I think it applies because the whole discussion was about > what's a 'faster' way to do this... > My point is that the second query is getting the data to > display on the page in question (say records 51 - 100), so > why does the first query need to return all rows of the > data? If the first query is purely to get a count (so you > know what to divide by to split up your pages) then it > seems to me to be unnecessary to return the additional > data that won't even be accessed. The second query (of > the 2 query approach) gets the actual data to display and > (in this scenario) would only contain 50 records. > The example I gave would of course need to have a WHERE > clause limiting to whatever the particular criteria is for > this page display...it wasn't just to get an arbitrary > count of all the records IN ADDITION to the 2 queries in > your approach. > The whole point was to say that using Count(ID) is faster > than using Count(*) or SELECT ID (getting all rows). Okay... Well... I was commenting specifically on the example where an ID column is fetched to filter the results being returned in the 2nd query, but I guess you're right, if you used another method like Oracle's MINUS clause to limit the records in the 2nd query, then you would need another method of getting the total number of records... however... I'm less inclined to think that approach would be faster because then you need all your where clause filters both in the data query and again in the count() query. My suspicion is that the execution plan for the query that uses just the primary key column (which you wouldn't have in this case) would be much more efficient than the execution plan for a query containing multiple references to non-primary columns in the where clause... I could be wrong, it's happened before. :) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ****************************************************************************************** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217040 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
