When I first started writing Coldfusion applications I was very naive about data and architecture. I can admit it. I did not use components and I wrote (and often re-wrote) queries in-line when needed. As my development matured and I began to more fully understand SQL Server as an independent platform I recognized my failures.
After delving into some SQL tuning for Navision, a Microsoft CRM/ERP system my company ran I began to understand that SQL Server likes stored procedures and functions over ad hoc queries. I also saw how this could benefit my Coldfusion development. This time coincided with a more full understanding of CF Components to allow me to write a function once and reference that function, rather than copy/paste the same queries (or business intelligence for that matter) all over the place.
What I learned about SQL Server was this: when using stored procedures you can leverage internal caching that is not available from ad hoc queries.
Because stored procedures have a formalized list of parameters, SQL can cache the way it will run the procedure (aka the execution plan). When an ad hoc query is sent to the compiler, it must determine the data types of any parameters before proceeding. By making this step unnecessary and pulling the execution plan from memory the stored procedure requires less overall processing and executes faster.
By making a request in a more SQL Server-friendly way, the server rewards us with a faster result. If the result comes back faster, Coldfusion can do it's processing sooner and the client is rewarded with a faster response. If you take further advantage of SQL's ability to handle the data, by handling data manipulation or combining multiple queries into a single call, you lighten the load on your Coldfusion server and make it more efficient.
