同义词对SQL服务器中的链接服务器的性能影响

On localserver (a SQL Server 2008 R2), I have a synonym called syn_view1 pointing to the linked server remoteserver.remotedb.dbo.view1

在localserver (SQL Server 2008 R2)上，我有一个名为syn_view1的同义词，指向链接的服务器remoteserver.remotedb.dbo.view1

This SLOW query takes 20 seconds to run.

这个缓慢的查询运行需要20秒。

select e.column1, e.column2
from syn_view1 e
where e.column3 = 'xxx'
  and e.column4 = 'yyy'
order by e.column1

This FAST query takes 1 second to run.

这个快速查询需要1秒才能运行。

select e.column1, e.column2
from remoteserver.remotedb.dbo.view1 e
where e.column3 = 'xxx'
  and e.column4 = 'yyy'
order by e.column1

The only difference in the two queries is really the presence of the synonym. Obviously, the synonym has an impact on the performance of the query.

这两个查询的惟一区别是同义词的存在。显然，同义词会影响查询的性能。

The execution plan for the SLOW query is :

慢查询执行计划为:

Plan                Cost %  Subtree cost
4 SELECT
I/O cost: 0.000000  CPU cost: 0.000000  Executes: 0  
Cost: 0.000000                  0.00    3.3521
    3 Filter
    I/O cost: 0.000000  CPU cost: 0.008800  Executes: 1  
    Cost: 0.008800              0.26    3.3521
        2 Compute Scalar
        I/O cost: 0.000000  CPU cost: 3.343333  Executes: 1  
        Cost: 0.000000          0.00    3.3433
            1 Remote Query
            I/O cost: 0.000000  CPU cost: 3.343333  Executes: 1  
            Cost: 3.343333      99.74   3.3433

And for the FAST query:

对于快速查询:

Plan            Cost %  Subtree cost
3 SELECT
I/O cost: 0.000000  CPU cost: 0.000000  Executes: 0  
Cost: 0.000000              0.00    0.1974
    2 Compute Scalar
    I/O cost: 0.000000  CPU cost: 0.197447  Executes: 1  
    Cost: 0.000000          0.00    0.1974
        1 Remote Query
        I/O cost: 0.000000  CPU cost: 0.197447  Executes: 1  
        Cost: 0.197447      100.00  0.1974

My understanding is that in the SLOW query, the server fetches all the data from the remote server, then applies the filter (though without index) whereas in the FAST query the server fetches the filtered data from the remote server, thus using the remote indexes.

我的理解是，在慢速查询中，服务器从远程服务器获取所有数据，然后应用过滤器(尽管没有索引)，而在快速查询中，服务器从远程服务器获取经过过滤的数据，从而使用远程索引。

Is there any way to use the synonym while being fast? Maybe a setup of the linked server ? the local database server?

有什么方法在快速使用同义词吗?可能是链接服务器的设置?本地数据库服务器?

Thanks for the help!

谢谢你的帮助!

2 个解决方案

#1

I would dump the data without the order by into a temp table on local server. Then I would select from the temp table with the order by. Order by is almost always the killer.

我将把没有订单的数据转储到本地服务器上的临时表中。然后，我将使用order by从临时表中选择。“待命”几乎总是杀手。

#2

The accepted answer for this post on dba.stackexchange.com notes that performance gotcha's may occur in queries over linked servers due to limited access rights on the linked server, restricting the visibility of the table statistics to the local server. This can affect query plan, and thus performance.

dba.stackexchange.com上这篇文章的公认答案指出，由于链接服务器上的访问权限有限，限制了表统计信息对本地服务器的可见性，因此可能会在链接服务器上的查询中出现性能陷阱。这可能会影响查询计划，从而影响性能。

Excerpt:

摘录:

And this is why I got different results. When running as sysadmin I got the full distribution statistics which indicated that there are no rows with order ID > 20000, and the estimate was one row. (Recall that the optimizer never assumes zero rows from statistics.) But when running as the plain user, DBCC SHOW_STATISTICS failed with a permission error. This error was not propagated, but instead the optimizer accepted that there were no statistics and used default assumptions. Since it did get cardinality information, it learnt that the remote table has 830 rows, whence the estimate of 249 rows.

这就是我得到不同结果的原因。作为sysadmin运行时，我得到了完整的分布统计信息，该统计信息表明没有具有order ID > 20000的行，估计是一行。(回想一下，优化器从不假设统计数据为零行。)但是当作为普通用户运行时，DBCC SHOW_STATISTICS会出现权限错误。这个错误没有传播，但是优化器接受没有统计数据并使用默认假设。由于它确实获得了基数信息，它了解到远程表有830行，因此估计有249行。