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. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2408 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=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
