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
ENDI'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 years, 5 months ago by julian.fletcher. Reason: Attempt to attach execution plans without them being blocked
-
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.
-
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.
-
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 ! )
( same image but hovering over the FILTER operator of the slowest plan )
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.
It's worth the try.
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply