sqlsql-servert-sql

Remote query, view and where clause performance

I'm a bit puzzled over a performance problem with our SQL server when using remote query's and applying a where clause. When I run the query on the local server a clustered index seek is used, but from remote this is not the case.

So when running this on the local server it will take 2 seconds:

SELECT * FROM uv_order WHERE order_id > '0000200000'

But running this from a remote database takes 2 minutes:

SELECT * FROM RemoteServer.data.dbo.uv_order WHERE order_id > '0000200000'

Here uv_order is a quite complex view but since an index seek is used when executing from the local server I don't see why it can't use it when running a remote query. This only seams to apply to view since doing the same thing on a table will work as expected.

Any ideas why this happens and how to "fix" it?

Solution

  • Well you can fix it like this

    select *
from openquery(
    RemoteServer,
    'select * from data.dbo.uv_order WHERE order_id > '''0000200000''''
)