same Procedure with same source tables in 2 environments but delay in exec in 1

  • 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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

  • frederico_fonseca wrote:

    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

  • 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

  • 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