SQL QUERY NIGHTMARE

,

Last week, we faced a basic and yet the only major problem that sql developers/admin had to deal with and yes your guess is right, It was with SQL QUERY RUNNING SLOW.  I will be explaining the problem below, however I want everyone reading this to help me find answers because I still haven't found one!

One more amazing aspect of the issue was that it worked fairly quickly in Development but was running like a tortoise in Production even though the number of records were practically the same.

I'll start by throwing some light on to the execution plan, so that we narrow down to the code which was the culprit here. The code is enclosed into a stored proc. On contacting my dba to enquire about the issue, I got the following replay:-



I must hand it to our dba to provide such a detailed response. 

So my job was now to look at the code and modify it. What I did was to shrink the calender table into a temp table consisting of few rows that would speed up the operation. Below you would see the culprit code and a rectified version of it.

Culprit Code :-

-- Customer Table and latest advertiser with complete hierarichy details                                              

        INSERT  INTO reporting.Obtain_CustAM_Hier

                SELECT  cod.AdvertiserId ,

                        cod.SalesConsultantId AS end_employee_sid ,

                        COALESCE(st10.end_org_team_key, stmax.END_ORG_TEAM_KEY) end_org_team_key ,

                        COALESCE(st10.end_org_area_key, stmax.END_ORG_AREA_KEY) end_org_area_key ,

                        COALESCE(st10.end_org_region_key,

                                 stmax.END_ORG_REGION_KEY) end_org_region_key ,

                        COALESCE(st10.end_org_channel_key,

                                 stmax.END_ORG_CHANNEL_KEY) end_org_channel_key ,

                        COALESCE(st10.Cycle_Qtr, latest_cod.FIN_QTR_KEY) Cycle_Qtr ,

                        cod.FromDateKey ,

                        cod.ToDateKey

                FROM    #ADOWNER cod with (nolock)

                        INNER JOIN ( SELECT DISTINCT

                                            DT.FIN_QTR_KEY ,

                                            AdvertiserId ,

                                            MAX(fromdatekey) FROMDATEKEY ,

                                            MAX(ToDateKey) TODATEKEY

                                     FROM   ( SELECT    ADV.* ,

                                                        DT.FIN_QTR_KEY END_QTR

                                              FROM      ( SELECT

                                                              AdvertiserId ,

                                                              DT.FIN_QTR_KEY STRT_QTR ,

                                                              MAX(FromDateKey) AS fromdatekey ,

                                                              MAX(ToDateKey) AS ToDateKey

                                                          FROM

                                                              #ADOWNER AO

                                                              INNER JOIN CustomerMeasurementTool.workflow.Calendar DT ON DT.DATE_KEY = AO.FromDateKey

                                                              AND FIN_QTR_KEY >= 20131

                                                          GROUP BY AdvertiserId ,

                                                              DT.FIN_QTR_KEY

                                                        ) ADV

                                                        LEFT JOIN CustomerMeasurementTool.workflow.Calendar DT ON DT.DATE_KEY = ADV.ToDateKey

                                            ) AO

                                            LEFT JOIN CustomerMeasurementTool.workflow.Calendar DT ON DT.DATE_KEY BETWEEN fromdatekey AND ToDateKey

                                     WHERE  CALENDAR_DATE <= CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE()

                                            - 1, 101))

                                     GROUP BY FIN_QTR_KEY ,

                                            AdvertiserId

                                   ) latest_cod ON cod.AdvertiserId = latest_cod.AdvertiserId

                                                   AND cod.FromDateKey = latest_cod.FROMDATEKEY

                        LEFT JOIN #ALL_CONSULATANTS_Prev st10 ON cod.SalesConsultantId = st10.end_employee_sid

                                                              AND latest_cod.FIN_QTR_KEY = st10.Cycle_Qtr

                        LEFT JOIN ( SELECT  EHD.EMPLOYEE_SID AS END_EMPLOYEE_SID ,

                                            EHD.ORG_TEAM_KEY AS END_ORG_TEAM_KEY ,

                                            EHD.ORG_AREA_KEY AS END_ORG_AREA_KEY ,

                                            EHD.ORG_REGION_KEY AS END_ORG_REGION_KEY ,

                                            EHD.ORG_CHANNEL_KEY AS END_ORG_CHANNEL_KEY

                                    FROM    [BIStage].[dbo].[EMPLOYEE_HIST_DIM_V] EHD with (nolock)

                                            INNER JOIN ( SELECT

                                                              EMPLOYEE_SID ,

                                                              MAX(BEGIN_DATE_KEY) AS BEGIN_DATE_KEY

                                                         FROM [BIStage].[dbo].[EMPLOYEE_HIST_DIM_V] with (nolock)

                                                         WHERE

                                                              POSITION_KEY > 0

                                                         GROUP BY EMPLOYEE_SID

                                                       ) EMP ON EHD.EMPLOYEE_SID = EMP.EMPLOYEE_SID

                                                              AND EHD.BEGIN_DATE_KEY = EMP.BEGIN_DATE_KEY

                                  ) stmax ON cod.SalesConsultantId = stmax.END_EMPLOYEE_SID; 

        

      

