How to optimize simple linked server select query?

Question

I have a table called Table with columns:

ID (int, primary key, clustered, unique index)
TEXT (varchar 15)

on a MSSQL linked server called LS. Linked server is on the same server computer. And:

When I call:

SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM Table')

It takes 400 ms.

When I call:

SELECT ID, TEXT FROM LS.dbo.Table

It takes 200 ms

And when I call the query directly while being at LS server:

SELECT ID, TEXT FROM dbo.Table

It takes 100 ms.

In many places i've read that OPENQUERY is faster, but in this simple case it does not seem to work. What can I do to make this query faster when I call it from another server, not LS directly?

I've only ever had to use OpenQuery when the linked server was of a different type, e.g Oracle. Is the difference always that marked ie. x2? If you were to send a bigger query requesting more data/heavier filter is the overhead as marked? — CResults
– CResults, Commented Mar 16, 2010 at 8:45
@CResults - when filter is more complex ovehead is bigger, the difference is from several trials avg. Particular queries have very different times. — Tom Smykowski
– Tom Smykowski, Commented Mar 16, 2010 at 8:51

gbn · Accepted Answer · 2010-03-21 12:43:12Z

1

What about SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM dbo.Table') to make the queries equivalent by using the schema?

Anyway, read this article from Linchi Shea about linked servers

Note: a linked server call will always be slower than a direct call. From SQL Server to your SSMS is now going through another SQL Server instance first, so of course it will be slower.

edited Mar 21, 2010 at 12:43

answered Mar 21, 2010 at 12:37

gbn

434k84 gold badges602 silver badges689 bronze badges

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

Collectives™ on Stack Overflow

How to optimize simple linked server select query?

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