June 6, 2007 at 2:21 pm
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
June 7, 2007 at 5:43 am
Please don't cross-post
http://qa.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
June 8, 2007 at 7:24 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply