Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
The last sentence answered your own question. :) –  Jon Seigel Mar 25 at 15:28
    
@Geoff Dwady, since your are truncating the table, a better option is to create a new table, push the records to it and rename the table. I hope it will be faster since the truncating time is NIL. After that you can easily delete the old table without causing any issues. –  Biju jose Jul 28 at 5:03

1 Answer 1

up vote 0 down vote accepted

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.