what is fastest way to get difrences from 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

what is fastest way to get difrences from 2 tables

Author

Topic

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-07 : 10:37:43

i have to tables for example work and work1

is the fastest way to get the diffrences to leftjoin on ID and then say where b.ID is null

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-07 : 10:40:40

I have found that using "not exists" works well.
[CODE]
SELECT a.*
FROM a
WHERE NOT EXISTS (SELECT b.* FROM b WHERE a.ID = b.ID)
[/CODE]

You should probably test the different ways though

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 10:40:52

IMHO yes.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 10:41:20

your point ditch.

Go with the flow & have fun! Else fight the flow

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-07 : 10:44:46

quote:
Originally posted by spirit1

your point ditch.

Is that a question or a statement?

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 10:52:34

statement. not meant as in "what do you mean"
i meant u sniped me

Go with the flow & have fun! Else fight the flow

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-07 : 10:59:18

Just Checking

Duane.

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-07 : 11:08:57

Basicly I have two tables one in SQL server 2000 and one in Access 2000 and i want to keep them syncronized. For example the work table has over 100K rows. My goal is upon new changes made to the access table to bring those changes to sql server 2000. I don't want to rewrite the underlining code in acces that actually puts the changes in the access table therefore i just want to compare the 2 tables on form exit event and take the changes and put them into sql server 2000. However for small tables this works fast, but for tables with over 100k rows it takes like 1 minute to run, because it has to bring the whole table from sql server over to the client and then join and compare and then insert into the sql server 2000.

I was thinking that maybe if i have them sorted by some uniqe identifier, i can get the max and then select from max to the end and insert them to sql server this way it will be faster. Anyone did anything like this that has any sugestions, or different aproaches

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-07 : 11:15:13

If the 2 tables are identical (in layout) then why don't you do something along these lines.

1.) copy table across to machine.
2.) drop existing table (on machine mentioned in 1)
3.) rename table (copied in 1) to the name of the table (dropped in 2)

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 11:17:48

well yes u could do that.
but that still requires joins or exists and since there is no order in the database as such i don't see any gain in this.

maybe it would be faster if u brought access table to sql server...

Go with the flow & have fun! Else fight the flow

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-07 : 11:21:52

it wouldn't requrie joins, becaue it gets the max id on the table that is allways smaller, then if i select where max id is bigger then (the max id that i just got) it would only return the diffrences and not have to bring over the whole 100K+ rows from sqlserver2000 over to access client, this would eliminate the network trafic. and the query would execute in a couple of sec instead of a min.... I think as i havent done it yet. I was hoping someone did something similar and they could help me with it.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-07 : 11:23:23

quote:

maybe it would be faster if u brought access table to sql server...

Well isn't that exactly what I said in detail?

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 11:23:31

aha, i get it... try it and tell us how it went. it could work...
Duane's idea isn't bad also.... why wouldn't that work for you?

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 11:24:23

quote:
Originally posted by ditch

quote:

maybe it would be faster if u brought access table to sql server...

Well isn't that exactly what I said in detail?

Duane.

yes it is

but i didn't see u yet. maybe ths time i need a typing course

Go with the flow & have fun! Else fight the flow

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-07 : 11:25:33

Oops sorry there spirit1 I thought the "well yes you could do that" was meant 4 me.

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-07 : 11:44:54

Not a seek in the bunch....



USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1)  PRIMARY KEY, Col2 char(1))
CREATE TABLE myTable00(Col1 int IDENTITY(1,10) PRIMARY KEY, Col2 char(1))
GO

DECLARE @x int
SELECT @x = 1

WHILE @x < 1000
  BEGIN
	INSERT INTO myTable99(Col2) SELECT 'x'
	INSERT INTO myTable00(Col2) SELECT 'x'
	SELECT @x = @x + 1
  END
GO

SELECT COUNT(*) FROM myTable99
SELECT COUNT(*) FROM myTable00

--Turn On SHOW PLAN [CTRL]+K...how do you do this with code?

SELECT Col1
  FROM myTable00 o
 WHERE NOT EXISTS (SELECT * 
		     FROM myTable99 i 
		    WHERE o.Col1 = i.Col1)

    SELECT l.Col1
      FROM myTable00 l
 LEFT JOIN myTable99 r 
	ON l.Col1 = r.Col1
     WHERE r.Col1 IS NULL


SELECT Col1
  FROM myTable00
 WHERE Col1 NOT IN (SELECT Col1 
		     FROM myTable99)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO

Brett

8-)

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-07 : 12:20:48

X002548

i am currently using this method

SELECT l.Col1
FROM myTable00 l
LEFT JOIN myTable99 r
ON l.Col1 = r.Col1
WHERE r.Col1 IS NULL

but the query runs on the Access client to check for diffrences, and in order to run it has to bring in the second table from sql server in order to make the select statement, therefore it has to bring over to the client the whole sqlserver table. THis takes network traffic and time.

I was thinking of a way to somehow mark the rows that need to be exported to sqlserver and therefore eliminate the coparison between the 2 tables. Or if i can't mark them with some kind of timestamp then maybe sort the tables and get the count from both tables. The diffrence in count would be the different rows. Now when i sort them if i select the # of rows, i would end up with the diffrence between the 2 tables? what do you think?

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-07 : 14:33:14

Hi X002548 what do you think is it possible to do what i described above? would it improve my time?

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-07 : 14:52:32

Why is the one table in access?

The network traffic is your problem....

How much data are we talking about....

Can place the data entirely in sql server?

Or in Access?

Brett

8-)

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-07 : 15:44:11

One of the tables has to stay in Access because the way the Application was developed, so my idea is to figure out the diffrences without having the sql server table be brought on the server..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources