Performance tuning a procedure

Question

Performance tuning a procedure

WayneS

SSC Guru

Points: 95461
More actions
June 10, 2009 at 10:08 am

#206009

Note: please see the attached file for the actual execution plan and the io & time statistics.
I'm trying to tune a procedure. I've successfully added indexes that takes some of the table scans to index scans (not much better, but it's a start).
The procedure has one of those where clauses like:
where (@value = 'ss and field1 = @Value1) or
(@value = 'bg' and field2 = @Value2) or
(@value = 'xx' and field2 = @Value2 and field3 = @Value3) or
(@value = 'zz' and field4 = @Value4)
I've separated this out:
if @value = 'ss' execute spProc;2 @Value1
else if @value = 'bg' execute spProc;3 @Value2
else if @value = 'xx' execute spProc;4 @Value2, @Value3
else if @value = 'zz' execute spProc;5 @Value4
I'm running the new code and the old code in my "test harness", which gets the attached statistics and execution plan.
Now, the new changes (according to the statistics) look better to me: improved time, less logical and physical reads on many tables.
However, the execution plan shows that the new query is 63% of the batch.
I'm having trouble reconciling this discrepancy. Anyone have any ideas?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The percentages and costs are estimates. They may not be accurate.

If the new query has fewer reads and a lower exec time then I'd go with that and ignore the costs. Just make sure you don't have outdated stats as they can cause bad estimates for cost and non-optimal plans.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Just looked at the plan. There are incorrect estimates (too high) in a few places so that may explain the cost. Also, if you look, the first plan has paralleled and the second hasn't. Beyond that there's problems here. You've got index scans and clustered index scans everywhere.

May I suggest the following indexes?

[dbo].[Directory] ([brigade_cd] ,[region_cd]) INCLUDE (dir_sch_cd,sch_nm)

[dbo].[TRNG_Campus] (student_id)

[dbo].[Campus_APFT] (KeyCol14, student_id) INCLUDE (test_dt, calc_total_scr_qy)

[dbo].[TRNG_Ht_Wt] (Student_id) INCLUDE (record_dt, height_dm, weight_wt)

[dbo].[TRNG_Campus_Comments] (student_id)

[dbo].[Campus_LDIM] (ldim_type_cd, student_id, position_cd)

There's probably more that are needed, see what those do for a start.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass