how to improve performance of the query with unions

  • uday-417581

    SSC Rookie

    Points: 29

    Hi,

    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

    IN

  • Sergiy

    SSC Guru

    Points: 109884

    I want to the screen where you gonna display that number of values simultaneously.

  • uday-417581

    SSC Rookie

    Points: 29

    This is basically for generating a report.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    You have both union and distinct. With union, distinct is redundent, since union removes duplicates anyway.

    Do the views retrun duplicate data? If not, you can remove the distincts

    Is there any chance of data overlap between the views? Will a row that appears in view 1 also appear in view 2? If not, change the union to union all. 

    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
  • uday-417581

    SSC Rookie

    Points: 29

    Firstly, cannot eliminate distinct as the query returns duplicate records.

    Secondly, there is overlap of data, so have to use union and not union all.

    By eliminating distinct would there be any performance improvement?

    I could squeeze the 2nd view by eliminating the union and combining in into a single query. But I gained only an improvement of 1sec.

    Can you suggest me what are the other ways of improving the query performance.

    I am pretty new to SQL, any suggestions would be helpful.

    Thanks,

    Uday

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Best this is to run the query in management studio withn the execution plan enabled and look at where the high costs are. That should give you some indication where the problem is.

    In my experience, duplicate data indicates either bad data or an incorrect query

    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
  • uday-417581

    SSC Rookie

    Points: 29

    Thank you Gail. Would check out and work on it.

  • Scott Coleman

    One Orange Chip

    Points: 27446

    I see a lot of the same joins at the beginning and end of each view definition.  Could you isolate the different parts of each of the three views, union the results, and then do the common joins only once?

  • TheSQLGuru

    SSC Guru

    Points: 134017

    My gut feel is that this is going to be a poor performer no matter what you do. 

    1) Too many joins to be efficient

    2) Use of both distinct and union - each require tempdb and sorting --> inefficient.

    3) No where clause --> all data

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Gail Shaw

    SSC Guru

    Points: 1004484

    2) Use of both distinct and union - each require tempdb and sorting --> inefficient.

    the optimiser is smart enough not to do both. In cases I've seen like this there's a single sort/distinct sort done at the end of the query. Point still stands though, it is slow and inefficient

    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
  • Irfan Baig-389253

    Valued Member

    Points: 50

    Hi,

    I have done little overview of your query code and following are some suggestions for query improvment and gaining the performance:

    1.   Please should reduce the number of joins because (maximum 3 or 4 number of joins are recommended) solution to reduce the number of joins create two or three variable temporary tables and dump your required data in relevant temp table using derived tables.

    2.   Create a Indexed Views instead of simple views, please note indexed views should only be created on your refined quries as mentioned in point 1

    3.   Create proper indexes on all the columns thats being used in WHERE calause and table JOINS.

    Thanks & Best Regard,

    Irfan Baig.

  • AndrewMurphy

    SSCertifiable

    Points: 5566

    "(maximum 3 or 4 number of joins are recommended)"....recommended by whom?  Please support by evidence.

    SQL is powerful enough not to have a problem with joins involving far more tables than this.  Proper indexing and avoiding data conversions on joins are key.

    Please post SQL execution plan to show us what's happening behind the queries.  What you have may be as good as it gets.  Also what are the individual table sizes (records, mb, etc).

     

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Recommended by lots of sources in my experience and readings, although the number of joins as a target max varies from 4-8.  I don't think it is a matter of sql server or the hardware can't handle it.  It is a matter of efficiency - mainly from a design perspective.  Most texts or class material I have read or taught from dealing with database design recommend considering denormalization or architecture changes if you get much deeper than 4-8 levels of joining to get an answer to a query.

    One could say that this is old-school since servers are so much more powerful these days.  To which others could reply that data volumes are growing at an equal or greater pace.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Irfan Baig-389253

    Valued Member

    Points: 50

    The join philosophy and optimizer work for choosing the best execution plan:

    How many ways are there of joining two tables: A and B? There are in fact two ways: AB and

    BA. What about three tables? There are six ways: ABC, ACB, BAC, BCA, CAB, and CBA.

    What about four tables? The answer is four, or 1 * 2 * 3 * 4 = 24.

    The number of ways, then, to join X tables is X!, or factorial X. If a query were to join 16

    tables, we are talking about 20,922,789,888,000 possible ways of performing this join. A join

    of ten tables would have 3,628,800 possible combinations, and SQL Server 2000 can join a

    maximum of 256 tables in a SELECT statement!

    Luckily, the query optimizer uses techniques internally to minimize the number of possible

    combinations, but the fact still remains that the more tables in a join the longer the query

    optimizer will take to work out the most efficient access strategy. Also, any inefficiency will

    be magnified enormously, since we are basically placing loops within loops within loops

    within a nested loops join.

     

    If you are not happy with your query performance, then break the join down into parts, perhaps joining a subset of the

    tables into a temporary table and then joining that with the remaining tables.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Irfan, you make a good point about the optimization cost/time which I don't think has been mentioned as of yet.

    However, the "loops . loops . loops" statement is a bit off.  SQL 6.5 was constrained to having only nested loop joins (which made it a true dog for larger systems at the time).  But since SQL 7 the optimizer has also been able to do both merge and hash joins, which work much better for larger sorted/unsorted datasets.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 16 total)

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