SQL Server – Nightly Table Reloads from Linked Server

linked-serversql serversql-server-2008-r2t-sql

I have a number of tables that need to be reloaded nightly from a linked server. My current method of doing this is to create a nightly job to pull the data using something like the following:

TRUNCATE TABLE [TableA]
INSERT INTO dbo.[TableA]
    SELECT [Col1], [Col2], [Col3]
    FROM [linked].[server].[dbo].[TableA]

On some of these tables have millions of rows of data are taking hours to complete. The job sometimes finishes after 8am which is causing performance issues for the applications using the database due to users logging in and querying the db. Most of the tables have multiple indexes.

I'm curious if there is a better method to speed up the process. Furthermore, is it possible and would it be faster to not truncate the table and instead only pull over any new or changed data for each of the tables.

Best Answer

It depends, but options include (and are not limited to):

Restore a backup of the linked database on the local instance (for better uptime, restore to a new name, then DROP DATABASE the old one and then sp_rename the fresh restore), and either
- create synonyms to the restored copy from your current local database so as to allow current queries to continue
  - optionally drop every table except the ones you need, SHRINK, and then do index maintenance to make up for the index fragmentation the SHRINK caused (all before the sp_rename)
- do your "table refresh" from the restored database, then DROP DATABASE the restore database
Use OPENQUERY or EXECUTE AT to send a good JOIN and/or WHERE clause or set of clauses to the linked server, including the maximum update date your local server has, so you retrieve (and insert or update) only the rows that have been inserted or updated since your last refresh
- deal with deleted rows painfully
Use a history table on the remote database to track inserts, updates, and deletes, and use that in your JOIN and/or WHERE clauses sent using OPENQUERY or EXECUTE AT.

Related Solutions

Sql-server – the most efficient way to get the minimum of multiple columns on SQL Server 2005

I tested the performance of all 3 methods, and here's what I found:

1 record: No noticeable difference
10 records: No noticeable difference
1,000 records: No noticeable difference
10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one.
100,000 records: UNION subquery is significantly slower, but UNPIVOT query becomes a little faster than the CASE WHEN query
500,000 records: UNION subquery still significantly slower, but UNPIVOT becomes much faster than the CASE WHEN query

So the end results seems to be

With smaller record sets there doesn't seem to be enough of a difference to matter. Use whatever is easiest to read and maintain.
Once you start getting into larger record sets, the UNION ALL subquery begins to perform poorly compared to the other two methods.
The CASE statement performs the best up until a certain point (in my case, around 100k rows), and which point the UNPIVOT query becomes the best-performing query

The actual number at which one query becomes better than another will probably change as a result of your hardware, database schema, data, and current server load, so be sure to test with your own system if you're concerned about performance.

I also ran some tests using Mikael's answer; however, it was slower than all 3 of the other methods tried here for most recordset sizes. The only exception was it did better than a the UNION ALL query for very large recordset sizes. I like the fact it shows the column name in addition to the smallest value though.

I'm not a dba, so I may not have optimized my tests and missed something. I was testing with the actual live data, so that may have affected the results. I tried to account for that by running each query a few different times, but you never know. I would definitely be interested if someone wrote up a clean test of this and shared their results.

SQL Server Linked Server Insert – Inserting a 30 Million Row Table from a Linked Server

MERGE doesn't seem to be what you want here. You're only inserting new rows with a CREATION_DATE from the past 24 hours, right?

SET NOCOUNT ON;

DECLARE @Yesterday DATE = DATEADD(DAY, -1, GETDATE());

INSERT dbo.ANNOUNCEMENTS(column list)
SELECT column list
  FROM [LINKEDSERVER].[DATABASE].[dbo].[ANNOUNCEMENTS] AS r
  WHERE CREATION_DATE >= @Yesterday 
  AND CREATION_DATE < DATEADD(DAY, 1, @Yesterday)
  AND NOT EXISTS
  (SELECT 1 FROM dbo.ANNOUNCEMENTS WHERE ANN_ID = r.ANN_ID);

Of course having useful indexes on CREATION_DATE and/or ANN_ID will help.

Best Answer

Related Solutions

Sql-server – the most efficient way to get the minimum of multiple columns on SQL Server 2005

SQL Server Linked Server Insert – Inserting a 30 Million Row Table from a Linked Server

Related Question