How to optimize simple linked server select query?

Question

Hello,

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?

Answer 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.