CFUNITED 2007: SQL Performance and Database Tuning

Today, I attended Nate Nelson's SQL Performance class (aka Advanced Database). I very much enjoyed this class and got a lot out of it. There were many relevant sections of the course that I could easily apply to my day to day tasks to ensure that I have database driven applications that are efficient and run quickly.

Many of the tips that Nate provided were quite easy to implement in your existing and future applications, but would have a tremendous effect on the performance of your application.

Until today, I thought an index on a table was good for performance, but I did not know how important it actually is. Indices improve table performance by a lot because the database basically only has to look at a "table of contents" to find the item you are looking for, as opposed to looking at every single row and column scanning every part to find it. Luckily, when a primary key is defined in a table usually a clustered index is defined as well for that table based on the primary key.

One thing that Nate talked about when beginning to tune your databases was to not simply start correcting a problem once you encounter it. Instead, be like a doctor and first diagnose the situation. Once you do that you will be able to cure the symptoms and fix the issue altogether. If you attempt to fix the first issue that you find, you may only be fixing a symptom and not the core of your issues--resulting in a temporary bandage on the application which will eventually wear out and fall off. One tool that we discussed in great detail today to aid a developer in diagnosing is the Execution Plan (this is what it is called in SQL Server).

The Execution Plan shows you the individual processes that the database server goes through to perform your query or statement connected together by a series of arrows that wholly describe the roadmap of the execution. Once the plan is generated (either after or before you run the query), you can roll over any of the graphical icons to view more details about a given process. Such details include whether the process was using a scanning/seeking an index or whether it did full table scan (not recommended on large sets of data). Each process is also given a percentage part of 100% of computational time that it consumed. I will definitely be using this tool much more often in all of my queries to continue to see how I could improve them.

Those are the two biggest points that I am going to cover here. Some other topics that we discussed were:

• Database performance ColdFusion tags
  • cahedbefore and cachedwithin attributes of cfquery
  • cftransaction
  • cflock
• Locking
• Designing for performance
  • Normalization
  • De-Normalization
  • Views
  • Partitioning

The class had much more content than what I briefly covered here. If you have the chance to visit one of his other sessions, I would highly recommend you do so.