SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 what is fastest way to get difrences from 2 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iancuct
Yak Posting Veteran

73 Posts

Posted - 09/07/2004 :  10:37:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/07/2004 :  10:40:40  Show Profile  Visit ditch's Homepage  Reply with Quote
I have found that using "not exists" works well.

SELECT a.*
FROM a
WHERE NOT EXISTS (SELECT b.* FROM b WHERE a.ID = b.ID)


You should probably test the different ways though


Duane.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 09/07/2004 :  10:40:52  Show Profile  Visit spirit1's Homepage  Reply with Quote
IMHO yes.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 09/07/2004 :  10:41:20  Show Profile  Visit spirit1's Homepage  Reply with Quote
your point ditch.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/07/2004 :  10:44:46  Show Profile  Visit ditch's Homepage  Reply with Quote
quote:
Originally posted by spirit1

your point ditch.




Is that a question or a statement?



Duane.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 09/07/2004 :  10:52:34  Show Profile  Visit spirit1's Homepage  Reply with Quote
statement. not meant as in "what do you mean"
i meant u sniped me


Go with the flow & have fun! Else fight the flow
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/07/2004 :  10:59:18  Show Profile  Visit ditch's Homepage  Reply with Quote
Just Checking


Duane.
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts

Posted - 09/07/2004 :  11:08:57  Show Profile  Reply with Quote
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
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/07/2004 :  11:15:13  Show Profile  Visit ditch's Homepage  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 09/07/2004 :  11:17:48  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts

Posted - 09/07/2004 :  11:21:52  Show Profile  Reply with Quote
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.
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/07/2004 :  11:23:23  Show Profile  Visit ditch's Homepage  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 09/07/2004 :  11:23:31  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 09/07/2004 :  11:24:23  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/07/2004 :  11:25:33  Show Profile  Visit ditch's Homepage  Reply with Quote
Oops sorry there spirit1 I thought the "well yes you could do that" was meant 4 me.



Duane.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/07/2004 :  11:44:54  Show Profile  Reply with Quote
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-)
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts

Posted - 09/07/2004 :  12:20:48  Show Profile  Reply with Quote
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?
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts

Posted - 09/07/2004 :  14:33:14  Show Profile  Reply with Quote
Hi X002548 what do you think is it possible to do what i described above? would it improve my time?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/07/2004 :  14:52:32  Show Profile  Reply with Quote
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-)
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts

Posted - 09/07/2004 :  15:44:11  Show Profile  Reply with Quote
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..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000