Join in linked server or join in host server?

Question

Here's the situation: we have an Oracle database we need to connect to to pull some data. Since getting access to said Oracle database is a real pain (mainly a bureaucratic obstacle more than anything else), we're just planning on linking it to our SQL Server and using the link to access data as we need it.

For one of our applications, we're planning on making a view to get the data we need. Now the data we need is joined from two tables. If we do this, which would be preferable?

This (in pseudo-SQL if such a thing exists):

 OPENQUERY(Oracle, "SELECT [cols] FROM table1 INNER JOIN table2")

or this:

 SELECT [cols] FROM OPENQUERY(Oracle, "SELECT [cols1] FROM table1")
 INNER JOIN OPENQUERY(Oracle, "SELECT [cols2] from table2")

Is there any reason to prefer one over the other? One thing to keep in mind: we are on a limitation on how long the query can run to access the Oracle server.

Answer 1

If the inner join significantly reduces the total number of rows, then option 1 will result in much less network traffic (since you won't have all the rows from table1 having to go across the db link

Answer 2

I'd go with your first option especially if your query contains a where clause to select a sub-set of the data in the tables.

It will require less work on both servers, assuming there are indices on the tables in the Oracle server that support the join operation.

Answer 3

What hamishmcn said applies.

Also, SQL Server doesn't really know anything about the indexes or statistics or cache kept by the oracle server. Therefore, the oracle server can probably do a much more efficient job with the join than the sql server can.