Well there are more (minor) speed benefits. Where as cfquery get compiled every time procedures do not. A stored procedure may be faster on the second run and the same speed following; cfquery will be as slow no matter how many times it's executed.
Additionaly CF is sending the entire query to Oracle through cfquery, whereas a stored proc call is alot less code. So network latency comes into play a bit. We havent mentioned database error handling which is another benefit of stored procs. User defined errors, strong datatyping and logging. > That's not a completely fair or accurate point: the second > execution of a stored procedure will be much faster than the second > execution of a <cfquery> assuming that the cfquery doesn't use > bind variables and is being run with different parameters. Otherwise, > the db will reuse the stored query plan and the time > difference won't be *that* great. The reason i want to point > this out is that a > lot of people think "SP's are complicated but they're fast", > so they tend to see them as a kind of optimization technique... and > then they get disappointed when switching to SP's doesn't > magically "deliver" much faster execution time. If all you're after is > speed and scalability then a) spend some time learning how to > write correct sql and b) use cfqueryparam. I've said this above, but cftransaction is drastically different than the transaction in a database. Plus it really doesnt fit well in an MVC pattern. Again, cftransaction doesnt give you row level locking. Which is pretty key in a database transaction. If you had two applications using the say schema. Both apps in CF, running seperate instances or using two seperate DSNs. They know nothing of each other transactions. You are going to end up with race conditions, which is what the transaction in the database will prevent. > True enough, but then again that's what cftransaction is for. > You could counter with savepoints and autonomous transactions but then > we're really getting into edge cases. I know it's sad really. If you are using CFquery in that manner, if someone has access to execute code on your webserver, they can take complete control over your database (which may even be outside of a DMZ behind a firewall). I'm not saying I've done this, but once you gain access to the box, with a few lines of code you can either pull the u/p out of the dsn, or just execute whatever you want through that dsn. > +1. The number of people who connect their web application to > their database as a user with dba privs is amazing. There are two > aspects to sql injection attacks: people putting junk data > into your tables... and people damaging your database (dropping tables, > deleting records, etc). In most cases, the user your web app > connects as has no business being allowed to drop or create objects. > SP's are a great way to grant explicit object level privs to > the web app user without having to even use the db privs system: they > can only do those things you gave them a proc to do. True enough. > yessssss, but: you could write functions in pl/sql and call > them from within a query in a cfquery block. One more thing I almost forgot that has just become second nature. Session state! When a user connects to oracle and calls any procedure in a package, the begin block of the package executes (much like init()) Each user gets thier own version of the entire package stored in session. There are also the OO benefits. -Adam ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:208851 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
