Make sure your SQL Statements are using cached execution plans.
Why?
When a query is passed to Microsoft SQL Server, a number of things have to happen in order for you to get a result set back. First, before anything can be done your SQL Statement is compiled by the Query Optimizer which creates an Execution Plan based on statistical data and things like indexes. Essentially it is making its best attempt to create a procedure that is as highly optimized as possible and can return the results your’re looking for.
Once this Execution Plan is created it is then cached on the server so that the next time a query with the same parameters and structure is called it can simply run that existing plan instead of having to build a new one which can be, and is often, processor and i/o intensive; creating undo overhead.
Using cached execution plans is considered a best-practice because it allows your queries to execute much faster (since the plan doesnt have to be constantly rebuilt/compiled) and as a side effect because you are passing data to typed values (int, varchar, etc.) you avoid any possibilities of SQL Injection Attacks.
How?
- used stored procedures
- use cfqueryparam for all values in your queries (even constants)
Note:
This is applicable to all languages and application types: web, desktop, cli… wether it be in cfml, c#, or c++, SQL essentially functions the same way with the exception of the driver being utilized. If you aren’t sure your execution plans are being cached take the time to look up the correct way to do it. You will be glad you did.
No related posts.
