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

Compare 2 datasets and output delta in one TSQL statement Expand / Collapse
Author
Message
Posted Thursday, November 3, 2011 9:02 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 31, Visits: 478
Comments posted to this topic are about the item Compare 2 datasets and output delta in one TSQL statement
Post #1200330
Posted Friday, November 4, 2011 5:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:20 AM
Points: 41, Visits: 50
Where's the code?
Post #1200482
Posted Friday, November 4, 2011 5:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 19, Visits: 619
Any idea on how well this scales, or its performance versus using a full join between the two tables with a WHERE clause where either side is NULL?
Post #1200491
Posted Friday, November 4, 2011 5:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:20 AM
Points: 41, Visits: 50
OK, now the code is showing. Thanks.
Post #1200495
Posted Sunday, November 6, 2011 8:52 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 31, Visits: 478
Hi,

As with any unbalanced compare, the performance varies.

Just compare this method with use your favorite compare and look closely at the execution plan

In my query close to 50% of the time is spent reading the 2 source tables using a clustered index scan, which is the most efficient way to read the ENTIRE table content.

The Merge join that does the actual compare work is only 19 % of the time

The rest: 33%, is spent storing the result.

If you find a more efficient way, i'd be happy to review your code!

Theo :)



Post #1201148
Posted Sunday, November 27, 2011 9:34 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:34 PM
Points: 790, Visits: 646
--Compareandinsertdeltaintotable,inonesqlstatement
Insert into #cmpAB
([EmpNr_a],[EmpNr_b],
[Name_a],[Name_b],
[HireDT_a],[HireDT_b],
[FireDT_a],[FireDT_b]
)

SELECT a.[EmpNr],b.[EmpNr],a.[Name] , b.[Name],
a.[HireDT] , b.[HireDT] , a.[FireDT] , b.[FireDT]
FROM #tblA a INNER JOIN #tblB b
ON a.[EmpNr] = b.[EmpNr]
WHERE( a.[Name] <> b.[Name] OR a.[HireDT] <> b.[HireDT] OR a.[FireDT] <> b.[FireDT])

UNION
SELECT a.[EmpNr],b.[EmpNr],a.[Name] , b.[Name],
a.[HireDT] , b.[HireDT] , a.[FireDT] , b.[FireDT]
FROM #tblA a FULL OUTER JOIN #tblB b
ON a.[EmpNr] = b.[EmpNr]
where (a.[EmpNr] IS NULL OR b.[EmpNr] IS NULL)

--Thanks
Mitesh Oswal



Regards,
Mitesh OSwal
+918698619998
Post #1212265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse