Help to optimize a SP

Question

Help to optimize a SP

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
February 12, 2016 at 5:35 am

#324320

Could you guys please review the attached Stored Procedure and advise if optimization is needed.
I've attached the SQL plan, Table/Function definitions.

Viewing 14 posts - 1 through 13 (of 13 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SQL!$@w$0ME (2/12/2016)
Could you guys please review the attached Stored Procedure and advise if optimization is needed.
-- Stored Procedure
CREATE procedure [dbo].[PeerReviewsGet]
as
begin
select Review_tab.ReviewId CustomerReferralId,
Review_tab.ActivityId ReviewActivityId,
ImeEventDetail.Id ImeEventDetailId,
activity.Id ActivityId,
caseinfo.casenum as CaseNumber,
convert(varchar, ImeEventDetail.ImeEventId) + ImeEventDetail.Extension ImeEventId,
ImeEventDetail.CustomerReceivedReportDate,
activity.ActivityDate,
ActivityNote.Note,
activity.ActivityDescription
from invoice.activity
inner join caseinfo on activity.caseid = caseinfo.caseid
inner join code.ActivityCode on ActivityCode.id = activity.ActivityCodeId
inner join ImeEventDetailActivity on ImeEventDetailActivity.activityid = activity.id
inner join ImeEventDetail on ImeEventDetailActivity.ImeEventDetailId = ImeEventDetail.Id
inner join invoice.ActivityNote on ActivityNote.activityid = activity.Id
left join dbo.PeerReviewActivity on PeerReviewActivity.activityid = activity.Id
outer apply dbo.PeerReviewIds(ImeEventDetail.Id) as Review_tab
where ActivityCode.Code in ('GHRTF')
and ImeEventDetail.ImeServiceTypeId = 2
and ImeEventDetail.closurecode is not null
and activity.isexcluded is null
and activity.DeletedAt is null
and activity.ActivityDescription not in (
'Contact',
'ReviewID'
)
and (
PeerReviewActivity.id is null
or (
PeerReviewActivity.id is not null
and PeerReviewActivity.StatusId = 3
)
)
order by ImeEventDetail.Id,
activity.Id
end
GO

The business will soon tell you if optimisation is needed!

Can you post an actual execution plan as a .sqlplan attachment, folks will review it for tuning opportunities.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Table definitions, index definitions, function definitions and execution plan please.

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

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 3

SQL!$@w$0ME

SSChampion

Points: 12349

February 12, 2016 at 6:34 am

#1858323

Tks. I've attached the sqlplan.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SQL!$@w$0ME (2/12/2016)
Tks. I've attached the sqlplan.

That's the estimated plan. Can you post the actual plan please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SQL!$@w$0ME (2/12/2016)
Tks. I've attached the sqlplan.

Add ActivityDescription as an INCLUDE column to index [IX_Activity_ActivityCodeId_DeletedAt_CaseId_ActivityDate], it's currently showing as 78% of the total cost of the query and masking everything else. Then capture and post an actual plan, along with the details Gail requested.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 6

SQL!$@w$0ME

SSChampion

Points: 12349

February 12, 2016 at 7:13 am

#1858336

Thanks, Please see the actual plan attached.

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

GilaMonster (2/12/2016)
Table definitions, index definitions, function definitions ~~and execution plan~~ please.

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

SQL!$@w$0ME (2/12/2016)
Thanks, Please see the actual plan attached.

1. Can you amend the index as suggested earlier?

2. Can you run it with sensible parameters such that it returns a result set?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 9

Thanks Gail. I've attached the table/index/function definitions.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 10

First the most important question (if it has already been asked, I have overlooked it): How much time does this query currently take, and how fast do you need it to be?

The actual execution plan shows all actual rowcounts to be zero, even when estimates are in the thousands. This means (a) that either something is very wrong with the estimates or you did something very weird to get this plan; and (b) that the procedure probably ran very fast.

Could you also run it again, with the SET STATISTICS IO option enabled, and share the results of that?

Based on the estimated cost percentages, I agree with Chris' suggestion that adding ActivityDescription as an included column in the index **MIGHT** make the querry faster. But it might also affect performance of other queries running on your system, for better or for worse, so do run a full test in your QA database.

Various tables and indexes in the plan are scanned. Whether or not that is a problem is hard to tell without knowing how much data is in the tables.

Also, your table-valued function: it is written as an inline function, which is great. (Well done!). But its results are not deterministic: it has a TOP 1 without ORDER BY, which is the SQL Server equivalent of "just pick any random row that meets the criteria".

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Eric M Russell SSC Guru Points: 125523 More actions · Answer 11

If you have to ask, then the answer is probably yes, at least to some degree.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 12

What's the value of ActivityCode.Code filter constant?

'GHRTF' OR 'kjhgu' OR 'HARTW' ?

Why is it constantly changing?

Alex Suprun

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 13

SQL!$@w$0ME (2/12/2016)
Thanks, Please see the actual plan attached.

It would appear that you have no data in the leading table. The actual execution plan isn't going to indicate much.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)