CODEWITHSUNDEEP
sqlsql-serverssmsdynamic-sqldelta
n8.
n8.

Reputation: 1738

Diff / Delta script: ideas on streamlining it?

I'm pulling data from a remote DB into a local MS SQL Server DB, the criteria being whether the PK is higher than I have in my data warehouse or whether the edit date is within a range that I provide with an argument. That works super fast so I am happy with it. However, when I attempt to sync this delta table into my data warehouse it takes quite a long time.

Here's my SPROC:

ALTER PROCEDURE [dbo].[sp_Sync_Delta_Table] 
@tableName varchar(max)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql as varchar(4000)

-- Delete rows in MIRROR database where ID exists in the DELTA database
SET @sql = 'Delete from [SERVER-WHS].[MIRROR].[dbo].[' + @tableName
+ '] Where [ID] in (Select [ID] from [SERVER-DELTA].[DELTAS].[dbo].[' + @tableName + '])'
EXEC(@sql)

-- Insert all deltas
SET @sql = 'Insert Into [SERVER-WHS].[MIRROR].[dbo].[' + @tableName
+ '] Select * from [SERVER-DELTA].[DELTAS].[dbo].[' + @tableName + ']'
EXEC(@sql)

END

It works, but I think it takes way too long. For example: inserting 3590 records from the DELTA table into the MIRROR table containing 3,600,761 took over 25 minutes.

Can anyone give me a hint on how I can make this job easier on SSMS? I'm using 2008 R2, btw.

Thanks again!

Nate

Upvotes: 0

Views: 764

Answers (2)

Jim V.
Jim V.

Reputation: 2177

The issue is likely the time required to do a table scan on the 3,600,761 to see if the new records are unique.

First of all, let's confirm that the primary key (ID) on the target table is the clustered index and increasing. 

SELECT s.name, o.name, i.name, i.type_desc, ic.key_ordinal, c.name
FROM sys.objects o
JOIN sys.columns c ON (c.object_id = o.object_id)
JOIN sys.schemas s ON (s.schema_id = o.schema_id)
JOIN sys.indexes i ON (i.object_id = o.object_id) 
JOIN sys.index_columns ic ON (ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.column_id = c.column_id)
WHERE o.name = '[table_name]'

If the index is not an ascending integer, it is possible that the inserts are causing lots of page splits.

Second, what other objects does that insert affect. Are there triggers, materialized views, or non-clustered indexes?

Third, do you have

My suggestion would be to stage the data on the mirror server in a local table. It can be as simple as as:

SET @sql = 'SELECT INTO * [MIRROR].[dbo].[' + @tableName + '_Staging] from [SERVER-DELTA].[DELTAS].[dbo].[' + @tableName + ']'
EXEC(@sql)

After that add a clustered primary key to the table. 

SET @sql = 'ALTER TABLE [MIRROR].[dbo].[' + @tableName + '_Staging] ADD  CONSTRAINT [PK_' + @tableName + ']  PRIMARY KEY CLUSTERED (Id ASC)'
EXEC(@sql)

At this point, try inserting the data into the real table. The optimizer should be much more helpful this time.

Upvotes: 2

Tak
Tak

Reputation: 1562

Change the delete portion to:

SET @sql = 'Delete tbl1 from [SERVER-WHS].[MIRROR].[dbo].[' + @tableName
+ '] tbl1 inner join [SERVER-DELTA].[DELTAS].[dbo].[' + @tableName + '] tbl2 on tbl1.[ID] = tbl2.[ID]'

In future use INNER JOIN instead of IN with Sub Query.

Upvotes: 1

Related Questions