如何优化简单的链接服务器选择查询?
我有一个名为 Table 的表,其中包含以下列:
- ID(int、主键、聚集、唯一索引)
- TEXT (varchar 15),
位于名为 LS 的 MSSQL 链接服务器上。链接服务器位于同一台服务器计算机上。并且:
当我打电话时:
SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM Table')
需要400 毫秒。
当我调用时:
SELECT ID, TEXT FROM LS.dbo.Table
需要200毫秒
当我在LS服务器上直接调用查询时:
SELECT ID, TEXT FROM dbo.Table
需要100毫秒。
在很多地方我都读到 OPENQUERY 更快,但在这个简单的情况下它似乎不起作用。当我从另一台服务器而不是直接从 LS 调用该查询时,我该怎么做才能使该查询更快?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
怎么样
SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM dbo.Table')
通过使用架构使查询等效?无论如何,请阅读此Linchi Shea 的有关链接服务器的文章
注意:链接服务器调用总是比直接调用慢。从 SQL Server 到您的 SSMS 现在首先要经过另一个 SQL Server 实例,所以当然会更慢。
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.