CHANGED CODE :-

I have changed the yellowed part of the code and insert new code higlighted in green as :-

-- Temp Tables to build a mini version of the Calender Table.

select * into #Mini_Cal from CustomerMeasurementTool.workflow.Calendar where FIN_QTR_KEY >= 20131

SELECT DISTINCT

                                            DT.FIN_QTR_KEY ,

                                            AdvertiserId ,

                                            MAX(fromdatekey) FROMDATEKEY ,

                                            MAX(ToDateKey) TODATEKEY into #latest_cod

                                     FROM   ( SELECT    ADV.* ,

                                                        DT.FIN_QTR_KEY END_QTR

                                              FROM      ( SELECT

                                                              AdvertiserId ,

                                                              DT.FIN_QTR_KEY STRT_QTR ,

                                                              MAX(FromDateKey) AS fromdatekey ,

                                                              MAX(ToDateKey) AS ToDateKey

                                                          FROM

                                                              #ADOWNER AO

                                                              INNER JOIN #Mini_Cal DT ON DT.DATE_KEY = AO.FromDateKey

                                                             

                                                          GROUP BY AdvertiserId ,

                                                              DT.FIN_QTR_KEY

                                                        ) ADV

                                                        LEFT JOIN #Mini_Cal DT ON DT.DATE_KEY = ADV.ToDateKey

                                            ) AO

                                            LEFT JOIN #Mini_Cal DT ON DT.DATE_KEY BETWEEN fromdatekey AND ToDateKey

                                     WHERE  CALENDAR_DATE <= CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE()

                                            - 1, 101))

                                     GROUP BY FIN_QTR_KEY ,

                                            AdvertiserId

     

-- Customer Table and latest advertiser with complete hierarichy details                                              

        INSERT  INTO reporting.Obtain_CustAM_Hier

                SELECT  cod.AdvertiserId ,

                        cod.SalesConsultantId AS end_employee_sid ,

                        COALESCE(st10.end_org_team_key, stmax.END_ORG_TEAM_KEY) end_org_team_key ,

                        COALESCE(st10.end_org_area_key, stmax.END_ORG_AREA_KEY) end_org_area_key ,

                        COALESCE(st10.end_org_region_key,

                                 stmax.END_ORG_REGION_KEY) end_org_region_key ,

                        COALESCE(st10.end_org_channel_key,

                                 stmax.END_ORG_CHANNEL_KEY) end_org_channel_key ,

                        COALESCE(st10.Cycle_Qtr, latest_cod.FIN_QTR_KEY) Cycle_Qtr ,

                        cod.FromDateKey ,

                        cod.ToDateKey

                FROM    #ADOWNER cod with (nolock)

                        INNER JOIN  #latest_cod latest_cod ON cod.AdvertiserId = latest_cod.AdvertiserId

                                                   AND cod.FromDateKey = latest_cod.FROMDATEKEY

                        LEFT JOIN #ALL_CONSULATANTS_Prev st10 ON cod.SalesConsultantId = st10.end_employee_sid

                                                              AND latest_cod.FIN_QTR_KEY = st10.Cycle_Qtr

                        LEFT JOIN ( SELECT  EHD.EMPLOYEE_SID AS END_EMPLOYEE_SID ,

                                            EHD.ORG_TEAM_KEY AS END_ORG_TEAM_KEY ,

                                            EHD.ORG_AREA_KEY AS END_ORG_AREA_KEY ,

                                            EHD.ORG_REGION_KEY AS END_ORG_REGION_KEY ,

                                            EHD.ORG_CHANNEL_KEY AS END_ORG_CHANNEL_KEY

                                    FROM    [BIStage].[dbo].[EMPLOYEE_HIST_DIM_V] EHD with (nolock)

                                            INNER JOIN ( SELECT

                                                              EMPLOYEE_SID ,

                                                              MAX(BEGIN_DATE_KEY) AS BEGIN_DATE_KEY

                                                         FROM [BIStage].[dbo].[EMPLOYEE_HIST_DIM_V] with (nolock)

                                                         WHERE

                                                              POSITION_KEY > 0

                                                         GROUP BY EMPLOYEE_SID

                                                       ) EMP ON EHD.EMPLOYEE_SID = EMP.EMPLOYEE_SID

                                                              AND EHD.BEGIN_DATE_KEY = EMP.BEGIN_DATE_KEY

                                  ) stmax ON cod.SalesConsultantId = stmax.END_EMPLOYEE_SID;




Apologies for such a messy code, however if you are able to follow or understand the code (by going through highlighted rows) you will find that I have done following things:-
1. Limited the Calendar table.
2. Taken the complicated code out and put it into a temp table.


The above changes did the trick and the stored proc ran within the SLA of 6 minutes (was taking 3 hours on that fatal day).

However the query which remained unanswered is why the stored proc ran much faster in our DEV environment than compared to PROD environment.

I know I may not have given the complete info, please ask questions to solve this conundrum!


Rate

Share

Share

Rate