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
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.