I've been told there are performance gains when using 'Stored Procedures'. Lately I've been wondering what the real gains of performance are and does it relate to a simple select statement?
First we need to create a test set of tables:
Now some code to generate data for the tables:
Now lets make the store procedure to select results:
And now the code we used to test the execution time of cfquery and cfstoredproc:
Now the results I got are very mixed. Sometimes the procedure is faster; sometimes it is not. I've never done anything like this before so I'm wondering if EXECUTIONTIME is the best thing to be comparing? Will using a store procedure in cases like this be that much faster (on such a simple select) or is it more for all the other benefits you get with using it?
ColdFusion, Flex and more...
Your probably better off doing inserts. As the first set of querys will be cached in the DB for the second time. I'd be interested to see the timings recorded though. You might need to insert 100k records to see any real difference.
ReplyDeleteI came across a situation where CFQUERYPARAMs were making my queries slow and query caching wasn't working. It may have had something to do with it being a combination of DISTINCTs and JOINs. At any rate, if I got rid of CFQUERYPARAMs query caching worked, but then that introduced a potential security hole by way of SQL injections.
ReplyDeleteI went with stored procedures instead and got really good performance from then on. With SPs the major issue was dealing with dynamic SQL and translating that to T-SQL. In some cases I could and in others I couldn't.
One benefit of stored procedures is that it provides another layer of encapsulation. So instead of selecting directly from tables, the application can select from this concept that is the result set of a stored procedure. As far as execution time, I always assumed it would be faster, but you're right in verifying that. The other benefit is that you can turn off inserts and updates in the ColdFusion administrator.
ReplyDeleteTo be fair, you should use cfqueryparam. Your stored procedure call is a re-usable execution statement that the database can both cache as well as a pooled statement at your datasource level. When you concatenate the user id in with the SQL, you are creating 1000 different SQL statements that have to each individually be sent to your SQL server and have execution plans generated for them.
ReplyDeleteFunctionally speaking, a paramaterized cfquery operates no differently than a stored procedure. The only difference is the SQL statement is passed to the database the first time so it can be compiled and prepared. Every other call simply passes the statement handle.
Procedures still have to be compiled on the first call, but the SQL inside the proc obviously never has to be passed "over-the-wire".
As far as stored procs as an abstraction layer, doesn't a DAO provide that?
~Brad