I have a customer that needs to query a linked server to produce a report. They will be joining a linked server table with a local table.

Is it better to just let them do the select across the linked server OR migrate (backup/restore) the database every day and have there query run against 2 local databases?

For discussion sake let's say the query returns 20K records.

  • If performance is important, and you do not want the administration of the linked server, then, running the report locally sounds like a better option. I would compare the Report's response times of both methods and base a decision off of that. – rvsc48 Mar 29 '17 at 20:06
  • Are you limited to only these options? – Erik Darling Mar 29 '17 at 20:07
  • 1
    You can publish the database instead of backup/restore. – McNets Mar 29 '17 at 20:12
  • @sp_BlitzErik No. Those are the only options I am familiar with – Kyle Johnson Mar 30 '17 at 16:06
  • @McNets Publish? I have only read access to the linked server database – Kyle Johnson Mar 30 '17 at 16:07

You'll likely get the best performance by copying the content of the remote table locally first, then running the query. Especially if you filter the remote table through a remote view or a stored proc on the remote server.

Joining across a linked server can be brutal on performance depending on the implementation details of the query.

| improve this answer | |
  • @sp_BlitzErik spelling edits FTW – Wes Mar 29 '17 at 20:46
  • 2
    @Wes I had Richie write a bot that checks Max's posts for typos. – Erik Darling Mar 29 '17 at 20:51
  • 1
    Thanks, Erik! Stupid me attempting to answer on mobile, again. – Max Vernon Mar 29 '17 at 22:12
  • 1
    @MaxVernon Even after 10 years, typing on phones is horrible. – Erik Darling Mar 29 '17 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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