0

This is my issue. I defined a linked server, let's call it LINKSERV, which has a database called LINKDB. In my server (MYSERV) I've got the MYDB database.

I want to perform the query below.

SELECT *
FROM LINKSERV.LINKDB.LINKSCHEMA.LINKTABLE
    INNER JOIN MYSERV.MYDB.MYSCHEMA.MYTABLE ON MYKEYFIELD = LINKKEYFIELD

The problem is that if I take a look to the profiler, I see that in the LINKSERV server lots of SELECT are made. They looks similar to:

SELECT * 
FROM LINKTABLE WHERE LINKKEYFIELD = @1

Where @1 is a parameter that is changed for every SELECT. This is, of course, unwanted because it appears to be not performing. I could be wrong, but I suppose the problem is related to the use of different servers in the JOIN. In fact, if I avoid this, the problem disappear.

Am I right? Is there a solution? Thank you in advance.

LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
ufo
  • 674
  • 2
  • 12
  • 35
  • This is called a parameterized query and it should perform quite well. Please do a search on this term. Maybe you should add an index to LINKTABLE.LINKKEYFIELD – Hogan Sep 06 '13 at 15:49
  • This isn't related to parameterized queries. Queries across linked servers are bound to behave differently. BTW, what version of SQL Server are we talking about? – Panagiotis Kanavos Sep 06 '13 at 15:51
  • **MYSERV** is SQL2008R2, while **LINKSERV** is SQL2008. – ufo Sep 06 '13 at 15:53

1 Answers1

1

What you see may well be the optimal solution, as you have no filter statements that could be used to limit the number of rows returned from the remote server.

When you execute a query that draws data from two or more servers, the query optimizer has to decide what to do: pull a lot of data to the requesting server and do the joins there, or somehow send parts of the query to the linked server for evaluation? Depending on the filters and the availability or quality of the statistics on both servers, the optimizer may pick different operations for the join (merge or nested loop).

In your case, it has decided that the local table has fewer rows than the target and requests the target row that correspons to each of the local rows.

This behavior and ways to improve performance are described in Linked Server behavior when used on JOIN clauses

The obvious optimizations are to update your statistics and add a WHERE statement that will filter the rows returned from the remote table. Another optimization is to return only the columns you need from the remote server, instead of selecting *

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • Thanks for the reply. Of course, the optimizations you suggest are already been implemented in the real scenario. There are also indices for the fields used in the join. Performance are still not very good for us. We should introduce a most powerful import logic. Just another question, do you think `OPENROWSET` could be a good alternative to linked servers or this last is better in any scenario? Thank you. – ufo Sep 09 '13 at 06:39