链接服务器查询性能降低 [英] Slow performance in linked server query
问题描述
我正在使用链接服务器查询
从MSSQL到mySql.
MySql要查询的表有800K +记录
I'm using a Linked Server Query
from MSSQL to mySql.
MySql table to be queried has 800K + records
我正在使用临时表从链接的服务器中提取结果，并在该临时表上进行联接以进行SQL查询
I'm using a temp table to pull the results from linked server and do a join on that temp table for SQL query
以下两者之间是否存在性能差异?
Is there a performance difference between:
Declare
@MyString varchar(max),
@Address varchar(20),
@tempTable (Address, ColumnB, ColumnC)
set @MyString = 'Select Address, ColumnB, ColumnC from schemaname.tablename where ''' + convert(varchar(30),@Address) + ''') order by ColumnB desc limit 10'
set @MyString = 'Select * from Openquery([My_Linked_Server], ''' + REPLACE(@MyString, '''', '''''') + ''')'
insert into @tempTable
exec (@MyString)
和
Set @MyString = 'Select Address, ColumnB, ColumnC from schemaname.tablename where ''' + convert(varchar(30),@Address) + ''') order by ColumnB desc limit 10'
exec (@MyString) at My_Linked_Server
(所有操作均在SQL中完成)
--second方法当前给我一个错误:
(all this is done in SQL)
--second approach is currently giving me an error:
链接服务器"My_Linked_Server"的OLE DB提供程序"MSDASQL"已返回 消息"[MySQL] [ODBC 5.2(w)驱动程序]不支持可选功能". 消息7391，第16级，状态2，第14行该操作无法 执行此操作是因为链接服务器的OLE DB提供程序"MSDASQL" "My_Linked_Server"无法开始分布式事务.
OLE DB provider "MSDASQL" for linked server "My_Linked_Server" returned message "[MySQL][ODBC 5.2(w) Driver]Optional feature not supported". Msg 7391, Level 16, State 2, Line 14 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "My_Linked_Server" was unable to begin a distributed transaction.
DTC已启动
使用此方法的另一个问题是，我得到的结果是1秒到1分钟的随机计时
Another problem with using this is that I'm getting random timing on the results 1 second to 1 minute
提前谢谢！
更新:
第一种方法给出了很好的结果.剩下的唯一问题是结果的随机计时.超过80％的时间是即时结果.这可能是由于MySql表锁(类型为MyISAM的表)造成的，因为不断对该表进行写操作，并且在SQL Server查询MySQL的使用情况信息中正在运行其他作业.
First approach gives quite good results. The only issue remaining is the random timing of the results. More than 80% of the times the result is instant. This could be due to MySql table locks (tables of type MyISAM) since there's constant writing to this table and additional job running in SQL Server Querying MySQL for usage information.
这是对延误的合理解释吗?
Is this a reasonable explanation for the delay?
推荐答案
调试MSDTC问题可能会有些痛苦.您说它正在运行，是否同时在两个服务器上运行?服务器之间是否有防火墙-您必须确保每台服务器都可以DTCP另一台服务器. DTCPing是可以下载的MS实用程序.您也可能会遇到权限问题.
Debugging MSDTC problems can be a bit painful. You said it is running, is it running on both servers? Are there firewall(s) between the servers -- you have to make sure each server can DTCPing the other one. DTCPing is a MS utility you can download. You can have permission problems too.
DTC的性能有时会非常糟糕，但这通常是由于带宽和往返时间问题或事务量很大而导致的内存消耗.据我所知，它总是比非MSDTC等价物慢-假设它们实际上是等价物
DTC performance can be pretty awful at times, but this is usually due to bandwidth and roundtrip time issues or memory consumption if the transactions are large. As far as I know, it is always slower than the non MSDTC equivalent -- assuming that they are actually equivalent
就个人而言，如果我可以重写查询以完全避免MSDTC，那么我通常对结果会更满意.如果您在远程服务器上插入exec proc的输出，则将始终调用MSDTC.
Personally, if I can rewrite the query to avoid MSDTC completely, I am usually happier with the result. If you insert the output of an exec proc on the remote server, MSDTC will always be invoked.
如果对随机时序没有详细的建议，也许您会对其进行详细解释.
No real advice on the random timings, perhaps if you explained in more detail.
从本文
DTCPING对远程计算机进行匿名RPC调用
DTCPING makes anonymous RPC call to remote machine
默认情况下，来自xp，sp2的远程计算机将不接受匿名呼叫，因此它为您提供了访问被拒绝的错误消息(OX5错误代码)
from xp,sp2,by default,remote machine won't accept anonymous calls,so it is giving you access denied error message (OX5 error code)
如果禁用RestrictRemoteClients密钥，则远程计算机将接受匿名呼叫
if you disable RestrictRemoteClients key,remote machine accepts anonymous calls
http://msdn.microsoft.com/security/productinfo/XPSP2/networkprotection/restrict_remote_clients.aspx
这只是DTCPING工具的问题，此错误与DTC无关.
This is just DTCPING tool problem,This error is not related to DTC.
这篇关于链接服务器查询性能降低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
