Using CFQuery for executing stored procedure
January 2nd, 2008
Something that all of us wants to avoid is server slow down and crash in the event of application load and that’s what motivates us to look at code optimization, refactoring, frameworks, database optimization etc.
There are various steps that can be taken for application optimization and to have uninterruptible application availability, but for the moment I am just going to focus on database side. This wont be the first time you are hearing about database/query optimization for having a healthy application, and assuming you have the best optimization done there are still times when unthinkable happens i.e. network connection to database is slow, database transaction log is full, query is running without using a index etc. The point being unforeseen issue can happen with database and it can bring your site to its knees, and your customers have to suffer.
Many of the large scale application uses stored procedure instead of inline SQL queries in CF code and therefore CFStoreproc tag is the ideal choice for running Stored procedure. But one of the biggest disadvantages of using CFStoredproce is the missing timeout attribute, which can lead to slow sites and complete crapping out of server. For whatever reason if the stored procedure executing time goes up then normal expectancy its going to slow down the page processing and eventually result in request queuing for high traffic site and the unavailable server crash.
If you are concerned about Stored procedure taking long time then normal, then use CFQuery tag with “timeout” attribute where you can set the timeout to be the normal expect Query executing time + some extra buffer. When the code gets executed the timeout is going to free the connection if SP takes longer then you have anticipated and you can graciously handle the error, rather then letting the page spin forever. Think about the scenario where the lots of users are hitting the same page (without the timeout), whosoever comes first will have the page still loading and basically holding the CF execution thread and other users request will be queued up in CF server which is going to slow down the server and as the queue size increases the server is going to become unstable. Whereas if the timeout was introduced its going to clear the request immediately with some error message to user, and therefore the server will continue processing request as they arrive.
CFQuery “timeout” attribute should not only be used for stored procedures, it should also be used for regular inline query, doing so will save your application from coming to a screeching hold in case of database outage.
<cfquery name=”name” timeout=”10″>
Query / Store procedure
</cfquery>
Entry Filed under: Observation
4 Comments Add your own
1. Sung | April 14th, 2009 at 8:21 pm
I can’t get the TIMEOUT parameter of CFQUERY to work for the life of me. I’m using CFMX 7.0.2 with MS SQL Server 2005. This is what I run:
select top 20000 *
from table
#cfquery.ExecutionTime#
That query outputs a number that’s in the 3000s — which should never happen, right? Since the timeout is set for 1 second (or 1000ms). Why is this happening? I thought the TIMEOUT paramter works with SQL…
2. stduseren | June 13th, 2009 at 6:37 am
Well first i would recommend not using 20000 *
since * will select all the columns and you may not need them all , that is just going to make whatever you are doing extremely slow not at the sql part, but when cf loops trough each result to present it on its cfquery alocation.
the other thing that may be happening is a timeout of the overall page giving up just because coldfusion is taking a long time parsing all those columns and rows.
when you say 3000s if you ment seconds that is 3000/60 that is 50 minutes. Yes it could happen but that would mean that somewhere in your code you have specified a timeout setting of greater than 50 minutes wich is not really recommended.
Any ways may I ask why do you need so many rows at once? 20,000 I never do this ever ever…
are you using a query of queries to then narrow down the numbers? or using coldfusion to perform calculations based on those results if so I will once again recommend learning better SQL and not using cf to run calculations based on sql results its just not efficient, nor elegant.
3. Pal | June 19th, 2009 at 3:02 am
I personally can’t quite figure out what I like about the approach. I like many who have used CF for many years have had to produce code with inline sql and stored procedures and I just cannot agree with this approach. I am sure there are scenarios where this approach in desired but that does not make the approach a recommended methodology for building sites. Stored Procs provide a multitude of benefits which are not address in this article and these benefits far outway the single option of using timeouts. Stored procs which are runaway or taking too long are not the fault of the database they are the fault of the people who run the infrastructure and/or the programmers who designed the system. A fancy error screen ’saying come back later’ is not a solution. Store Procs are nearly always better for all reasons IMHO.
4. Pal | June 19th, 2009 at 9:41 pm
I apologize as I made a wrong assumption that the article was pushing in-line SQL as opposed to stored procs, my bad; I had not had a second cup of coffee. I misunderstood the context and retract my compaint above. Thanks for the contribution.
Leave a Comment
Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>
Trackback this post | Subscribe to the comments via RSS Feed