Inner join speed issue (Linked Server column)

Question

I have a view (View A) that pulls in columns from a number of tables. It also pulls in a column from another view (View B) which gets its data from a Linked Server table.

Now, View B runs fine, pulling back 11,000 rows in about a second. View A also runs fine. However, if I INNER JOIN from View A to View B on a column that comes from the Linked Server, the entire query runs so slow it times out.

If I INNER JOIN from View A to View B on a column that does NOT come from the Linked Server, it runs fine.

So I traced the issue to joining on a column which resides on the Linked Server. I just have no idea how to fix it.

Can anyone give me any pointers?

If A contains columns from B and you are joining A to B on those columns, then you're basically doing a self-join against a linked server (slow). Can you provide the schema? Maybe you don't need the join. — Anon
– Anon, Commented Jul 17, 2013 at 16:51
I can see in Activity Monitor the query that is taking a huge amount of time. I didn't write this so I assume its SQL getting the data from the linked server. The query says: "SELECT * FROM OpenRowset(TABLE TRCDATA, @traceid, @records)". — creatiive
– creatiive, Commented Jul 18, 2013 at 8:23

Dan Bracuk · Accepted Answer · 2013-07-17 16:59:28Z

1

The circumstances were slightly different, but both my co-workers and I have seen evidence that if you have something like this:

select something 
from LinkedServer.DataBase.Owner.Table
where whatever

then sql server will select the entire table from the other server first, and apply the where clause afterwards. That might be happening to you.

We solve the problem by using openquery instead of the fully qualified method shown above, and by putting the openquery results into a temp table. Then we join to the temp table.

answered Jul 17, 2013 at 16:59

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inner join speed issue (Linked Server column)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related