Need help in SP Its urgent :-( please help me

  • 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

  • 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

  • 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?

    “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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

    “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

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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!!!!

  • 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.

  • 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

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

    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?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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.

    “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

  • 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.

    “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 (2/12/2015)

    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.

    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