March 30, 2023 at 2:22 am
Good Morning,
I have the below procedure, in both UAT & PROD same data in source tables (record counts also matched for all source tables in both UAT & PROD) but UAT takes only 1 minute and Prod takes 11 minutes. any idea what can I do/check to make it run faster.
just fyi I removed all DISTINCT (as there is no meaning with group by and distant together) it is running fast in prod but what it means? is there any way can we refactor/optimize this procedure? please advise
CREATE PROCEDURE [dbo].[MYSAMPLEPROC] (
--DECLARE
@RequesterName VARCHAR(80) = 'Lana.Sita@NSW.EDU.AU',
@EnrollYear INT = 2022,
@MUNICIPALITYTYPE VARCHAR(MAX) = 'ALL',
@MUNICIPALITY_ID VARCHAR(MAX) = '9995',
@INSTITUTION_ID VARCHAR(MAX) = '-1',
@CREDITLEVELList VARCHAR(250) = 'ALL'
)
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF @MUNICIPALITY_ID = 'All'
SET @MUNICIPALITY_ID = '-1';
IF @INSTITUTION_ID = 'All'
SET @INSTITUTION_ID = '-1';
IF @MUNICIPALITYTYPE = 'All'
SET @MUNICIPALITYTYPE = '-1';
IF @CREDITLEVELList = 'All'
SET @CREDITLEVELList = '-1';
WITH MYCTETEMP
AS ( SELECT DISTINCT
FEP.PERSON_KEY,
STU.NAME,
SCH.COLLEGE_NAME AS SchoolName,
SCH.ENROLL_YEAR AS EnrollYear,
SCH.INSTITUTION_ID AS INSTITUTION_ID,
SCH.MUNICIPALITY_ID AS MUNICIPALITY_ID,
d.MUNICIPALITY_NAME AS MUNICIPALITY_Name,
FEP.ENROLLMENTS_COUNT,
Classification_Of__Endorsement = CASE WHEN MAX (Classification_Of__Endorsement.Classification_Key) IS NOT NULL THEN 'Y'
ELSE 'N'
END,
Classification_Of__Career_Endorsement = CASE WHEN MAX (Classification_Of__Career_Endorsement.Classification_Key) IS NOT NULL THEN 'Y'
ELSE 'N'
END,
Classification_Of__Honors_Endorsement = CASE WHEN MAX (Classification_Of__Honors_Endorsement.Classification_Key) IS NOT NULL THEN 'Y'
ELSE 'N'
END,
[Parking Status Color] = 'White',
[Parking Status Color2] = 'Blue',
[Parking Status Color3] = 'White'
FROM
[NSWEDU].[dbo].[FCT_ENROLLMENTS] ( NOLOCK ) AS FEP
JOIN NSWEDU.dbo.DIM_MUNICIPALITY d
ON FEP.MUNICIPALITY_KEY = d.MUNICIPALITY_KEY
JOIN [NSWEDU].[dbo].[DIM_PERSON] ( NOLOCK ) AS STU
ON STU.PERSON_KEY = FEP.PERSON_KEY
CROSS APPLY ( SELECT
PERSON_KEY
FROM
[NSWEDU].[dbo].[VW_MUNICIPAL_PERSON_KEYs] skv WITH ( NOLOCK )
WHERE
RequesterName = @RequesterName
AND ALLOW_HISTORICAL_PERSON_LOOKUP = 0
AND skv.PERSON_KEY = STU.PERSON_KEY ) OptiSec
JOIN [NSWEDU].dbo.[DIM_COLLEGE] ( NOLOCK ) AS SCH
ON SCH.INSTITUTION_KEY = FEP.INSTITUTION_KEY
LEFT JOIN ( SELECT DISTINCT
FSGx.PERSON_KEY,
Classification_Key,
s.STATE_ID_NUMBER
FROM
NSWEDU.dbo.FCT_PERSON_GRADUATION AS FSGx
JOIN NSWEDU.dbo.DIM_PERSON AS s WITH ( NOLOCK )
ON FSGx.PERSON_KEY = s.PERSON_KEY
JOIN NSWEDU.dbo.DIM_PERSON_ACADEMIC_YEAR AS b
ON b.ACAD_PERSON_YEAR_KEY = FSGx.ACAD_PERSON_YEAR_KEY
WHERE
FSGx.Classification_Key = 1
AND b.ENROLL_YEAR = @EnrollYear ) Classification_Of__Endorsement
ON Classification_Of__Endorsement.STATE_ID_NUMBER = STU.STATE_ID_NUMBER
LEFT JOIN ( SELECT DISTINCT
FSGx.PERSON_KEY,
Classification_Key,
s.STATE_ID_NUMBER
FROM
NSWEDU.dbo.FCT_PERSON_GRADUATION AS FSGx
JOIN NSWEDU.dbo.DIM_PERSON AS s WITH ( NOLOCK )
ON FSGx.PERSON_KEY = s.PERSON_KEY
JOIN NSWEDU.dbo.DIM_PERSON_ACADEMIC_YEAR AS b
ON b.ACAD_PERSON_YEAR_KEY = FSGx.ACAD_PERSON_YEAR_KEY
WHERE
FSGx.Classification_Key = 2
AND b.ENROLL_YEAR = @EnrollYear ) Classification_Of__Career_Endorsement
ON Classification_Of__Career_Endorsement.STATE_ID_NUMBER = STU.STATE_ID_NUMBER
LEFT JOIN ( SELECT DISTINCT
FSGx.PERSON_KEY,
Classification_Key,
s.STATE_ID_NUMBER
FROM
NSWEDU.dbo.FCT_PERSON_GRADUATION AS FSGx
JOIN NSWEDU.dbo.DIM_PERSON AS s WITH ( NOLOCK )
ON FSGx.PERSON_KEY = s.PERSON_KEY
JOIN NSWEDU.dbo.DIM_PERSON_ACADEMIC_YEAR AS b
ON b.ACAD_PERSON_YEAR_KEY = FSGx.ACAD_PERSON_YEAR_KEY
WHERE
FSGx.Classification_Key = 3
AND b.ENROLL_YEAR = @EnrollYear ) Classification_Of__Honors_Endorsement
ON Classification_Of__Honors_Endorsement.STATE_ID_NUMBER = STU.STATE_ID_NUMBER
WHERE
1 = 1
AND SCH.ENROLL_YEAR = @EnrollYear
AND ( CHARINDEX (d.MUNICIPALITY_ID, @MUNICIPALITY_ID) > 0
OR @MUNICIPALITY_ID = '-1' )
AND ( CHARINDEX (d.MUNICIPALITY_ID + '-' + SCH.INSTITUTION_ID, @INSTITUTION_ID) > 0
OR @INSTITUTION_ID = '-1' )
AND ( CHARINDEX (d.DISTRICT_TYPE_CD, @MUNICIPALITYTYPE) > 0
OR @MUNICIPALITYTYPE = '-1' )
AND ( CHARINDEX (STU.STATE_GRADE_LEVEL_CD, @CREDITLEVELList) > 0
OR @CREDITLEVELList = '-1'
AND STATE_GRADE_LEVEL_CD IN ( '09', '10', '11', '12' ))
AND d.MUNICIPALITY_ID != '9001'
AND FEP.ENROLLMENT_TYPE != 'K'
AND FEP.NOSHOW_COUNT = 0
AND FEP.ENROLLMENT_COUNT = 1
AND COLLEGE_NAME NOT LIKE ( '%Distant%University%' )
GROUP BY
FEP.PERSON_KEY,
STU.SORT_NAME,
STU.NAME,
STU.LAST_NAME,
STU.FIRST_NAME,
STU.UNIQUE_ID,
STU.LOCAL_STUDENT_ID,
STU.STATE_ID_NUMBER,
STU.PERSON_KEY,
STU.GENDER_CD,
STU.GENDER_DESC,
STU.RACE_CD,
STU.RACE_DESC,
STU.LIMITED_ENGLISH_PROFICIENCY_IND,
STU.ECONOMICALLY_DISADVANTAGED_IND,
STU.SPECIAL_EDUCATION_IND,
STU.DISABILITY_IND,
STU.MIGRANT_IND,
STU.STATE_GRADE_LEVEL_CD,
STU.STATE_GRADE_LEVEL_SORT_ORDER,
SCH.COLLEGE_NAME,
SCH.ENROLL_YEAR,
SCH.INSTITUTION_ID,
SCH.MUNICIPALITY_ID,
d.DISTRICT_PUBLIC_NAME,
FEP.ENROLLMENT_COUNT,
STU.CLASS_OF )
SELECT
b.PERSON_KEY,
b.SORT_NAME,
b.NAME,
CASE WHEN b.RACE_DESC = 'Mixed Race' THEN 'Two or More Races - TR'
WHEN b.RACE_DESC = 'Hispanic' THEN 'Hispanic/Latino - HI'
WHEN b.RACE_DESC = 'Hawaiian or Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander - HI'
ELSE b.RACE_DESC + ' - ' + b.RACE_CD
END AS Race_Desc,
b.EnrollYear,
b.INSTITUTION_ID,
b.MUNICIPALITY_ID,
b.District_Name,
b.ENROLLMENT_COUNT,
b.CLASS_OF,
b.Classification_Of__Endorsement,
b.Classification_Of__Career_Endorsement,
b.Classification_Of__Honors_Endorsement,
[Parking Status Color] = CASE WHEN Classification_Of__Endorsement = 'Y' THEN 'PINK'
WHEN Classification_Of__Endorsement = 'N'
AND Classification_Of__Career_Endorsement = 'N'
AND Classification_Of__Honors_Endorsement = 'N' THEN 'BROWN'
ELSE 'White'
END,
[Parking Status Color2] = CASE WHEN Classification_Of__Career_Endorsement = 'Y' THEN 'PINK'
WHEN Classification_Of__Endorsement = 'N'
AND Classification_Of__Career_Endorsement = 'N'
AND Classification_Of__Honors_Endorsement = 'N' THEN 'BROWN'
ELSE 'White'
END,
[Parking Status Color3] = CASE WHEN Classification_Of__Honors_Endorsement = 'Y' THEN 'PINK'
WHEN Classification_Of__Endorsement = 'N'
AND Classification_Of__Career_Endorsement = 'N'
AND Classification_Of__Honors_Endorsement = 'N' THEN 'BROWN'
ELSE 'White'
END
FROM
MYCTETEMP b
GROUP BY
b.PERSON_KEY,
b.SORT_NAME,
b.NAME,
b.EnrollYear,
b.INSTITUTION_ID,
b.MUNICIPALITY_ID,
b.District_Name,
b.ENROLLMENT_COUNT,
b.CLASS_OF,
b.Classification_Of__Endorsement,
b.Classification_Of__Career_Endorsement,
b.Classification_Of__Honors_Endorsement
ORDER BY
b.SORT_NAME,
b.STATE_ID_NUMBER,
b.SchoolName;
GO
Thanks
Sita
March 30, 2023 at 8:06 am
Compare the execution plans in both environments. Are they identical?
Are the hardware, o/s and SQL Server versions the same?
Have you checked for blocking?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 30, 2023 at 8:26 am
and speaking of blocking.
whoever did the code does not really understand what "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" and "with (nolock)" do.
apart from the fact that it allows you to get "bad data" they both do EXACTLY the same thing - so if you can allow for potential bad data on your results KEEP the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" on the code and remove all those "with (nolock) from the remaining of the code.
regarding performance - you may need to use the divide and conquer approach - e.g. create temp tables of some of those intermediary queries and use those instead - and also review the use of distinct - in most cases when these are used is because the required/correct joins/filtering is not being applied.
March 30, 2023 at 12:20 pm
and speaking of blocking.
whoever did the code does not really understand what "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" and "with (nolock)" do.
apart from the fact that it allows you to get "bad data" they both do EXACTLY the same thing - so if you can allow for potential bad data on your results KEEP the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" on the code and remove all those "with (nolock) from the remaining of the code.
regarding performance - you may need to use the divide and conquer approach - e.g. create temp tables of some of those intermediary queries and use those instead - and also review the use of distinct - in most cases when these are used is because the required/correct joins/filtering is not being applied.
THIS!!!
"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
March 30, 2023 at 12:32 pm
All the DISTINCT operators... Either, your data structures are very poor, so you can't guarantee unique rows based on standard queries, or, someone thinks this is how all queries must be written. Personally, I'd toss every single one and see if you get the same data. I wouldn't even worry about performance yet.
Next, these:
CHARINDEX (STU.STATE_GRADE_LEVEL_CD, @CREDITLEVELList) > 0
That's going to cause scans no matter what. You'll need to replace this with some other logic that can take advantage of indexes in your database.
Finally, what is that a 27 column GROUP BY statement followed by a 12 column GROUP BY statement and yet, not one single aggregation? No averages or counts or anything? So, what is the GROUP BY doing for you? Is it just another way to say DISTINCT? Once more, it points to issues with your structures and your data that should be addressed first.
As to why the differences between production & uat, you only mention record counts. Honestly, that's meaningless when it comes to query optimization. The real question is statistics. What do they look like on each of the tables involved between UAT & Prod. Are they the same? Is the data distribution physically different? That will lead to differences in execution plans generated and performance. Also, server setttings, from Max Degree of Parallelism to Cost Threshold for Parallelism, Memory allocated, ANSI connection settings and more, all can affect plans generated and performance. Database settings too. It sounds like these are likely not identical databases. Check the settings there.
To truly compare two environments and get anything approaching accuracy in the comparison, they have to be as close as possible to being identical. And where they are not, let's say production has more or faster CPU, you can readily explain the differences in 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 1, 2023 at 5:22 pm
I concur with the others' assessements and emphasize that my first go to would be to compare the servers as you have stated the query is the same on DEV, UAT, and PROD and it runs slow only on UAT -- not usually the server with least resources or poor settings but perhaps in your case it is. You have several fairly good bits of information provided by the others as to where to look but if that does not explain it perhaps dive a bit deeper. I mean basically if all the underlying structure and settings are the same and the various tables and their indexes are the same -- a Script does not suddenly act differently on its own.
So line up the ducks and give each a squeeze and find the one that honks rather than quacks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply