July 3, 2018 at 12:15 am
I have a SP in a job which is performing slowwww. I identified that the SP which is the culprit, Need some help in identifying the root cause.
SP:
will be posting more refined analysis based on http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ by gail shaw
July 3, 2018 at 1:43 am
The first thing that strikes me, when looking at this code, is cursors.
There is one cursor spanning most of the code and within that there are three other cursors.
A cursor forces SQL Server to process one record at a a time, when it is actually designed to work with sets ("all at once" operations). There are cases where a cursor is necessary, although you would do well to avoid them whenever possible. This code uses several and you aren't going to get any acceptable performance this way.
Ideally, this procedure should be re-designed, using tables to hold intermediate results where required.
Unfortunately, I don't believe there is any quick fix for this.
July 3, 2018 at 10:09 am
are you sure that is the real bad one? this proc calls 2 others
EXECUTE Getemailaddress @customerId, @toEmail output
and EXEC dbo.CreateDDForMissingCheckoutOrCheckin that the very end
As the first one is called in a loop it could be that the total elapsed time of this one is the really reason for being slower than before.
that on top of using cursors - this probably should be redesigned to prepare the data using set based logic and then a final loop for the sendmail process
July 3, 2018 at 10:20 am
The root cause is definitely Sp_send_dbmail, recommend a redesign with a queue type table and a separate job to actually send the emails.
😎
July 3, 2018 at 2:50 pm
Eirikur Eiriksson - Tuesday, July 3, 2018 10:20 AMThe root cause is definitely Sp_send_dbmail, recommend a redesign with a queue type table and a separate job to actually send the emails.
😎
sp_send_dbmail does use a queue:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
July 3, 2018 at 3:01 pm
Looking at the posted code, you really need to refactor this procedure and eliminate the cursors where appropriate. And before you say you can't, actually you can. You just need to change your thought process. Currently you are doing things row by agonizing row. There may be a part of this needs to be done a row at a time, that would be actually sending the individual emails. Much of the rest can be rewritten to work with sets. You also have a call to a procedure, Getemailaddress, that (if it actually queries a single table) should either add the table itself to the procedure or rewrite it as an inline table valued function. Either could then be used in a rewritten query to bring back the email address. I can see using temporary table(s) to store intermediate results where appropriate.
July 4, 2018 at 3:06 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply