At 11:17 AM 3/20/2007, D wrote: Are cached queries as good as stored procedures? Technically which are better? Is it a significant difference?
Cached queries hold data in RAM so no call to the DB is necessary, so they are always much faster than stored procedures (except for the first call to retrieve the data of course). I'm thinking you might actually be asking about regular queries however. Some years ago I did a test to see how much difference sp's actually made. I constructed a couple of sp's to do lookups on large tables, compared their execution time with regular CFQUERY queries (not cached queries) doing the same thing. This was using SQL Server 7. I found there was no difference whatsoever, other than minor random millisecond variations (sometimes the regular queries were slightly faster even). Conclusion: the people who recommend stored procedures are working from theory and not practice and do not know what they are talking about in any practical sense. Stored procedures can involve significant extra work in esoteric code that's hard to find good documentation for, and they make your CF code far less portable between platforms (lots trickier to move between different kinds of databases). Consequently, since that time I have never again wasted my time writing stored procedures as they provide no practical benefit that I have been able to determine. I would love for someone to prove me wrong, however. That is not a request for your random uninformed opinion, if you're really a believer in sp's then please make a template or two that compares the execution time of a CFQUERY vs a sp, provide the code for your query and your sp, as well as a SQL Server maketable script and a downloadable data file in csv or tab-delimited format to populate the table so we can download and run the same test ourselves. I'm always willing to change my mind, but not for the sake of some buffoon spouting his ignorant opinions. *Show* me what the truth is, and I'll believe you. K ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273477 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
