help needed to optimize query

Question

Post reply

help needed to optimize query

uday-417581

SSC Rookie

Points: 29
More actions
June 6, 2007 at 2:21 pm

#176980

Hi,
I am new to databases, can any one help me out to improve the performance of the below query,
SELECT DISTINCT INTNL_PTNT_NUM,REG_FCLTY_CD,FCLTY_CD_DESC,MRN_REG,MRN_PTNT, LAST_NAME,FIRST_NAME,MID_NAME,UVS_PTNT_NUM,
BRTH_DT,SSN,SEX_CD,INTNL_TST_NUM,TST_CD,TST_CD_DESC,TST_PRI_CD,PRI_CD_DESC,PROD_CD,PROD_CD_DESC,DONTN_ID1,CHK_DGT,TST_RST_FRE_DESC,
TST_PRFM_FCLTY_CD,TST_PRFM_LAB_DESC,VRFY_TECH_ID,VRFY_DTTM,VOL_NUM,EXP_DTTM,SEG_NUM,MANF_LOT_NUM,DONTN_ID,PROD_ABO,
PROD_RH,ORD_INTRFC_NUM,ORD_NUM,ORD_PHYSN_NUM,ORD_PHYSN_FRE_DESC,ORD_REQ_DTTM,ICD9_CD,ICD9_FRE_DESC,INTNL_PF_NUM,
INTNL_UNIT_NUM,INTNL_DRV_NUM,INTPRTN_CD_DESC,DIVISION,UNIT_STAT_CD,PRFM_FCLTY_CD,NAME_ALS_NUM,MRN_ALS_NUM
FROM V_RPT_TRANS1
UNION
SELECT DISTINCT INTNL_PTNT_NUM,REG_FCLTY_CD,FCLTY_CD_DESC,MRN_REG,MRN_PTNT, LAST_NAME,FIRST_NAME,MID_NAME,UVS_PTNT_NUM,
BRTH_DT,SSN,SEX_CD,INTNL_TST_NUM,TST_CD,TST_CD_DESC,TST_PRI_CD,PRI_CD_DESC,PROD_CD,PROD_CD_DESC,DONTN_ID1,CHK_DGT,TST_RST_FRE_DESC,
TST_PRFM_FCLTY_CD,TST_PRFM_LAB_DESC,VRFY_TECH_ID,VRFY_DTTM,VOL_NUM,EXP_DTTM,SEG_NUM,MANF_LOT_NUM,DONTN_ID,PROD_ABO,
PROD_RH,ORD_INTRFC_NUM,ORD_NUM,ORD_PHYSN_NUM,ORD_PHYSN_FRE_DESC,ORD_REQ_DTTM,ICD9_CD,ICD9_FRE_DESC,INTNL_PF_NUM,
INTNL_UNIT_NUM,INTNL_DRV_NUM,INTPRTN_CD_DESC,DIVISION,UNIT_STAT_CD,PRFM_FCLTY_CD,NAME_ALS_NUM,MRN_ALS_NUM
FROM V_RPT_TRANS2 WHERE INTNL_PF_NUM NOT IN (SELECT INTNL_PF_NUM FROM V_RPT_TRANS1)
UNION
SELECT DISTINCT INTNL_PTNT_NUM,REG_FCLTY_CD,FCLTY_CD_DESC,MRN_REG,MRN_PTNT, LAST_NAME,FIRST_NAME,MID_NAME,UVS_PTNT_NUM,
BRTH_DT,SSN,SEX_CD,INTNL_TST_NUM,TST_CD,TST_CD_DESC,TST_PRI_CD,PRI_CD_DESC,PROD_CD,PROD_CD_DESC,DONTN_ID1,CHK_DGT,TST_RST_FRE_DESC,
TST_PRFM_FCLTY_CD,TST_PRFM_LAB_DESC,VRFY_TECH_ID,VRFY_DTTM,VOL_NUM,EXP_DTTM,SEG_NUM,MANF_LOT_NUM,DONTN_ID,PROD_ABO,
PROD_RH,ORD_INTRFC_NUM,ORD_NUM,ORD_PHYSN_NUM,ORD_PHYSN_FRE_DESC,ORD_REQ_DTTM,ICD9_CD,ICD9_FRE_DESC,INTNL_PF_NUM,
INTNL_UNIT_NUM,INTNL_DRV_NUM,INTPRTN_CD_DESC,DIVISION,UNIT_STAT_CD,PRFM_FCLTY_CD,NAME_ALS_NUM,MRN_ALS_NUM
FROM V_RPT_TRANS3
wherer V_RPT_TRANS1,V_RPT_TRANS2,V_RPT_TRANS3 are as,
V_RPT_TRANS1
------------
CREATE VIEW V_RPT_TRANS1 AS
SELECT A.INTNL_PTNT_NUM, A.REG_INTO_FCLTY_CD AS REG_FCLTY_CD, B.FCLTY_CD_DESC, A.MED_REC_NUM AS MRN_REG,
C.MED_REC_NUM AS MRN_PTNT, A.PTNT_LST_NAM AS LAST_NAME, A.PTNT_FST_NAM AS FIRST_NAME, A.PTNT_MID_NAM AS MID_NAME,
D1.UVS_PTNT_NUM, D1.BRTH_DT, D1.SSN, D1.SEX_CD, T.INTNL_TST_NUM, T.TST_CD, T1.TST_CD_DESC, T.TST_PRI_CD, T2.PRI_CD_DESC, U1.PROD_CD, U.PROD_CD_DESC, U1.RCV_FCLTY_DONTN_ID AS DONTN_ID1, U1.CHK_DGT, T.TST_RST_FRE_DESC, T.TST_PRFM_FCLTY_CD, Y.TST_PRFM_LAB_DESC, T.VRFY_TECH_ID, T.VRFY_DTTM, U1.VOL_NUM, U1.EXP_DTTM, U1.SEG_NUM,
U1.PROD_MANF_LOT_NUM AS MANF_LOT_NUM, U1.DONTN_ID, U1.PROD_ABO, U1.PROD_RH, R.ORD_INTRFC_NUM, R.ORD_NUM,
R.ORD_PHYSN_NUM, R.ORD_PHYSN_FRE_DESC, R.ORD_REQ_DTTM, R.ICD9_CD, R.ICD9_FRE_DESC, P1.INTNL_PF_MST_NUM AS INTNL_PF_NUM, U1.INTNL_UNIT_NUM, 0 AS INTNL_DRV_NUM, ICD.INTPRTN_CD_DESC, U1.DIVISION, U1.UNIT_STAT_CD,
T.TST_CMPLT_FCLTY_CD AS PRFM_FCLTY_CD, P1.INTNL_PTNT_ALS_NUM AS NAME_ALS_NUM, P1.INTNL_MED_REC_NUM AS MRN_ALS_NUM
FROM dbo.PTNT_RGSTRTN A INNER JOIN dbo.FCLTY_CD B ON A.REG_INTO_FCLTY_CD = B.FCLTY_CD
INNER JOIN dbo.PTNT_MED_REC_NUM C ON A.MED_REC_NUM = C.MED_REC_NUM
INNER JOIN dbo.PTNT_MST D1 ON A.INTNL_PTNT_NUM = D1.INTNL_PTNT_NUM
INNER JOIN dbo.TST_XMTCH T0 ON D1.INTNL_PTNT_NUM = T0.INTNL_PTNT_NUM
INNER JOIN dbo.TST_MST T ON T0.INTNL_TST_NUM = T.INTNL_TST_NUM
INNER JOIN dbo.TST_CD T1 ON T.TST_CD = T1.TST_CD
INNER JOIN dbo.PRI_CD T2 ON T.TST_PRI_CD = T2.PRI_CD
INNER JOIN dbo.PF_MST P1 ON D1.INTNL_PTNT_NUM = P1.INTNL_PTNT_NUM AND T0.INTNL_TST_NUM = P1.INTNL_TST_MST_NUM
INNER JOIN dbo.UNIT U1 ON P1.INTNL_UNIT_NUM = U1.INTNL_UNIT_NUM
INNER JOIN dbo.PROD_CD U ON U1.PROD_CD = U.PROD_CD AND B.FCLTY_CD = U.FCLTY_CD
INNER JOIN dbo.INTPRTN_CD ICD ON ICD.INTPRTN_CD = T.TST_RST_FRE_DESC
INNER JOIN dbo.ORD_MSG_MST R ON T.INTNL_ORD_MSG_NUM = R.INTNL_ORD_MSG_NUM
LEFT OUTER JOIN dbo.TST_PRFM_LAB_CD Y ON T.TST_PRFM_LAB_CD = Y.TST_PRFM_LAB_CD
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
V_RPT_TRANS2
------------
CREATE VIEW V_RPT_TRANS2 AS
SELECT A.INTNL_PTNT_NUM, A.REG_INTO_FCLTY_CD AS REG_FCLTY_CD, B.FCLTY_CD_DESC, A.MED_REC_NUM AS MRN_REG,
C.MED_REC_NUM AS MRN_PTNT, A.PTNT_LST_NAM AS LAST_NAME, A.PTNT_FST_NAM AS FIRST_NAME, A.PTNT_MID_NAM AS MID_NAME,
D1.UVS_PTNT_NUM, D1.BRTH_DT, D1.SSN, D1.SEX_CD, ISNULL(P1.INTNL_TST_MST_NUM, 0) AS INTNL_TST_NUM, '' AS TST_CD, ''
AS TST_CD_DESC, '' AS TST_PRI_CD, '' AS PRI_CD_DESC, U1.PROD_CD AS PROD_CD, U.PROD_CD_DESC AS PROD_CD_DESC,
U1.RCV_FCLTY_DONTN_ID AS DONTN_ID1, U1.CHK_DGT AS CHK_DGT, '' AS TST_RST_FRE_DESC, '' AS TST_PRFM_FCLTY_CD, ''
AS TST_PRFM_LAB_DESC, '' AS VRFY_TECH_ID, '' AS VRFY_DTTM, U1.VOL_NUM AS VOL_NUM, U1.EXP_DTTM AS EXP_DTTM,
U1.SEG_NUM AS SEG_NUM, U1.PROD_MANF_LOT_NUM AS MANF_LOT_NUM, U1.DONTN_ID AS DONTN_ID, U1.PROD_ABO AS PROD_ABO,
U1.PROD_RH AS PROD_RH, '' AS ORD_INTRFC_NUM, '' AS ORD_NUM, '' AS ORD_PHYSN_NUM, '' AS ORD_PHYSN_FRE_DESC, '' AS ORD_REQ_DTTM, '' AS ICD9_CD,'' AS ICD9_FRE_DESC, P1.INTNL_PF_MST_NUM AS INTNL_PF_NUM, U1.INTNL_UNIT_NUM AS INTNL_UNIT_NUM,0 AS INTNL_DRV_NUM, '' AS INTPRTN_CD_DESC, U1.DIVISION AS DIVISION, U1.UNIT_STAT_CD AS UNIT_STAT_CD, '' AS PRFM_FCLTY_CD, P1.INTNL_PTNT_ALS_NUM AS NAME_ALS_NUM, P1.INTNL_MED_REC_NUM AS MRN_ALS_NUM
FROM DBO.PTNT_RGSTRTN A INNER JOIN DBO.FCLTY_CD B ON A.REG_INTO_FCLTY_CD = B.FCLTY_CD
INNER JOIN DBO.PTNT_MED_REC_NUM C ON A.MED_REC_NUM = C.MED_REC_NUM
INNER JOIN DBO.PTNT_NAM D ON A.INTNL_PTNT_NUM = D .INTNL_PTNT_NUM
INNER JOIN DBO.PTNT_MST D1 ON A.INTNL_PTNT_NUM = D1.INTNL_PTNT_NUM
INNER JOIN DBO.PF_MST P1 ON D1.INTNL_PTNT_NUM = P1.INTNL_PTNT_NUM
INNER JOIN DBO.UNIT U1 ON P1.INTNL_UNIT_NUM = U1.INTNL_UNIT_NUM
INNER JOIN DBO.PROD_CD U ON U1.PROD_CD = U.PROD_CD
WHERE U.FCLTY_CD = B.FCLTY_CD AND U.PROD_CLR_TYP = 'N'
UNION
SELECT A.INTNL_PTNT_NUM, A.REG_INTO_FCLTY_CD AS REG_FCLTY_CD, B.FCLTY_CD_DESC, A.MED_REC_NUM AS MRN_REG,
C.MED_REC_NUM AS MRN_PTNT, A.PTNT_LST_NAM AS LAST_NAME, A.PTNT_FST_NAM AS FIRST_NAME, A.PTNT_MID_NAM AS MID_NAME,
D1.UVS_PTNT_NUM, D1.BRTH_DT, D1.SSN, D1.SEX_CD, ISNULL(P1.INTNL_TST_MST_NUM, 0) AS INTNL_TST_NUM, '' AS TST_CD, ''
AS TST_CD_DESC, '' AS TST_PRI_CD, '' AS PRI_CD_DESC, U1.PROD_CD AS PROD_CD, U.PROD_CD_DESC AS PROD_CD_DESC,
U1.RCV_FCLTY_DONTN_ID AS DONTN_ID1, U1.CHK_DGT AS CHK_DGT, '' AS TST_RST_FRE_DESC, '' AS TST_PRFM_FCLTY_CD, ''
AS TST_PRFM_LAB_DESC, '' AS VRFY_TECH_ID, '' AS VRFY_DTTM, U1.VOL_NUM AS VOL_NUM, U1.EXP_DTTM AS EXP_DTTM,
U1.SEG_NUM AS SEG_NUM, U1.PROD_MANF_LOT_NUM AS MANF_LOT_NUM, U1.DONTN_ID AS DONTN_ID, U1.PROD_ABO AS PROD_ABO,
U1.PROD_RH AS PROD_RH, R.ORD_INTRFC_NUM AS ORD_INTRFC_NUM, R.ORD_NUM AS ORD_NUM, R.ORD_PHYSN_NUM AS ORD_PHYSN_NUM,
R.ORD_PHYSN_FRE_DESC AS ORD_PHYSN_FRE_DESC, R.ORD_REQ_DTTM AS ORD_REQ_DTTM, R.ICD9_CD AS ICD9_CD,
R.ICD9_FRE_DESC AS ICD9_FRE_DESC, P1.INTNL_PF_MST_NUM AS INTNL_PF_NUM, U1.INTNL_UNIT_NUM AS INTNL_UNIT_NUM,
0 AS INTNL_DRV_NUM, '' AS INTPRTN_CD_DESC, U1.DIVISION AS DIVISION, U1.UNIT_STAT_CD AS UNIT_STAT_CD, '' AS PRFM_FCLTY_CD, P1.INTNL_PTNT_ALS_NUM AS NAME_ALS_NUM, P1.INTNL_MED_REC_NUM AS MRN_ALS_NUM
FROM DBO.PTNT_RGSTRTN A
INNER JOIN DBO.FCLTY_CD B ON A.REG_INTO_FCLTY_CD = B.FCLTY_CD
INNER JOIN DBO.PTNT_MED_REC_NUM C ON A.MED_REC_NUM = C.MED_REC_NUM
INNER JOIN DBO.PTNT_NAM D ON A.INTNL_PTNT_NUM = D .INTNL_PTNT_NUM
INNER JOIN DBO.PTNT_MST D1 ON A.INTNL_PTNT_NUM = D1.INTNL_PTNT_NUM
INNER JOIN DBO.PF_MST P1 ON D1.INTNL_PTNT_NUM = P1.INTNL_PTNT_NUM
INNER JOIN DBO.UNIT U1 ON P1.INTNL_UNIT_NUM = U1.INTNL_UNIT_NUM
INNER JOIN DBO.PROD_CD U ON U1.PROD_CD = U.PROD_CD
INNER JOIN DBO.ORD

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please don't cross-post

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=371682

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mister Sachmo SSCommitted Points: 1618 More actions · Answer 2

Do you have any indexes on the table? Do you really need to select all of those columns? IF you can create a unique index on the columns that you absolutely need, you can vastly improve your performance.