Coldfusion Design Patterns

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>

Tags: cf, cfquery, cfstoredprocedure, coldfusion, Observation, timeout

Entry Filed under: Observation