2

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.

3

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.)

| improve this answer | |

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.