SP took too much time for executing

Question

SP took too much time for executing

SQL Galaxy

One Orange Chip

Points: 29472
More actions
October 7, 2015 at 2:04 am

#301329

HI.
I have verified at DB level for Index farg and statistics are normal but still execution take long time to completed.
Actual Execution plan does not suggested any missing index.
Pl. suggest the attached actual execution plan and SP definition.
Thanks

Viewing 6 posts - 1 through 5 (of 5 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 Galaxy (10/7/2015)
HI.
I have verified at DB level for Index farg and statistics are normal but still execution take long time to completed.
Actual Execution plan does not suggested any missing index.
Pl. suggest the attached actual execution plan and SP definition.
Thanks

The nested cursors are most likely the cause. Replace the whole nested cursor section with set-based code.

^{“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 Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thanks for your replay..

I am not a developer. but I will try my best.

Could you give me any example for how will change existing nested cursor to set based method.

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

SQL Galaxy (10/7/2015)
Thanks for your replay..
I am not a developer. but I will try my best.
Could you give me any example for how will change existing nested cursor to set based method.

You will need a developer for this. There are no fixed rules for the process; work out what the existing code does to the database and derive a set-based method to achieve the same endpoint.

You could encourage folks from this community to help, by posting sample data for them to code against.

^{“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

chris92mars SSC Enthusiast Points: 198 More actions · Answer 4

Create Temp table to store the data instead of using cursor.

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 5

You would be able to get a better handle on aggregated durations by tracing the SP:StmtCompleted event and aggregating the results. Codeplex's pssdiag (using detailed template) and SQL Nexus can automate the collection (albeit via a trace).

The most expensive operator in the plans that I saw (by scrolling through the XML) is:

<RelOp AvgRowSize="108" EstimateCPU="37.2395" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="80640" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="123.366">

Above hash match operator's inability to use parallelism is somewhat of a concern, given that its 123.366 cost easily exceeds SQL Server's cost threshold for parallelism default value (5). Most of my systems are firmly rooted in OLTP activity, so I am not a big fan of parallelism (for my OLTP systems), but for the above operator (and for the sproc's DML), this is clearly on the data warehousing side of the spectrum and should be harnessing parallelism (but isn't or can't). You might want to review the server's max degree of parallelism setting, while keeping in mind that radically increasing max degree of parallelism can consume all of SQL Server's worker threads (and the rule of thumb is the number of CPUs or no more than 8, whichever is lower).

Above hash match is trying to JOIN #attendance with tmuster, here:

Update #attendance Set DayIn = convert(varchar(17), tmuster.dayin, 113),

DAyOut =convert(varchar(17), tmuster.dayout, 113),

In_Date = convert(varchar(15), tmuster.dayin, 106),

In_Time = convert(varchar(5), tmuster.dayin, 108),

Out_TIme = convert(varchar(5), tmuster.dayout, 108),

Out_DAte = convert(varchar(15), tmuster.dayout, 106),

Status = case when tmuster.rtype is null then 'AWL' else tmuster.rtype end,

schd_shft = tmuster.schd_shft,

WOrkHrs = dbo.GetWorkHours (tmuster.dayin, tmuster.dayout),

rtype = tmuster.rtype

From tmuster

Where tmuster.empno = #attendance.empno

And tmuster.date = #attendance.date

I suggest investigating whether #attendance should have a covering index created, on empno and date. It may be that tmuster also needs a similar index.

Overall, I think the sproc is far too procedural (the cursors and the IF @FetchedValue = 'Something' blocks), and it uses (unindexed) temporary tables as a place holder, when Common Table Expressions could instead be used (along with CASE clauses). One advantage of Common Table Expressions is that they expose the underlying statistics to SQL Server, while temporary tables tend to hide statistics from SQL Server (unless a recompile threshold triggers a stats update). In addition, temporary tables tend to squander IO ("read it here, temporarily write it there, finally get rid of it"), which also impedes performance. Someone who values set-based operations (more than the original developers) needs to rewrite the whole thing.

I should also point out that calling dbo.GetWorkHours once per updated row is not at all performant. The operator's EstimateRows="80640" suggests how many times the update and the call will be happening, given that results are not filtered within above update statement. The function call should instead be expressed as a JOIN (with a covered index).