Before vs. After Perf Tuning Report #11 – 767,508% improvement

Before vs. After Perf Tuning Report #11 – 767,508% improvement

This is the case when tuning sometimes can not be done perfectly.

We trying to change DB code, so nothing else needs to change.

perf-tuning-report-11

In some cases, even though result set is exactly same, rows may come back in the different order back to the app.

In those cases, we have to work with the the client application team to review that different order of the rows is not going to break the application.

I know a lot of you will say “it shouldn’t” – but for the large enterprise apps you never know what type of stupidity is inside them. It may not. Or it may.

The point is to be careful.

Here are more details on what we did.

 

 

Problem: spPartsReceived stored procedure takes 40-60s to execute and times out in the app.
 
Why: SP is performing multiple index scans and causing too much Disk Operations.
 
Change: Re-written code is attached spPartsReceived_new_version.sql line 56 to 61 and line 66 to 68.
Testing: Test value of  3026685 was used for both old and new SP. Result set is same, but data row ordering is different.
Next steps: We recommend making sure that data order difference is not going to cause problems before implementing the change.
excel-calc-report-11
Performance gains:
Execution time
Old execution = 40s
New = (under) 1s
Improvement: 39 times faster
Reads
old: 687,157
new: 90
improvement: 7,634 times less IO
Actual
Table ‘tblRcptheader’. Scan count 9, logical reads 28,911, physical reads 0
Table ‘tblRcptitem’. Scan count 9, logical reads 363,865, physical reads 0
Table ‘tblLineItems’. Scan count 9, logical reads 294,381, physical reads 0
New version 
Table ‘tblRcptheader’. Scan count 5, logical reads 27, physical reads 0
Table ‘tblRcptitem’. Scan count 1, logical reads 27, physical reads 0

Table ‘tblLineItems’. Scan count 5, logical reads 36, physical reads 0

IO-tuning-report-11
Need your SQL Server Performance Tuned? We have service that does just that!
Would love to help you!

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email