Performance of same query slower for a different user

  • I'm investigating a performance issue and have narrowed it down to the following code:

    Declare @IsMissing Bit = 1

    Select
    ADS.AccountDetailSequenceID,
    1
    From
    dbo.T_AccountDetailSequence ADS
    Join dbo.T_AccountSequence ASq On ASq.AccountSequenceID = ADS.AccountSequenceID
    Join dbo.T_AccountRun AR On AR.AccountRunID = ASq.AccountRunID
    Left Join dbo.TRef_CostCentre CC On CC.CostCentreID = ADS.CostCentreID
    Left Join dbo.TRef_Subjective SU On SU.SubjectiveID = ADS.SubjectiveID
    Left Join dbo.TRef_SubDetail SD On SD.SubDetailID = ADS.SubDetailID
    Where
    (AR.AccountStatusID = 3)
    And
    (
    ADS.ExportedImportExportLogID Is Null
    And
    ADS.NeverExported = 0
    )
    And
    (
    @IsMissing Is Null
    Or
    @IsMissing = Case When dbo.GenerateCostCode (CC.Code, SU.Code, SD.Code, '00MB') Is Null Then 1 Else 0 End
    )

    I'm running this in SSMS on the same database on the same server. When connected as a typical Windows user, it completes in a not very impressive 8 seconds, to return one row. When connected as a typical SQL user, it takes an even less impressive 44 seconds to return the same row.

    GenerateCostCode is a fairly unpleasant scalar function:

    CREATE FUNCTION dbo.GenerateCostCode 
    (
    @CostCentreCode varchar(50),
    @SubjectiveCode varchar(50),
    @SubDetailCode varchar(50),
    @ONSCode varchar(10)
    )
    RETURNS varchar(24)
    AS
    BEGIN

    DECLARE @Return varchar(24)

    SET @Return = CASE @ONSCode
    WHEN '00FY' THEN dbo.GenerateCostCode_Redacted1 (@CostCentreCode, @SubjectiveCode, @SubDetailCode)
    WHEN '00MB' THEN dbo.GenerateCostCode_Redacted2 (@CostCentreCode, @SubjectiveCode, @SubDetailCode)
    WHEN '00KG' THEN dbo.GenerateCostCode_Redacted3 (@CostCentreCode, @SubjectiveCode, @SubDetailCode)
    WHEN '00EB' THEN dbo.GenerateCostCode_Redacted4 (@CostCentreCode, @SubjectiveCode, @SubDetailCode)

    -- Many more of the same ...

    -- Use @CostCentreCode if no LA specific function defined
    ELSE Left(@CostCentreCode, 23)
    END

    RETURN @Return

    END

    I've attached the execution plans which are almost identical, the only differences being in ActualElapsedms and ActualCPUms. The 8 second / 44 second difference arises from the Filter. (See attachments.)

    Does anyone have any idea why actual elapsed time would be so much more for one user than another? Thanks.

    • This topic was modified 2 weeks ago by  julian.fletcher. Reason: Attempt to attach execution plans without them being blocked
    Attachments:
    You must be logged in to view attached files.
  • I'm probably going to fix this particular instance by reworking that terrible scalar GenerateCostCode function. However, I would like to know what's going on to satisfy my own curiosity if nothing else.

    • This reply was modified 1 week, 6 days ago by  julian.fletcher. Reason: Typo corrected
  • Execution plans now attached as a zip file.

  • Replace the Scalar Value function with a (in-line) Table Value Function and you will have a stable running potentially fast query !

    ...
    outer apply dbo.tvf_GenerateCostCode(CC.Code, SU.Code, SD.Code, '00MB') GCC

    WHERE(AR.AccountStatusID = 3)
    AND (ADS.ExportedImportExportLogID IS NULL
    AND ADS.NeverExported = 0)
    AND (@IsMissing IS NULL
    OR @IsMissing = CASE
    WHEN GCC.CostCode IS NULL
    THEN 1
    ELSE 0
    END
    );

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks Johan, but I would like to know what's going on with the original code to satisfy my own curiosity if nothing else.

  • This is just a feeling, but I have had the same problem a time or two. Turned out that different default settings of ARITHABORT between SSMS and the application resulted in different execution plans.

    For some bizarre reason different Microsoft tools/drivers use different default settings.

    SQL SERVER - Setting ARITHABORT ON for All Connecting .Net Applications - SQL Authority with Pinal Dave

     

  • Thanks kaj. That was one of my first thoughts too, but the connection properties are identical (both from SSMS), as are the execution plans apart from the values of time spent completing the query.

  • As SSMS plan compare shows, a huge chunk of time is wasted with the FILTER operator ! ( 8sec vs 43sec ! )

    2022-06-23 14_57_56-Showplan Comparison - Microsoft SQL Server Management Studio


    ( same image but hovering over the FILTER operator of the slowest plan )

    2022-06-23 14_51_29-

    I cannot explain why, but I can state this is where your culprit is.

    Depending on the content of the SVF, it may be converted to an (i)TVF in a easy way and having your query engine being able to get the full solution in its "set"-memory. That's what SQLServer is built for and where lies its strength.

     

    FYI: we have had a case ( on SQL2019 CU23 ) where we only got such query back to "normal" switching the LEGACY_CARDINALITY_ESTIMATION = ON for the database.

    2022-06-23 15_21_00-ALTER DATABASE SCOPED CONFIGURATION - SQL Server (Transact-SQL) _ Microsoft Docs

    It's worth the try.

    • This reply was modified 1 week, 5 days ago by  Johan Bijnens.
    • This reply was modified 1 week, 5 days ago by  Johan Bijnens. Reason: added: LEGACY_CARDINALITY_ESTIMATION

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Yes, I'd noticed that as per the original post

    The 8 second / 44 second difference arises from the Filter. (See attachments.)

    Does anyone have any idea why actual elapsed time would be so much more for one user than another?

    Fixing this particular bit of code is going to be relatively easy - I was just wanting to understand why there was such a big difference in the time taken for the query to complete when run as one user as compared to when run as a different user. That knowledge could possibly help me make further improvements elsewhere.

    Could the fact that one was a Windows user and the other a SQL one have anything to do with it?

  • julian.fletcher wrote:

    ....

    Could the fact that one was a Windows user and the other a SQL one have anything to do with it?

    No

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 10 posts - 1 through 10 (of 10 total)

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