XOR two tables 1

[graabein]

Hi,

I have a "before and after" problem where I have two tables with id_keys and have to detect if there's been any changes.

I've built an internal temp table of the before list, passed as a string array to the stored procedure, like this create table #prev (id_key int).

I then want to do a select id_key from ... query to get the after state and do an XOR with #prev in the where clause.

In other words I want to return a list of the id_keys either in #prev or query but not in both.

Any suggestions? It should be pretty basic but I've been unable to google it...


graabein

 

[ca8msm]

You could use a union all query e.g

[COLOR=blue]declare[/color] @table1 [COLOR=blue]table[/color] (id [COLOR=blue]int[/color])
[COLOR=blue]declare[/color] @table2 [COLOR=blue]table[/color] (id [COLOR=blue]int[/color])

[COLOR=blue]insert[/color] @table1 [COLOR=blue]values[/color] (1)
[COLOR=blue]insert[/color] @table1 [COLOR=blue]values[/color] (2)
[COLOR=blue]insert[/color] @table1 [COLOR=blue]values[/color] (3)

[COLOR=blue]insert[/color] @table2 [COLOR=blue]values[/color] (1)
[COLOR=blue]insert[/color] @table2 [COLOR=blue]values[/color] (4)
[COLOR=blue]insert[/color] @table2 [COLOR=blue]values[/color] (5)

[COLOR=blue]select[/color] t1.id 
[COLOR=blue]from[/color] @table1 t1
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] @table2 t2 [COLOR=blue]on[/color] t1.id = t2.id
[COLOR=blue]where[/color] t2.id [COLOR=blue]is[/color] null
union all
[COLOR=blue]select[/color] t2.id 
[COLOR=blue]from[/color] @table2 t2
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] @table1 t1 [COLOR=blue]on[/color] t2.id = t1.id
[COLOR=blue]where[/color] t1.id [COLOR=blue]is[/color] null

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[graabein]

Thank you, that works.


graabein

 

[ESquared]

select
   coalesce(t1.id, t2.id)
from
   @table1 t1
   full join @table2 t2 on t1.id = t2.id
where
   t1.id is null
   or t2.id is null

This will have about half the reads of the union all method.

 

[ca8msm]

This will have about half the reads of the union all method.

How do you measure the reads (and are you referring to IO reads)? I did a test by using SET STATISTICS IO ON and there wasn't anything in it (although I didn't test how much this would change if there were a lot more records):

-- Test 1
select t1.id
from @table1 t1
left join @table2 t2 on t1.id = t2.id
where t2.id is null
union all
select t2.id
from @table2 t2
left join @table1 t1 on t2.id = t1.id
where t1.id is null

-- Test 2
select
   coalesce(t1.id, t2.id)
from
   @table1 t1
   full join @table2 t2 on t1.id = t2.id
where
   t1.id is null
   or t2.id is null

(4 row(s) affected)
Table '#44FF419A'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#45F365D3'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


(4 row(s) affected)
Table '#44FF419A'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#45F365D3'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Or is there another measurement you were referring to?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ESquared]

I did the same thing in Query Analyzer with execution plan turned on. The graphical execution plan returned these results. Then I ran them again with execution plan turned off and query profiler on.

Query 7: Query cost (relative to the batch): 43.86%
subtree cost: 0.151
• 2 table scans (25% each), nested loops/left outer join (0%) -> filter (0%)
• 2 table scans (25% each), nested loops/left outer join (0%) -> filter (0%)
• finally, concatenation (0%) of these two streams.
Query profiler: Reads: 48


Query 8: Query cost (relative to the batch): 26.99%
subtree cost: 0.0930
• 2 table scans (40% each), hash match/full outer join (19%) -> filter (0%) -> compute scalar (0%)
Query Profiler: Reads: 12

This is a fairly intuitively obvious result.

Doing two queries against two tables each is reading four times from separate tables.

Doing one query against two tables is reading two times from separate tables.

 

[ca8msm]

OK, thanks.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]