Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replacing UNION in TSQL 2012 Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 9:20 AM
Points: 8, Visits: 31
Gents,
I have been looking for the most optimal way to replace UNIONs all over the internet and I can't seem to find anything relating to my situation.

I have a statement with about 3 unions. A couple of the unions select from the same table. I want to replace the union so that I can select those tables only once thereby reducing the reads and maybe even improve the time it takes.

Are there any new inbuilt functions in 2012 that can help?

Here is the ddl to make my request make more sense.

I have 5 tables

TeamPlayer
Team
Player
Statistics
League


--drop table #teamplayer
create table #teamplayer (teamplayerid int, teamid int, playerid int)
insert into #teamplayer
select 1, 1, 1 union all
select 2, 2, 2 union all
select 3, 3, 3 union all
select 4, 4, 4 union all
select 5, 5, 5 union all
select null, null, 6 union all
select null, null, 7

--drop table #player
create table #player (playerid int, playername varchar(100))
insert into #player
select 1, 'Lebron' union all
select 2, 'Kobe' union all
select 3, 'Paul' union all
select 4, 'Durant' union all
select 5, 'Carmelo' union all
select 6, 'Barkley' union all
select 7, 'Shaq'


--drop table #team
create table #team (teamid int, teamname varchar(100))
insert into #team
select 1, 'miami' union all
select 2, 'lakers' union all
select 3, 'clippers' union all
select 4, 'okc' union all
select 5, 'knicks'

--drop table #league
create table #league (leagueid int, teamid int, leaguename varchar(100))
insert into #league
select 10, 1, 'southwest' union all
select 20, 2, 'northwest' union all
select 30, 3, 'southeast' union all
select 40, 4, 'northeast' union all
select 50, 5, 'north'

--drop table #statisticscount
create table #statisticscount (statsid int, playerid int, ppg int, apg int, rpg int)
insert into #statisticscount
select 1, 1, 29, 7, 7 union all
select 2, 2, 35, 5, 4 union all
select 3, 3, 18, 11, 3 union all
select 4, 4, 32, 5, 5 union all
select 5, 5, 27, 4, 6 union all
select 6, 6, 24, 13, 3 union all
select 7, 7, 26, 2, 11

Here is the union statement

Select TP.PlayerID, P.PlayerName
From #teamplayer TP
JOIN #team T on TP.TeamID = T.TeamID
JOIN #player P on TP.PlayerID = P.PlayerID

UNION

Select NULL as PlayerID, 'FreeAgent' as PlayerName
From #teamplayer TP
where TeamPlayerID is NULL

UNION

SELECT L.LeagueID as PlayerID, L.LeagueName as PlayerName
FROM #league l


For the first 2 unions, I hit the same table (teamplayer) twice. How can I rewrite this to hit the table once and hereby eliminating the UNION ?

I welcome all approaches. i will test them all and let the community know about the fastest approach. Thanks
Post #1564053
Posted Tuesday, April 22, 2014 4:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 1,786, Visits: 5,681
Firstly, let me say that there are plenty of excellent women that use this site, so you may want to rethink the use of "Gents" as you are excluding half the population...

Select TP.PlayerID, ISNULL(P.PlayerName,'Free Agent') as PlayerName
From #teamplayer TP
-- JOIN #team T on TP.TeamID = T.TeamID
LEFT OUTER JOIN #player P on TP.PlayerID = P.PlayerID

I removed the join to #team as you were not SELECTing from it or using it in the JOIN to #player, but if it was there as a "filter" to only allow valid teams, then by all means add it back in by removing the comment marks.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1564054
    Posted Tuesday, April 22, 2014 4:37 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 11:33 PM
    Points: 7,042, Visits: 12,970
    Here's an approach using the OUTER APPLY operator available since SQL 2008 (so, it's nothing new in 2012 ...).
    Please note that I changed UNION to UNION ALL for the query using #league since I wouldn't expect any duplicates. But if there are dups, please let us know how you'd differentiate where those are coming from (either the #league table or the #teamplayer table).

    SELECT DISTINCT
    CASE WHEN TP.TeamPlayerID IS NULL THEN NULL ELSE TP.PlayerID END as PlayerID,
    CASE WHEN TP.TeamPlayerID IS NULL THEN 'FreeAgent' ELSE x.PlayerName END as PlayerName
    From #teamplayer TP
    OUTER APPLY
    (
    SELECT P.PlayerName
    FROM #team T
    JOIN #player P on TP.PlayerID = P.PlayerID AND TP.TeamID = T.TeamID
    )x

    UNION ALL

    SELECT L.LeagueID as PlayerID, L.LeagueName as PlayerName
    FROM #league l





    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    Post #1564057
    Posted Tuesday, April 22, 2014 6:56 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Friday, April 25, 2014 9:20 AM
    Points: 8, Visits: 31
    My apologies for the 'gents' salutation. What was I thinking.

    Thanks for the response. I just created something to mimic what I have currently. But yes I do need all tables and the league table can have duplicates hence the union instead of Union all. I also noticed you still had the union even after the outer apply replacement.

    In the real scenario, all the unions share at least 3 tables in common. Is there any way to avoid the unions completely?
    Post #1564069
    Posted Wednesday, April 23, 2014 2:33 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 11:33 PM
    Points: 7,042, Visits: 12,970
    Did you try the solution I provided?
    If the league table can have duplicates, keep the UNION ALL and change the league query to
    SELECT L.LeagueID as PlayerID, L.LeagueName as PlayerName
    FROM #league l
    GROUP BY L.LeagueID,L.LeagueName

    Therewith the duplicates are eliminated before union with the other result set.

    There is nothing wrong with a UNION ALL statement. A plain UNION will force a DISTINCT sort against the full result set leading to possible performance issues. Therefore, remove duplicates as early as possible in the process.
    If you simply "hate" the UNION operator, just create a staging table, insert the values returned by each single statement and run your query against that staging table. Voila! No UNION operator required!

    In the real scenario, all the unions share at least 3 tables in common.

    If the sample scenario you provided does not represent your current scenario, how would you expect our solutions to help you?
    If the query using #league would use other tables already used in the query, my solution would be different, too. But the sample data are presented as is and so is my solution.

    Keep in mind we can't look over your shoulder. All we have is what you've posted. If the original query is different, the solution most probably will be different, too.




    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    Post #1564145
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse