What do to want to get? The last know record inserted?
"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: [EMAIL PROTECTED] <[EMAIL PROTECTED]> To: SQL <[email protected]> Sent: Thu Nov 24 16:45:54 2005 Subject: Re: Max vs. Top 1 Any difference when there's other data in the mix (i.e. group by...)? >> Which of these are more efficient in a CFQUERY? >> Select max(threadid) >> OR >> select top 1 threadid >> .... >> order by threadid desc > > I just ran both types of queries against a table with 1.03 million records > on the primary key, and the first was faster showing 0ms time to process, > while the "order by" style took 16ms. > > When testing against an indexed field, the difference is unremarkable. > > I ran the queries again on a non-indexed varchar field in the same table > and > the difference was huge. The "max()" method won with only 3764 ms, while > the "order by" method took 18172 ms. > > I think it's safe to say that using max() is faster on non-indexed fields. > > > -Justin Scott > > > > PS: Tests were done from SQL Query Analyzer against MS SQL 7.0. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how to get a fax number that sends and receives faxes using your current email address http://www.houseoffusion.com/banners/view.cfm?bannerid=64 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2409 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
