MERGE vs NOT EXISTS with linked server

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 2008 Forums

Transact-SQL (2008)

MERGE vs NOT EXISTS with linked server

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-13 : 04:54:11

Hi,

I need to transfer a few 100k rows from one linked server to another on a daily basis and I was wondering which would perform better; a MERGE or an INSERT INTO ... WHERE NOT EXISTS. There will be no updating, only inserting and unfortunately there is no test system and the linked server is too bogged down to do any live testing. Is there any difference at all...? Here's my pseudo syntax ->


MERGE localtable AS Target
USING (SELECT Col1, Col2 
	FROM myLinkedServer.dbname.dbo.remotetable
	WHERE Transferred IS NULL) AS Source (Col1, Col2)
		ON ...
WHEN NOT MATCHED THEN 
	INSERT (Col1, Col2)
	...
	
	
INSERT INTO localtable (Col1, Col2)
SELECT Col1, Col2
FROM myLinkedServer.dbname.dbo.remotetable a
WHERE NOT EXISTS (
	SELECT 1 
	FROM localtable b
	WHERE a.Col1 = b.Col1
		AND a.Col2 = b.Col2)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-13 : 06:49:03

do you have 2 different desktops? put the DBs on yours and a friends, then link them and give it a go

Or wait for Peter to see this and test it into oblivion.

I know that EXISTS is supposed to be very efficient, but have no idea on how well MERGE performs...

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-13 : 11:46:36

If you can avoid joining over linked servers I would. You might find that SQL will bring the entire table over to your local TEMPDB before doing the actual join. Not 100% sure about the NOT EXISTS. But, since it's only a few 100K rows I probably wouldn't sweat it. However, if the server(s) are under pressure, you might want to look at a different type of solution. Perhaps something like an SSIS ETL-type process would work better?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources