> 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:2407 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
