Dave, This approach doesn't work if you have gaps in your keys, such as after you delete a row. For example, in my case, if your ID's were 1, 51, 101, etc. It would return the first 50 entries, regardless of what their ID's were. In your case, the COUNT doesn't tell you the ID's of the records you are looking for. So if your first query returned 100, and you then queried for records with ID > 50 and ID < 100, you would only get 1 row, instead of the 50.
Unless I missed your point completely :) Cedric > I agree with Isaac. The first query could even be: > > <cfquery name="TotalCount"> > select count(id) as TheCount > </cfquery> > > Wouldn't this make the first query even faster as it's not returning x > records, it's only returning ONE record and then you can reference > TotalCount.TheCount instead of .recordcount the other way? It seems > this would make it even more efficient. > > Yes, it's true that if your table is small, that this could end up > taking more time, but the whole idea is to handle tables that are > large, not small, so this to me seems the most efficient. > > Dave > > -----Original Message----- > From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 31, 2005 1:27 PM > To: CF-Talk > Subject: RE: Paging through records > > > > Not in this scenario. > > You have to select ALL records to extract or output the > > ones you want. > > > So my original example was > > > 1. query all the required records > > 2. output the ones you want > > > The one your saying is better > > > 1. query all the required records > > 2. query a subset of those records > > 3. output the records. > > > Thus an extra step to produce the same output. Of course > > querying the > > database a second time is going to take up more memory for > > the 2nd query and > > take longer to process. If you don't believe me, just try > > it for yourself. > > That's gonna depend on the indexing... the first query returns only > the primary key column (which cuts down on time to execute) and the > 2nd query has only the one primary key column filter in the where > clause, which is presumably faster than searching on other columns > (at > least, it definately will be if the db's indexing is done properly). > So it's possible the 2 query approach in this case could actually be > faster... Consider that in the alternative single-query approach, > you're returning x number of columns on y number of records that > won't > be used, which will create drag just like the additional query does > -- > but this 2 query approach also has advantages which reduce the time > to > complete each query, so... the end result will vary. > > > 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:217056 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
