Alternate way of using Union ALL

  • Hi all,

    I have a script which contains lots of union all statements like this:

    SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID, IR.VSFORMNM as SVFORMNM, IR.VSUNS as SVUNS, IR.VSSEQ as SVSEQ, IR.VSNAME as VISIT, IR.VSNUM as VISITNUM, IR.SUBJID,s.STATUS, IR.DMSCRNUM, IR.DMRNDNUM, case when s.STATUS ='Screen Failed' then 'SF-' + S.DMSCRNUM else DMCOHRT + '-' + S.DMSCRNUM end as USUBJID

    ,IRYN,IRDOSE,IRASTYP

    ,'' as IRCDAT

    ,IRSTDY1 as IRSTDY, '' as IRCATTPT

    ,'' as IRPERF

    ,IRADAT

    ,IRTEMP1 as IRTEMP

    ,IRSNONE1 as IRSNONE

    ,'ITCHING AT SITE' as IRSCAT

    ,IRSORRES6 as IRSORRES

    ,IRSINTF6 as IRSINTF

    ,IRSPRVT6 as IRSPRVT

    ,IRSMED6 as IRSMED

    ,IRSGRADE6 as IRSGRADE

    ,'' as IRRMEAS

    ,'' as IRUNVYN

    ,'' as IRUNVAYN

    ,'' as IRMAXMEA

    ,'' as IRMAXWCH

    ,IRPGRADE

    from IR left JOIN

    dbo.DM2 AS S ON S.SUBJID = dbo.IR.SUBJID

    where VSFORMNM not like 'Injection Site Reaction Phone Report Log Dose%'and ir.VSFORMID in (select id from [10.100.100.20].[CA5569F3-21B3-4731-95BF-9D715E348554].[dbo].visits where deleted =0)

    UNION ALL

    SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID, IR.VSFORMNM as SVFORMNM, IR.VSUNS as SVUNS, IR.VSSEQ as SVSEQ, IR.VSNAME as VISIT, IR.VSNUM as VISITNUM, IR.SUBJID,s.STATUS, IR.DMSCRNUM, IR.DMRNDNUM, case when s.STATUS ='Screen Failed' then 'SF-' + S.DMSCRNUM else DMCOHRT + '-' + S.DMSCRNUM end as USUBJID

    ,IRYN,IRDOSE,IRASTYP

    ,'' as IRCDAT

    ,IRSTDY1 as IRSTDY, '' as IRCATTPT

    ,'' as IRPERF

    ,IRADAT

    ,IRTEMP1 as IRTEMP

    ,IRSNONE2 as IRSNONE

    ,'ERYTHEMA' as IRSCAT

    ,IRSORRES7 as IRSORRES

    ,'' as IRSINTF

    ,'' as IRSPRVT

    ,'' as IRSMED

    ,IRSGRADE7 as IRSGRADE

    ,IRRMEAS7 as IRRMEAS

    ,'' as IRUNVYN

    ,'' as IRUNVAYN

    ,'' as IRMAXMEA

    ,'' as IRMAXWCH

    ,IRPGRADE

    from IR left JOIN

    dbo.DM2 AS S ON S.SUBJID = dbo.IR.SUBJID

    where VSFORMNM not like 'Injection Site Reaction Phone Report Log Dose%'and ir.VSFORMID in (select id from [10.100.100.20].[CA5569F3-21B3-4731-95BF-9D715E348554].[dbo].visits where deleted =0)

    UNION ALL

    SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID, IR.VSFORMNM as SVFORMNM, IR.VSUNS as SVUNS, IR.VSSEQ as SVSEQ, IR.VSNAME as VISIT, IR.VSNUM as VISITNUM, IR.SUBJID,s.STATUS, IR.DMSCRNUM, IR.DMRNDNUM, case when s.STATUS ='Screen Failed' then 'SF-' + S.DMSCRNUM else DMCOHRT + '-' + S.DMSCRNUM end as USUBJID

    ,IRYN,IRDOSE,IRASTYP

    ,'' as IRCDAT

    ,IRSTDY1 as IRSTDY, '' as IRCATTPT

    ,'' as IRPERF

    ,IRADAT

    ,IRTEMP1 as IRTEMP

    ,IRSNONE2 as IRSNONE

    ,'DESQUAMATION' as IRSCAT

    ,IRSORRES8 as IRSORRES

    ,'' as IRSINTF

    ,'' as IRSPRVT

    ,'' as IRSMED

    ,IRSGRADE8 as IRSGRADE

    ,IRRMEAS8 as IRRMEAS

    ,'' as IRUNVYN

    ,'' as IRUNVAYN

    ,'' as IRMAXMEA

    ,'' as IRMAXWCH

    ,IRPGRADE

    from IR left JOIN dbo.DM2 AS S ON S.SUBJID = dbo.IR.SUBJID

    where VSFORMNM not like 'Injection Site Reaction Phone Report Log Dose%'and ir.VSFORMID in (select id from [10.100.100.20].[CA5569F3-21B3-4731-95BF-9D715E348554].[dbo].visits where deleted =0)

    Is there any alternate way of inspite of using USING ALL so that performance can be improved..

    Thanks in advance

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Try to do the join after the union like this:

    select ...

    from (select ...

    union all

    select ...) as unioned

    join jointable

    on unioned.col = jointable.col

    It may be better (if most of the rows are joined) or worse (if only a few rows are joined).

    Also, try to add the constant data outside of the union all/join logic, if possible.

    finally, ensure that the join and where predicates are on indexed columns and add appropriate indexes if not

    Gerald Britton, Pluralsight courses

  • Am I missing something, or aren't all those unions to the same source tables with the same joins and where clauses? The only difference I can see is in the hard-coded values so, in effect, you are simply creating three records from each source record. If thats the case, why not create a temp table with just those three hardcoded values in it and then join it to the first query without the unions?

  • andyscott (3/12/2015)


    Am I missing something, or aren't all those unions to the same source tables with the same joins and where clauses? The only difference I can see is in the hard-coded values so, in effect, you are simply creating three records from each source record. If thats the case, why not create a temp table with just those three hardcoded values in it and then join it to the first query without the unions?

    Excellent idea. But I think you missed that the columns in the select list are different in each query.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This thing screams of denormalized data when I see columns like IRSORRES6, IRSORRES7, IRSORRES8. If you can normalize the data you could eliminate the need to have 3 queries here instead of 1. If you are stuck with the structures (which is highly likely) there isn't a lot you do. If performance is the issue we need to see the table structures, indexes and actual execution plans.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (3/12/2015)


    This thing screams of denormalized data when I see columns like IRSORRES6, IRSORRES7, IRSORRES8. If you can normalize the data you could eliminate the need to have 3 queries here instead of 1. If you are stuck with the structures (which is highly likely) there isn't a lot you do. If performance is the issue we need to see the table structures, indexes and actual execution plans.

    He also has hard coded columns in the select with different values in them, I guess there's some business reason for that....

  • ZZartin (3/12/2015)


    Sean Lange (3/12/2015)


    This thing screams of denormalized data when I see columns like IRSORRES6, IRSORRES7, IRSORRES8. If you can normalize the data you could eliminate the need to have 3 queries here instead of 1. If you are stuck with the structures (which is highly likely) there isn't a lot you do. If performance is the issue we need to see the table structures, indexes and actual execution plans.

    He also has hard coded columns in the select with different values in them, I guess there's some business reason for that....

    Yeah I saw that too. Probably something to do with each group has some hard coded value. Yet another argument for normalization. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Assuming normalising the tables isn't an option for you, how about something like this:

    WITH cte AS (SELECT * FROM (VALUES (1),(2),(3)) AS temp (no))

    SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID, IR.VSFORMNM as SVFORMNM, IR.VSUNS as SVUNS, IR.VSSEQ as SVSEQ, IR.VSNAME as VISIT, IR.VSNUM as VISITNUM, IR.SUBJID,s.STATUS, IR.DMSCRNUM, IR.DMRNDNUM, case when s.STATUS ='Screen Failed' then 'SF-' + S.DMSCRNUM else DMCOHRT + '-' + S.DMSCRNUM end as USUBJID

    ,IRYN,IRDOSE,IRASTYP

    ,'' as IRCDAT

    ,IRSTDY1 as IRSTDY, '' as IRCATTPT

    ,'' as IRPERF

    ,IRADAT

    ,IRTEMP1 as IRTEMP

    ,IRSNONE1 as IRSNONE

    ,CASE WHEN No=1 THEN 'ITCHING AT SITE'

    WHEN No=2 THEN 'ERYTHEMA'

    WHEN No=3 THEN 'DESQUAMATION'

    END AS IRSCAT

    ,CASE WHEN No=1 THEN IRSORRES6

    WHEN No=2 THEN IRSORRES7

    WHEN No=3 THEN IRSORRES8

    END AS IRSORRES

    ,IRSINTF6 as IRSINTF

    ,IRSPRVT6 as IRSPRVT

    ,IRSMED6 as IRSMED

    ,IRSGRADE6 as IRSGRADE

    ,'' as IRRMEAS

    ,'' as IRUNVYN

    ,'' as IRUNVAYN

    ,'' as IRMAXMEA

    ,'' as IRMAXWCH

    ,IRPGRADE

    FROM IR,cte

    LEFT JOIN dbo.DM2 AS S ON S.SUBJID = dbo.IR.SUBJID

    WHERE VSFORMNM not like 'Injection Site Reaction Phone Report Log Dose%'

    AND ir.VSFORMID in (select id from [10.100.100.20].[CA5569F3-21B3-4731-95BF-9D715E348554].[dbo].visits where deleted =0)

    with the same sort of CASE/WHEN as shown for IRSCAT and IRSORRES applied to the remaining columns.

    NB I can't test this 'cos I don't have your tables but it appears to work in a harness!

  • ZZartin (3/12/2015)


    Sean Lange (3/12/2015)


    This thing screams of denormalized data when I see columns like IRSORRES6, IRSORRES7, IRSORRES8. If you can normalize the data you could eliminate the need to have 3 queries here instead of 1. If you are stuck with the structures (which is highly likely) there isn't a lot you do. If performance is the issue we need to see the table structures, indexes and actual execution plans.

    He also has hard coded columns in the select with different values in them, I guess there's some business reason for that....

    But couldn't those also be added to the temp table? This does look a might messed up.

    Also, the UNION ALL is probably not the source of slow performance. It's probably that WHERE clause. Have you looked at the execution plans to understand how the indexes are being used?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 9 posts - 1 through 9 (of 9 total)

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