Case Study… and a new big gain!
Pretty close to the last record, huh?
Here is another recent SQL Server performance tuning report.
Improvements achieved:
- CPU from 258,142 to 63
- Reads from 68,650,873 to 18,411
- Duration from 470,993 to 490
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 ?
Its 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.
