Multi-Table Join to Single Destination

Question

I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL.

To start, I also have to include that I have two auxiliary master data tables that help me join with the eventual destination table. They have the following schema. Their relation to the problem will be further explained down the post.

MD_TABLE_1

KEY_1|LVL1
001   100 
002   100
…

MD_TABLE_2

KEY_1|LVL1
ABC   XYZ
BCD   XYZ
…

I have 4 Tables (for brevity I will only use 2) that act as a source and should be placed into one. All four tables have a similar schema:

SOURCE_1

KEY_1|KEY_2|VALUE
001    XYZ  1.05


SOURCE_2

KEY_1|KEY_2|VALUE
100    XYZ  1.33

My Destination table has an identical schema that should hold all unique permutation at KEY level in an IF ELSE IF ELSE logic:

DEST

KEY_1|KEY_2|VALUE
001    ABC  1.05
001    BCD  1.05
002    ABC  1.33
002    BCD  1.33

That is:

IF EXISTS in SOURCE_1
ELSE IF EXISTS in SOURCE_2
ELSE SOURCE_N

I’ve tried the following two ways to slice this problem, each with its own downfall:

INSERT INTO DEST
SELECT DISTINCT
   KEY_1,
   KEY_2,
   VALUE
FROM (
   SELECT 
      MD1.KEY_1 as KEY_1,
      MD2.KEY_1 as KEY_2,
      S1.VALUE 
   FROM SOURCE_1 S1, MD_TABLE_1 MD1, MD_TABLE_2 MD2
   WHERE S1.KEY_1 = MD1.KEY_1 –- I know not needed but for full explaination
   AND S1.KEY_2 = MD2.LVL1
   UNION
   SELECT 
      MD1.KEY_1 as KEY_1,
      MD2.KEY_1 as KEY_2,
      S2.VALUE 
   FROM SOURCE_2 S2, MD_TABLE_1 MD1, MD_TABLE_2 MD2
   WHERE S2.KEY_1 = MD1.LVL1 – I know not needed but for full explanation
   AND S2.KEY_2 = MD2.LVL1
   );

Downfall of the above query is obvious. It returns a distinct of everything including the return of the value which isn’t correct. Desired behavior (this is not a complaint of distinct) is that I could call DISTINCT(KEY_1, KEY_2). My second approach was “better” but caused HUGE performance issues once above 50k rows in the destination table:

INSERT INTO DEST
SELECT MD1.KEY_1 as KEY_1,
       MD2.KEY_1 as KEY_2,
       S1.VALUE 
FROM SOURCE_1 S1, MD_TABLE_1 MD1, MD_TABLE_2 MD2
WHERE S1.KEY_1 = MD1.KEY_1 
AND S1.KEY_2 = MD2.LVL1

GO

INSERT INTO DEST
SELECT MD1.KEY_1 as KEY_1,
       MD2.KEY_1 as KEY_2,
       S2.VALUE 
FROM SOURCE_1 S2, MD_TABLE_1 MD1, MD_TABLE_2 MD2
WHERE S2.KEY_1 = MD1.KEY_1 
AND S2.KEY_2 = MD2.LVL1
AND NOT EXISTS (SELECT 1
            FROM DEST D 
            WHERE D.KEY_1 = MD1.KEY_1
            AND D.KEY2    = MD2.KEY_1);

First query operates in a matter of seconds (7 MM rows), but the second runs > 2 hours . (about 9 MM new permutations)

I feel like I’m missing something right In front of me with this one since it’s a fairly simple operation of determining unique combinations. Perhaps it’s a factor of the data size but I cannot seem to find a performing solution to collect unique combinations of data.