Post reply

Replica of Store Procedure taking more time

  • April 4, 2018 at 11:24 pm

    #410387

    Hi All,
    I have optimized one store procedure. Original is taking 35 seconds to take data and new SP is taking more than 1 min.

    So, I tried to make the replica of same SP with the same code, even that is taking more than 1 min.

    Summary is, new replica of same SP is taking also more than 1 min.

    Please suggests, if any one faced this issue. This is very urgent issue.

    Thanks,

  • April 5, 2018 at 6:17 am

    #1985631

    Sure, this kind of thing happens all the time. Have you compared execution plans, the one running fast the one running slowly? Are they the same? Probably not. What are the compile time values for the parameters in the plans? That's the most likely path to identify why there are differences.

    However, you may see other causes. Are you running all tests against identical databases, with equally up to date statistics? Are all the connection settings the same? Are these the same servers or different? Any of these causes, and a few others, could lead to this behavior.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 5, 2018 at 7:02 am

    #1985655

    Hi,

    SP in same DB and I am running using Query window and even parameter is also same.

    Both SP are same except SP name

    Please suggest,

    Thanks...

  • April 5, 2018 at 7:31 am

    #1985665

    To get radical differences in behavior, something has to be different. Did you check the execution plans as I suggested? Are they different? If so, are the parameters that compiled the plans, not the ones you're currently calling them with, the compiled values, different?

    There have to be differences. You have to find them. Nothing just randomly occurs inside the database. Everything occurs from a cause.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 5, 2018 at 8:30 am

    #1985680

    anoop.mehra - Wednesday, April 4, 2018 11:24 PM

    Hi All,
    I have optimized one store procedure. Original is taking 35 seconds to take data and new SP is taking more than 1 min.

    So, I tried to make the replica of same SP with the same code, even that is taking more than 1 min.

    Summary is, new replica of same SP is taking also more than 1 min.

    Please suggests, if any one faced this issue. This is very urgent issue.

    Thanks,

    Did you clear the cache for the SP or force a recompile to overcome a possible "bad" parameter sniffing problem?

    --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)

  • April 6, 2018 at 12:53 am

    #1985765

    Thanks for all, my challenge is the below code. If I am not using it then SP is taking 6 seconds to get data and after using it, taking more then 1 min
    Please do you have any idea to optimize it

    --------------------------------------------------------------------------------

    Update TMP1

    Set DriverID = FDR.DriverID,
    CompanyID = CDT.CompanyName
    from @TempTable1 TMP1

     Inner Join (Select FaxID, CompanyID, LTrim(Replace(Stuff((Select ', ' + ISNULL(Cast(FD1.DriverID as varchar(10)),'') From FaxId_Document_Relation FD1 Where FD1.FaxID = FD2.FaxID For XML PATH ('')),1,1,''),' ,','')) as DriverID

           From FaxId_Document_Relation FD2       
    Where FaxID in (select FaxID from @TempTable1)       
    Group By FaxID, CompanyID) as FDR ON TMP1.FaxID = FDR.FaxID  

    Inner Join (select CompanyID, CompanyName from CustomerDetails) as CDT on CDT.CompanyID = FDR.CompanyID

  • April 6, 2018 at 2:22 am

    #1985771

    You are trying to update CompanyID with CompanyName?
    I would like to see the execution plan for this.

    Can you also please post the @temptable1 creation script if its not an into.
    Change your Where FaxID in (select FaxID from @TempTable1) to an inner join.
    Why are you grouping on the sub select? If you are getting duplicates without it, use a rownumber instead.
    What indexing do you have on the physical tables in this query?

  • April 6, 2018 at 4:35 am

    #1985777

    anoop.mehra - Friday, April 6, 2018 12:53 AM

    Thanks for all, my challenge is the below code. If I am not using it then SP is taking 6 seconds to get data and after using it, taking more then 1 min
    Please do you have any idea to optimize it

    --------------------------------------------------------------------------------

    Update TMP1

    Set DriverID = FDR.DriverID,
    CompanyID = CDT.CompanyName
    from @TempTable1 TMP1

     Inner Join (Select FaxID, CompanyID, LTrim(Replace(Stuff((Select ', ' + ISNULL(Cast(FD1.DriverID as varchar(10)),'') From FaxId_Document_Relation FD1 Where FD1.FaxID = FD2.FaxID For XML PATH ('')),1,1,''),' ,','')) as DriverID

           From FaxId_Document_Relation FD2       
    Where FaxID in (select FaxID from @TempTable1)       
    Group By FaxID, CompanyID) as FDR ON TMP1.FaxID = FDR.FaxID  

    Inner Join (select CompanyID, CompanyName from CustomerDetails) as CDT on CDT.CompanyID = FDR.CompanyID

    Using a temp table rather than a table variable might give you some improvement.
    How is your table variable created?
    How did you determine that this query is the rate-limiting step?
    This might work a little faster:

    UPDATE TMP1 SET

    DriverID = FDR.DriverIDlist,

    CompanyID = CDT.CompanyName

    FROM @TempTable1 TMP1

    CROSS APPLY (

    SELECT

    CompanyID,

    LTrim(Replace(Stuff((Select ', ' + ISNULL(Cast(FD1.DriverID as varchar(10)),'')

    FROM FaxId_Document_Relation FD1

    WHERE FD1.FaxID = FD2.FaxID

    FOR XML PATH ('')),1,1,''),' ,','')) as DriverIDlist

    FROM FaxId_Document_Relation FD2

    WHERE FD2.FaxID = TMP1.FaxID

    GROUP BY FD2.FaxID, FD2.CompanyID

    ) as FDR

    Inner Join CustomerDetails as CDT

    on CDT.CompanyID = FDR.CompanyID

    “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

  • April 6, 2018 at 7:21 am

    #1985801

    Certainly replacing the table variable with a temp table will likely lead to improvements as will the code that Chris is suggesting.

    However, that doesn't explain why you're seeing two different sets of performance for the exact same (in need of tuning) query. Have you compared the plans as I've suggested several times now?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 9 posts - 1 through 8 (of 8 total)

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