Case Study… and a new big gain!
Here is another recent SQL Server performance tuning report.
Improvements achieved:
- CPU from 51,628 to 12
- Reads from 13,730,174 to 6,682
- Duration from 94,198 to 98
Before vs. After.
“Before” – shows numbers before we made any changes.
“After” – shows SQL performance numbers after we made some changes in the database to improve slow SQL calls.
See capture of BEFORE vs. AFTER performance tuning run:
What changes made these queries run faster?
- SQL Cursor changed from Local to Fast Forward (cannot replace the SQL cursor, because this is a third-party application and we are limited on what we can do).
- Added a non-clustered index on table [<removed>Transaction.
- A subquery became a Common Table Expression (CTE).
Why did you use CTE ?
It’s simple.
You can influence SQL Server’s ordering of joins using CTEs (which were introduced in SQL Server 2005).
Common Table Expression allows a developer to break up a complex query into little parts, which can simplify the logic as well as assessment of exactly which joins dramatically affect performance.
