Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Distinct with left outer join slow in 2008

Author  Topic  amey_pg Starting Member 2 Posts Posted - 2011-03-15 : 10:26:34 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