SQL Server Performance

Distinct with left outer join slow in 2008

Discussion in 'SQL Server 2008 General DBA Questions' started by amey_pg, Mar 15, 2011.

  1. amey_pg New Member

    Dear All,

    I'm facing the while running below query in 2008 SP2 server. I tried to backup and restore the same database in 2000 and restore on 2008. when i ran the same query on 2000 it fatch 3998 records in 5 min. but when i ran the same query on 2008 it dones't shown output even ran since 16-17 hours.


    even i tried update stats index no used of execution plan.....:(

    ----------------------------------------------------

    ALTER VIEW [dbo].[V_EAI_COMMON_INTERFACE]

    AS
    SELECT DISTINCT
    rpt.APP_ID,
    rpt.APP_CD,
    rpt.APP_NM,
    cast(rpt.APP_DSCR as varchar(8000)),
    rpt.STTS_DSCR,
    rpt.LOB_ID,
    rpt.LOB_NM,
    rpt.ADMN_LOB_ID,
    rpt.ADMN_LOB_NM,
    rpt.ADMN_LOB_NM,
    rpt.AD_PM_NM,
    rpt.AD_PM_CERT_NUM,
    rpt.CRE_NM,
    rpt.CRE_CERT_NUM,
    rpt.AD_SCND_LVL_MGR_NM,
    rpt.AD_SCND_LVL_MGR_CERT_NUM,
    rpt.AD_THRD_LVL_MGR_NM,
    rpt.CIO_NM,
    rpt.CIO_CERT_NUM,
    isnull(rpt.CRIT_IND,'N') AS CRIT_IND,
    rpt.CMN_NM,
    RTRIM(LTRIM((dbo.FN_GET_APPID_LST(rpt.CMN_NM)))) AS APPID_LST, -- List of applications with same common name.
    RTRIM(LTRIM((dbo.FN_CONCAT_ROWS(rpt.APP_ID,10)))) AS ARM_NM_LST,
    RTRIM(LTRIM((dbo.FN_CONCAT_ROWS(rpt.APP_ID,9)))) AS ARM_NUM_LST,
    LEFT(prj.PROJ_CD,4),
    rpt.MSTR_APP_NM,
    rpt.DCOMM_DT,
    rpt.CO_CD,
    rpt.AD_PM_EMAIL_ADR,
    rpt.MSTR_APP_ID,
    rpt.MSTR_APP_CD,
    rpt.MSTR_APP_DSCR,
    rpt.CO_NM,
    rpt.USR_CNT,
    rpt.PROD_DT,
    rpt.BUS_SPNSR_NM,
    rpt.PLTFRM_NM,
    rpt.PROD_DSCR,
    rpt.TECH_NM,
    rpt.CRT_DT,
    rpt.ACT_SUNSET_DT,
    rpt.SUNSET_RSN_TXT,
    rpt.BXG_ID,
    rpt.BXG_NM,
    rpt.APP_IND,
    rpt.AD_SPRT_IND,
    rpt.AD_THRD_LVL_MGR_CERT_NUM,
    rpt.BUS_SPNSR_CERT_NUM,
    rpt.PLN_IMPL_DT,
    rpt.APP_ORIG_CO_NM,
    rpt.ORIG_APP_CD,
    rpt.ORIG_APP_NM,
    rpt.INT_DPOS_DSCR,
    rpt.INT_HOST_APP_ID,
    rpt.POC_TGT_CMPLT_DT,
    rpt.CANC_DT,
    rpt.CANC_RSN_TXT,
    rpt.ALS_NM,
    rpt.CERT_DT,
    rpt.XPCT_SUNSET_DT,
    rpt.CERT_IND,
    rpt.RGLTRY_ATRB,
    rpt.MSTR_APP_STTS_CD,
    rpt.MSG_TYP_IND,
    rpt.MSG_TYPS,
    rpt.PORTFOL_MGR_LIST_TXT,
    rpt.AD_SUPR_PM_LIST_TXT,
    rpt.SLO_PCT,
    rpt.MRCRY_ITG_PROJ_NUM,
    rpt.SR_BUS_LEAD_CERT_NUM,
    rpt.SR_BUS_LEAD_NM,
    rpt.BUS_LEAD_CERT_NUM,
    rpt.BUS_LEAD_NM,
    rpt.BUS_WRK_CLNT_CERT_NUM,
    rpt.BUS_WRK_CLNT_NM,
    PROJALL.PROJ_CD,
    PROJALL.PROJ_TYP_DSCR,
    PROJALL.PROJ_STTS_DSCR,
    TPM.PHYS_SRVR_NM,
    TPM.LPAR_SRVR_NM,
    TPM.DPAR_NM,
    TPM.SRVR_TYP_NM,
    LOC.LOC_NM,
    TPM.ENVRN_NM,
    TPM.STTS_DSCR,
    TPM.OPER_SYS_NM,
    TPM.SRL_NUM,
    DPT.DEPT_NM,
    CASE ISNULL(AVSCH.SCHD_DAY_DSCR,'')
    WHEN '' THEN HRS.SCHD_DAY_DSCR
    ELSE AVSCH.SCHD_DAY_DSCR
    END AS [DAY],
    HRS.STRT_TM AS [Hours of operation - Start Time],
    HRS.END_TM AS [Hours of operation - End Time],
    HRS.HR_CNT AS [Hours of operation - Num Of Hours],
    AVSCH.STRT_TM AS [Scheduled Maintenance Window - Start Time],
    AVSCH.END_TM AS [Scheduled Maintenance Window - End Time],
    AVSCH.HR_CNT AS [Scheduled Maintenance Window - Num Of Hours],
    PM.FRST_NM,
    PM.LST_NM,
    PM.WRK_TEL_NUM,
    CIO.FRST_NM,
    CIO.LST_NM,
    CIO.WRK_TEL_NUM,
    CIO.EMAIL_ADR_LN,
    CRE.FRST_NM,
    CRE.LST_NM,
    CRE.WRK_TEL_NUM,
    CRE.EMAIL_ADR_LN,
    PM.DOC_ID,
    BS.DOC_ID,
    BXG.BXG_CD,
    LOB.LOB_CD,
    RPT.APP_CLASS_DSCR,
    RPT.APP_CNTN_TYP_DSCR,
    RPT.DATA_STOR_DSCR,
    RPT.DATA_STOR_LOC_DSCR,
    RPT.PRVLG_INFO_IND,
    RPT.PROP_INFO_IND,
    RPT.PDCSR_SYS_IND,
    RPT.PDCSR_SYS_DSCR,
    RPT.SRCH_ABL_IND,
    RPT.DATA_SRCH_XPRT_IND,
    RPT.AUTO_DEL_IND,
    RPT.AUTO_DEL_DSCR,
    RPT.DABL_AUTO_DEL_IND,
    RPT.DABL_AUTO_DEL_DSCR,
    RPT.CPTR_BACKUP_INFO_IND,
    RPT.DR_PLN_IN_ARDB_IND,
    RPT.DR_PLN_LOC_DSCR,
    RPT.BCKUP_SCHD_DSCR,
    RPT.BACKUP_TYP_DSCR,
    RPT.BACKUP_MDM_DSCR,
    RPT.BACKUP_STOR_LOC_DSCR,
    RPT.BACKUP_INFO_RTR_DSCR,
    RPT.BACKUP_RSPN_PARTY_DSCR,
    RPT.BACKUP_SFTWR_DSCR,
    RPT.DATA_RNG_STRT_DT,
    RPT.DATA_RNG_END_DT,
    RPT.RPO_TM,
    RPT.RTO_TM,
    TPM.TRU_ISLA_TYP_NM,
    RPT.CNFR_INFO_TXT ,
    RPT.TECH_CNTCT_NM,
    RPT.CALC_IC_NUM,
    RPT.CALC_TC_NUM,
    RPT.ASSOC_TYP_CD,
    TPM.ISLA_TYP_NM
    FROM DBO.T_RPT RPT
    LEFT OUTER JOIN T_PROJ prj
    ON rpt.APP_ID = prj.APP_ID and PROJ_TYP_DSCR = 'Services' AND PROJ_STTS_DSCR = 'Active'
    LEFT OUTER JOIN T_PROJ PROJALL ON(RPT.APP_ID = PROJALL.APP_ID AND PROJALL.PROJ_STTS_DSCR = 'Active')
    LEFT OUTER JOIN T_TPM_SRVR TPM ON(TPM.APP_ID=RPT.APP_ID)
    LEFT OUTER JOIN T_LOC LOC ON(LOC.LOC_CD=TPM.LOC_CD)
    LEFT OUTER JOIN T_AVLB_SCHD AS SCHD ON(SCHD.APP_ID=RPT.APP_ID)
    LEFT OUTER JOIN T_EMPE EMPE ON(EMPE.CERT_NUM= rpt.CRE_CERT_NUM)
    LEFT OUTER JOIN T_DEPT DPT ON(EMPE.DEPT_CD=DPT.DEPT_CD)
    LEFT OUTER JOIN dbo.T_AVLB_SCHD Hrs ON RPT.APP_ID = Hrs.APP_ID and Hrs.AVLB_PURP_CD = 'H'
    LEFT OUTER JOIN dbo.T_AVLB_SCHD avsch ON RPT.APP_ID = avsch.APP_ID and avsch.AVLB_PURP_CD = 'M' AND avsch.SCHD_DAY_DSCR = Hrs.SCHD_DAY_DSCR
    LEFT OUTER JOIN T_EMPE PM ON(RPT.AD_PM_CERT_NUM=PM.CERT_NUM)
    LEFT OUTER JOIN T_EMPE CIO ON(RPT.CIO_CERT_NUM=CIO.CERT_NUM)
    LEFT OUTER JOIN T_EMPE CRE ON(RPT.CRE_CERT_NUM=CRE.CERT_NUM)
    LEFT OUTER JOIN T_EMPE BS ON(RPT.BUS_SPNSR_CERT_NUM=BS.CERT_NUM)
    LEFT OUTER JOIN T_BXG AS BXG ON(RPT.BXG_ID=BXG.BXG_ID)
    LEFT OUTER JOIN T_LOB AS LOB ON(RPT.LOB_ID=LOB.LOB_ID)

    WHERE
    NOT (RPT.MSTR_APP_NM LIKE '%DELETE%' OR RPT.MSTR_APP_NM LIKE '%CLOSED%')



    GO

  2. Flexdog New Member

    Assuming that both sql 2k & 2k8 are on the hw/load baseline (equiv hw, same load, et..).
    My WAG that for this qry 'DISTINCT' in 2k uses hashtable while 2k8 plan uses groupby. Quite obvious that some of the LEFTJOINs are not 1-to-1 (partially cartesian), thus workingset became too large which thrash 2k8. Easy to test this theory:
    SELECT count(*)
    FROM dbo.t_rpt r
    LEFT JOIN ...
    WHERE ...
    test0 - no left join
    test1 - left join with t_proj only
    testN - left join N entities
    Once you identified the join that cause data explosion <= fix it. Aha, maybe it's not about 2k or 2k8 after all.
    Best,

Share This Page