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've been using the new lookup function in Reporting Services 2008 R2 when using different servers where I don't have the option of a linked server. However, the DBA just created the a link to that server and I'm wondering if I can get better performance changing my query/report to use it instead of the lookup function.

I definitely have a lot more flexibility going linked server route but I'm not sure about performance.

share|improve this question
add comment

migrated from stackoverflow.com Oct 25 '11 at 12:56

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

If you are really executing the exact same query, then there shouldn't be much of a difference in executing and returning results. I'd expect SQL server with the linked server to have an edge in the actual lookups into the results.

But this won't mean a thing if the queries you are executing against the sources are different. The differences could go either way depending on the specifics of your queries. With the linked server you could be retrieving exactly the data you need from the linked server, so things would probably be faster. But if your query is too general in its join, or retrieves an excessive number of rows from the linked server, that will cost you.

So I vote to go with whichever is more convenient for you. If you need the subquery dataset in SSRS for some other summaries, keep the separate dataset. If you comfortably think in SQL terms, go with the linked server. Then optimize if things are slow.

(It would be easy to make two situations where I could get either answer to be faster, depending on my lookups and queries even when ultimately returning the same results. The query optimizations would easily more than make up for any performance difference between the two methods. For example, with an SSRS lookup, you could use dataset caching, which would avoid waiting for other server entirely.)

share|improve this answer
add comment

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.