2

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.

Improve this question
5
  • 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
    Commented Mar 29, 2017 at 20:06
  • Are you limited to only these options?
    – Erik Reasonable Rates Darling
    Commented Mar 29, 2017 at 20:07
  • 1
    You can publish the database instead of backup/restore.
    – McNets
    Commented Mar 29, 2017 at 20:12
  • @sp_BlitzErik No. Those are the only options I am familiar with
    – Kyle Johnson
    Commented Mar 30, 2017 at 16:06
  • @McNets Publish? I have only read access to the linked server database
    – Kyle Johnson
    Commented Mar 30, 2017 at 16:07

1 Answer 1

Reset to default
2

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
4
  • @sp_BlitzErik spelling edits FTW
    – Wes
    Commented Mar 29, 2017 at 20:46
  • 2
    @Wes I had Richie write a bot that checks Max's posts for typos.
    – Erik Reasonable Rates Darling
    Commented Mar 29, 2017 at 20:51
  • 1
    Thanks, Erik! Stupid me attempting to answer on mobile, again.
    – Hannah Vernon ♦
    Commented Mar 29, 2017 at 22:12
  • 1
    @MaxVernon Even after 10 years, typing on phones is horrible.
    – Erik Reasonable Rates Darling
    Commented Mar 29, 2017 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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