SQL Server 2005: Select that Joins Tables from a Remote Server Runs Too Slowly - When I "select min(column)"

Question

Update: After some experimentation, it appears that the thing that is slow is when I call "SELECT min(column) FROM theTableValuedFunction()".

I'll call the local server ServerLocal and the other remote server ServerForeign. I have a SQL query in a table valued function on ServerLocal that looks like this:

SELECT columns
FROM ServerForeign.database.tableA
JOIN ServerForeign.database.tableB ON columns
JOIN ServerForeign.database.tableC ON columns
JOIN ServerForeign.database.tableD ON columns
JOIN ServerForeign.database.tableE ON columns
JOIN ServerForeign.database.tableF ON columns
WHERE conditions

So here's my questions:

When I run this, does ServerLocal transmit the whole query to ServerForeign and ask it to do the join itself and get back the results in one operation, or does it grab entire tables, needlessly transmitting everything only to filter it down/join them on ServerLocal?

The "WHERE conditions" in the table valued function are very general; in various places where the table valued function is called it applies further where conditions. Is there any efficiency to be gained by moving those where clauses up into the table valued function code, or is there no difference?

Would there be significant benefit to putting the table valued function on ServerForeign and calling that from ServerLocal, or will I still incur the same slowness just transmitting the data? If I do move the table valued function to ServerForeign, and ServerLocal applies a WHERE clause at the point where it calls the table valued function, is SQL Server smart enough to transmit that WHERE clause from ServerLocal to ServerForeign and use it to reduce the number of rows transmitted, or does it mindlessly return all of them and drop rows that don't match?

For various external reasons I'd prefer NOT to have to put any code on ServerForeign. If there is a way to run all the SQL code on ServerLocal and still efficiently work with data from ServerForeign, that would be preferable.

Answer 1

if you do an Execution Plan (Control-L in SQL Server Manager Studio), you'll see exactly what it's submitting. The steps will be called "Remote Query", and if you hover over it, it will show you the remote query that's being sent.

Answer 2

I've found in circumstances like this, it can be quicker to cache the remote data locally in a temp table, especially if you can filter it using a where clause while pulling it over.

Answer 3

I ran into this myself. A lot of times, the remote tables will be copied over to the local database to execute the query. Try removing any "where" clause criteria on the remote databases and it should speed up.

Obviously that is not a solution, but most recommend to create a SP or view on the remote database and call that instead.

So in your case if you create a view, ensure the WHERE clause is on the remote server, at least for the columns that are from the remote tables.