March 2, 2011 at 3:23 pm
I used SP below to compare two tables in Remote and Local SQL server(linked server) and select the deference.
This is a daily job. There are about 300,000 records in Remote. Most time about 1000 deference records will insert into Local table.
How to improve the code? (ID in both tables is primary key)
ALTER PROCEDURE spCompare
AS
BEGIN
SET nocount ON;
BEGIN
CREATE TABLE #a
(
id INT
CONSTRAINT pk_id PRIMARY KEY (id)
)
INSERT INTO #a
(id)
SELECT
id
FROM
[remote].[order_remote].dbo.order a
WHERE NOT EXISTS(SELECT
id
FROM
order_local.dbo.order b
WHERE b.id = a.id)
END
SELECT
x.*
FROM
[remote].[order_remote].dbo.order x
INNER JOIN #a
ON x.id = #a.id
END
March 2, 2011 at 3:33 pm
When you run a query like that, what happens is SQL will pull the entire table back over the link and then run it locally (possibly twice in this case). Very inefficient
You're better off creating either a view or a SP on the remote server that calculates the difference, and then just query that SP/View from your local box. This is how I've handled it previously for things like comparing multi-million row tables..reducing run time from 1-2 hours to a minute or so.
March 2, 2011 at 3:36 pm
You could try adding a column to the end of the remote table that has a default value of GETDATE so as records get added to the remote table, they have a timestamp. Then when you run your proc, you can just INSERT into your local table where the InsertDate = Today's Date (formatted to omit the hh:mm:ss of course)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2011 at 3:44 pm
I've also had good experiences using EXCEPT, as long as you've got some decent indexes on the table you're hitting.
INSERT INTO Local.Table
SELECT col1, col2... FROM Remote.Table
EXCEPT
SELECT col1, col2... FROM Local.Table
This actually works quite well
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2011 at 3:59 pm
Some thoughts:
1. Don't use Hungarian naming for your procs, i.e. don't prefix with sp.
Try this code:
ALTER PROCEDURE dbo.compare_orders
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @ids VARCHAR(MAX) = '' ;
DECLARE @sql NVARCHAR(MAX) ;
IF OBJECT_ID(N'tempdb..#remote_orders') IS NOT NULL
DROP TABLE #remote_orders ;
-- temp table to hold set of remote order IDs
CREATE TABLE #remote_orders
(
id INT CONSTRAINT pk_id PRIMARY KEY (id)
) ;
-- get all remote order IDs
INSERT INTO #remote_orders
(
id
)
SELECT id
FROM [remote].[order_remote].dbo.[order];
--SELECT * FROM #remote_orders
-- make string of ids for orders that are on remote and not on local
SELECT @ids = @ids + CONVERT(VARCHAR(11), ro.id) + ','
FROM #remote_orders ro
LEFT JOIN [order_local].dbo.[order] lo ON ro.id = lo.id
WHERE lo.id IS NULL ;
--SELECT @ids AS ids
IF LEN(@ids) > 0
BEGIN
-- remove trailing , from @ids
SET @ids = LEFT(@ids, LEN(@ids) - 1) ;
-- build query string to pull remote orders using Linked Server
SET @sql = 'SELECT x.* FROM OPENQUERY([remote], ''SELECT * FROM [order_remote].dbo.[order] WHERE id in (' + @ids + ')'') AS x' ;
--SELECT @sql
-- get remote orders
EXEC (@sql) ;
END
ELSE
BEGIN
PRINT 'no work to do' ;
END ;
END
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2011 at 8:09 am
Derrick Smith, can you tell me more detail of how to do it?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply