将机器学习服务器上的Stored Proc查询远程服务器的效率如何。 [英] How efficient is it to have the Stored Proc on the machine learning server query a remote server.

问题描述

我试图找出如何将所有R代码用于在我的机器学习服务器上的存储过程中处理数据而不使用链接服务器。我所经历的所有教程都假设你有机器学习
服务器的数据。 

I am trying to figure out how to put all the R Code for pulling\processing the data in a stored procedure on my machine learning server without using a linked server. All the tutorials I have gone through have assumed you have the data on the machine learning server. 

我试图避免;

• 链接服务器查询的性能损失未获得良好的执行计划
• 在ML服务器上暂存数据。 

谢谢，

Phil

推荐答案

Phil，

如果您愿意关闭默认防火墙规则（请参阅
帖子了解详情）然后运行机器学习服务的SQL Server实例应该能够使用ODBC从R进程到其他SQL Server数据库进行标准查询。您需要使用
SQL身份验证（用户名/密码）连接到其他SQL Server数据库。 Windows身份验证不起作用，因为R进程作为本地R用户之一运行（例如MSSQLSERVER01）。

If you're willing to turn off the default firewall rule (see post for details) then your SQL Server instance that's running Machine Learning Services should be able to make standard queries from the R process to other SQL Server databases using ODBC. You'll need to connect to the other SQL Server databases using SQL authentication (username/passoword). Windows authentication will not work because the R process is running as one of the local R users (e.g. MSSQLSERVER01).

代码需要看起来像：

sqlConnString <- "Driver=SQL Server;Server=OtherServer; Database=MyDatabase;Uid=user_name;Pwd=password"

sqlDataSource <- RxSqlServerData(connectionString = sqlConnString,
   query = "SELECT * FROM myTable",
   rowsPerRead = sqlRowsPerRead)

myDataSet <- rxImport(sqlDataSource) # you could also load this into a .xdf file if it's very large

我发现在同一台服务器上从SQL中提取数据的性能通常具有可比性从不同的SQL Server实例中提取数据（使用ODBC移动数据时）。在我的设置中，所有机器都位于同一个数据中心，
性能是单CPU速度的瓶颈，而不是网络性能。

I've found that performance for pulling data from SQL on the same server is generally comparable to pulling data from a different SQL Server instance (when using ODBC to move the data). In my setup where all of the machines are located in the same data center, performance is bottlenecked on single-CPU speed, not network performance.

祝你好运！

Bob

这篇关于将机器学习服务器上的Stored Proc查询远程服务器的效率如何。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！