0

We have a process in which several site servers send data to a central server (through a Linked Server). A new site has seen the job duration more than double in three weeks, and a couple of the other sites often fail due to run time overlap.

It is a two step process:

  1. Insert new records
  2. Update changed records

The insert only takes a few seconds, but the update takes anywhere from 5 to 20 minutes, depending on the site. I am able to change the query that drives the update and get it down to only a couple seconds, but still when put into an UPDATE statement it takes several minutes.

I am leaning towards moving the jobs to a single job on the central server, so it is a pull operation which, based on the testing I have done, should be much faster. However, my question is: What is considered "best practice" in this situation? I am going to have to change quite a bit to get this working properly, so I might as well do it right.

Dave Johnson
  • 825
  • 16
  • 27
  • This sounds a bit like you're building replication - when it is in fact built into the product. Have you considered replication for your situation and ruled it out for some reason? – Damien_The_Unbeliever Jul 28 '14 at 14:39
  • SQL Replication was ruled out (not by me, I was in high school) back when the system ran on SQL 7.0. I have been wanting to investigate it (I know little about it) for other processes, but had not considered it for this one. I imagine it would work for this, since the table structures are the same? When looking at the central table we know where it came from based on a location fields. – Dave Johnson Jul 28 '14 at 14:41

0 Answers0