February 12, 2015 at 4:45 am
Please let me know if you need any further detail.
Can anyone please let me know that RAISERROR give us the ability to log an exception such as a timeout?
I have implemented following in my SP
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE() <> 0)
ROLLBACK TRANSACTION
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
February 12, 2015 at 5:02 am
how much time this procedure if you execute it. Its an awfully long transaction which mean if this procedure is running in the background then it will lock all the table until commit / rollback fire.
good practice is to select the data upfront and do the INSERT/UPDATE/DELETE in side the transaction.
All your select queries are using common parameter @OrderID. so better get then select 1st and then begin the transaction
OR
if you do not want to change that then you should begin and commit against each INSERT you are performing in your query.
you have work it out at your end which option suites you well.
Hope it helps
February 12, 2015 at 5:29 am
Zohaib Anwar (2/12/2015)
Please let me know if you need any further detail. I Can also provide Trace file if required. please help me its urgent development team is asking ETA :-(...
Is it as urgent as this?
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
February 12, 2015 at 5:30 am
Zohaib Anwar (2/12/2015)
Please let me know if you need any further detail. I Can also provide Trace file if required. please help me its urgent development team is asking ETA 🙁
Please keep in mind that the people answering questions here are doing so in their spare time and are not paid for their work. If you need urgent help with hard deadlines, suggest that a consultant or contractor is brought in to help out.
The procedure you posted is close to 2000 lines of code. That's not something that can be easily looked at over a coffee break.
Can anyone please let me know that does the following SP RAISERROR give us the ability to log an exception such as a timeout?
No, because a timeout is not a SQL error. It's the application that decides it's waited too long. Do your error handling for timeouts in the application
Please review the following stored procedure CreateInvoice to determine what we can do for logging an exception that occurs within it ??
The TRY ... CATCH block you already have should do that. You can add an INSERT before the RAISERROR to write the error information into a table.
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
February 12, 2015 at 5:31 am
twin.devil (2/12/2015)
if you do not want to change that then you should begin and commit against each INSERT you are performing in your query.
I strongly disagree there.
Doing as you suggest would mean that the entire operation is no longer atomic and mean that partial data can be inserted. I'll take a slow proc over bad data any day.
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
February 12, 2015 at 5:47 am
The stored procedure is written in a procedural Row-By-Agonizing_Row way indicating an inexperienced programmer. The upside is that you have plenty of scope for improvement by a professional. So why not bring one in for a code review? If this sample is representative of the style and standard of coding in the db, then you could be in for some substantial - and fast - wins.
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
February 12, 2015 at 5:59 am
Thanks for your reply.
Sorry Gila , you are absolutely correct people answering questions here are doing so in their spare time and are not paid for the work.
I was really having hard time with Dev team and was having hard deadlines but cant suggest a consultant or contractor who can help out if I suggest they will FIRE me :angry: I am the only DBA trapped in Group of Dev's 🙁
Any how I really appreciate the help!!!!!!!!
Ok can you please suggest that can we use WAITFOR (Transact-SQL) in this SP for timeout? Dev do not want error handling for timeouts in the application.
Can we do it the following way >?
BEGIN
WAITFOR DELAY '02:00';
EXECUTE sp_helpdb;
END;
GO
Actually, I am not sure EXACTLY where I can fit the above code in my SP
I tried doing Google and get the above code from following link
https://msdn.microsoft.com/en-us/library/ms187331.aspx
Once again I am sorry for typing URGENT It seems like this word stuck in my mind some where due to Dev team pressure!!! but I am really great full and appreciate you support!!!
Thanks once again.
February 12, 2015 at 6:03 am
Zohaib Anwar (2/12/2015)
Ok can you please suggest that can we use WAITFOR (Transact-SQL) in this SP for timeout?
Lol. You have a problem where the procedure is taking too long to run, and you want to add a delay into it to make it run longer?
For timeouts, tune the code so that it runs faster or increase the timeout value in the application so that it waits longer.
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
February 12, 2015 at 6:23 am
ChrisM@Work (2/12/2015)
The stored procedure is written in a procedural Row-By-Agonizing_Row way indicating an inexperienced programmer. The upside is that you have plenty of scope for improvement by a professional. So why not bring one in for a code review? If this sample is representative of the style and standard of coding in the db, then you could be in for some substantial - and fast - wins.
Actually, I am not good in Code optimization. I can / must admit here in the forum but not at work. I am good in SQL Administration side and whenever I got any task on my plate related to Optimization then that would be a BAD DAY for me.!!!!
and you are correct I am working with lots of inexperienced programmer!!!!
February 12, 2015 at 6:25 am
GilaMonster (2/12/2015)
Lol. You have a problem where the procedure is taking too long to run, and you want to add a delay into it to make it run longer?
.
yes, that's true. I am not good in code tuning / optimization.
February 12, 2015 at 6:31 am
Then motivate to get someone in to help out in this area. Most good consultants will teach as they fix, so you get the immediate problems fixed and you learn how to handle them next time there's a problem.
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
February 12, 2015 at 6:32 am
...
yes, that's true. I am not good in code tuning / optimization.
If there is no-one to tune this code, the only option I can suggest is to increase Connection Timeout value in the calling application (UI or middle-tier).
What is calling your stored procedure?
February 12, 2015 at 6:40 am
Zohaib Anwar (2/12/2015)
ChrisM@Work (2/12/2015)
The stored procedure is written in a procedural Row-By-Agonizing_Row way indicating an inexperienced programmer. The upside is that you have plenty of scope for improvement by a professional. So why not bring one in for a code review? If this sample is representative of the style and standard of coding in the db, then you could be in for some substantial - and fast - wins.Actually, I am not good in Code optimization. I can / must admit here in the forum but not at work. I am good in SQL Administration side and whenever I got any task on my plate related to Optimization then that would be a BAD DAY for me.!!!!
It's not uncommon practice for a DBA to pass a poorly-performing query or stored procedure back to the cowboy developer with instructions to improve it. Here are a few pointers that they've done the job properly for you:
All 8 WHILE loops are replaced by set-based inserts.
SCOPE_IDENTITY() is replaced by OUTPUT, either into a scalar variable or a table variable (or if you're lucky, permanent table).
There will be few variables other than the parameters.
The entire stored procedure will be considerably simpler and shorter and will run in significantly less time.
With the stored procedure properly written, you will have an opportunity to examine the execution plan and in conjunction with indexing metrics, perhaps improve the performance even more with some judicious index tweaking.
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
February 12, 2015 at 6:47 am
In this particular case, which of the following statements is the more accurate:
"Write it properly"
"Tune it"
Sometimes it's a fine line, but in this case I think it's clear.
It seems to me that if there isn't a dev on the team who can fix this code, then the whole team needs some training. As Gail pointed out, most good consultants will teach as they fix.
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
February 12, 2015 at 7:16 am
ChrisM@Work (2/12/2015)
It's not uncommon practice for a DBA to pass a poorly-performing query or stored procedure back to the
cowboydeveloper with instructions to improve it. Here are a few pointers that they've done the job properly for you:All 8 WHILE loops are replaced by set-based inserts.
SCOPE_IDENTITY() is replaced by OUTPUT, either into a scalar variable or a table variable (or if you're lucky, permanent table).
There will be few variables other than the parameters.
The entire stored procedure will be considerably simpler and shorter and will run in significantly less time.
With the stored procedure properly written, you will have an opportunity to examine the execution plan and in conjunction with indexing metrics, perhaps improve the performance even more with some judicious index tweaking.
Very much my thoughts, until the internet ate my last post.....
I think I spotted a nested while loop as well.
The other criteria I would apply is to have the code properly COMMENTED and formatted with tabs to make it Understandable and more importantly readable.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply