Remote Query Join Performance

3 Nov 2005 11:13 PM

Asa Monsey

I have a remote query to an oracle table with 81 millions rows in it.

The query runs on two SQL servers, both with remote collation and collation
compatible turned on.

Both execution plans use a nested joop join. However, on one server, the
local table is the outer input and the remote table is the inner input. The
reverse is true on the other server. The difference in performance is a
factor of 20.

I was wondering if anyone could explain:
1. How does SQL Server decide which input should be inner or outer, and
2. Can you force the order?

Thanks,
Asa Monsey

3 Nov 2005 11:32 PM

Asa Monsey

I have been able to force the join order and have the local table used as the
outer input, however SQL Server is using a table spool on the remote data
source and pulling 81 million rows into SQL server before joining to the 2000
rows in my local table.

A third questiion would be can you prevent a table spool?

Basically, I am certain that the performance of 2000 individual queries back
to the Oracle database will be substantially better than pulling 81 million
rows to SQL Server. How do I convince SQL Server of this?

Thanks,
Asa Monsey

Show quote

"Asa Monsey" wrote:

> I have a remote query to an oracle table with 81 millions rows in it.
>
> The query runs on two SQL servers, both with remote collation and collation
> compatible turned on.
>
> Both execution plans use a nested joop join. However, on one server, the
> local table is the outer input and the remote table is the inner input. The
> reverse is true on the other server. The difference in performance is a
> factor of 20.
>
> I was wondering if anyone could explain:
> 1. How does SQL Server decide which input should be inner or outer, and
> 2. Can you force the order?
>
> Thanks,
> Asa Monsey