链接服务器性能和选项 [英] Linked Server Performance and options

问题描述

在工作中，我们有两台服务器，其中一台正在运行许多人使用的应用程序，该应用程序具有SQL Server 2000后端.我已经可以自由查询很长时间了，但是无法向其中添加任何内容，例如存储过程或额外的表.

At work we have two servers, one is running an application a lot of people use which has an SQL Server 2000 back end. I have been free to query this for a long time but can't add anything to it such as stored procedures or extra tables.

这导致我们将第二个SQL Server链接到第一个SQL Server，而我建立了一个存储过程的库，该库使用链接的服务器从双方查询数据.其中一些查询花费的时间比我想要的要长.

This has lead to us having a second SQL Server linked to the first one and me building up a library of stored procedures that query data from both sides using linked server. Some of these queries are taking longer than what I would like.

有人可以指出一些有关使用链接服务器的好文章吗?我对找出两者之间要传输的数据特别感兴趣，因为通常大多数sql语句都可以远程执行，但是我感觉它可能正在传输完整的表，通常只是连接到一个小final本地表.

Can someone point me to some good articles about using linked servers? I am particularly interested in finding out what data is being transferred between the two as usually the majority of the sql statement could be performed remotely but I have the feeling it may be transferring the full tables, it is usually just a join to a small final table locally.

我当前还拥有链接服务器选项什么?

Also what do the linked server options do I currently have:

• 排序规则兼容的真
• 数据访问正确
• Rpc True
• Rpc输出为真
• 使用远程排序规则为错误
• 归类名称(空白)
• 连接超时0
• 查询超时0

以为我会更新这篇文章，我在一段时间内使用带有动态参数的openqueries来提高性能，谢谢.但是，这样做最终会使查询变得更加混乱，最终导致您无法处理字符串.终于在今年夏天，我们将SQL Server升级到2008，并实现了实时数据镜像.老实说，开放查询正在接近我的任务的本地查询速度，但是镜像无疑使sql更易于处理.

Just thought I would update this post I used openqueries with dynamic parameters for a while to boost performance, thanks for the tip. However doing this can make queries more messy as you end up dealing with strings. finally this summer we upgraded SQL Server to 2008 and implemented live data mirroring. To be honest the open queries were approaching the speed of local queries for my tasks but the mirroring has certainly made the sql easier to deal with.

推荐答案

我建议在游标循环中建议动态打开查询，而不要使用链接联接. 这是我能够复制MS Access的链接联接性能(至少对于单个远程表而言)的唯一方法.

ms sql中的常规链接联接通过特别拉动大型表中的所有内容，效率太低.

I would advise dynamic openqueries in a cursor loop instead of linked joins. This is the only way i've been able to replicate MS Access' linked join performance (at least for single remote tables)

Regular linked joins in ms sql are too inefficient by pulling everything specially in humongous tables..

-我想知道游标循环内的openqueries有什么不好?如果操作正确，则不会出现锁定问题.

-- I would like to know what is so bad about openqueries inside cursor loops? if done correctly, there are no locking issues.

这篇关于链接服务器性能和选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！