Excel服务器远程回写规则更新超时的解决

2018年12月26日接公司生产计划部计划员报告“零件委外计划”报表无法保存，零件委外计划的内容构成是生产管理系统中的设备委外加工零部件清单。公司生产计划下发被动停止，采购部门无法工作。主要表现为公司Excel服务器系统（主要用于对外采购及外协加工）中的“零件委外计划”报表在保存时30秒后弹出失败提示“应用规则失败”。以前系统虽慢，但没有出现不能保存的状况。

接到报告后，我通过系统后台分析“零件委外计划”报表模版，经SQL服务器跟踪发现此问题跟回写远程服务器表规则有关。其中一条执行对“生产管理系统”的零部件清单回写采购计划标记，更新语句执行时间超过30秒后系统弹出“应用规则失败”的提示。这条语句经我在控制台上测试，完成更新需要32秒至45秒之间。找到问题所在，如何解决问题，这个问题困扰了一周时间。

公司采购系统由于需要表单较多，采用的是勤哲公司出品的“EXCEL服务器2010版”。该系统以Microsoft Excel为软件操作界面。采购系统服务器跟生产管理系统数据库不在同一服务器上，而这两个系统之间需要数据交换，我们在Excel服务器系统上采用外部数据源方式将生产管理系统接进采购系统。在SQL服务器上的表现就是采用SQLNCLI接口的“链接服务器”形式进行连接。2015年1月“生产管理系统”启用，刚启用时由于生产计划中“零部件清单”后台数据量较少，所以更新较快，随着数据量的增大，目前公司数据库“零部件清单”中的记录数超过46万条，相当于公司从2015年1月至今的3年时间中公司生产了46万个零部件。由于“零部件清单”表字段较多及记录数太多，导致远程更新较慢。

为解决问题，我尝试了如下方法：

1. 查询百度，用关键字“SQLNCLI 更新慢” 查询(SQLNCLI为Microsoft SQL Server Native Client简称)，发现SQLNCLI有很多版本分别对应SQL2005、SQL2008、SQL2012等。公司“生产管理系统”用的SQL版本为2008R2SP3对应SQLNCLI10，版本为10.0,采购系统用的是2005SP4对应SQLNCLI，版本为9.0。我尝试在采购服务器上安装SQLNCLI10接口。安装是成功的，但在SQL服务器SQL Server Management Studio中测试弹出“对应接口服务不适用”，此方法失败。为保持系统原样，我卸载了SQLNCLI10。尝试将链接接口改成ODBC方式，测试结果是速度更慢，还是保留Excel服务器默认接口SQLNCLI,再找其它原因。
2. 二个系统SQL版本不一样，是不是这个原因，我找了一台与“生产管理系统”服务器操作系统及SQL版本都相同的机器进行远程“链接服务器”更新测试，发现更新速度同样慢，自此放弃升级Excel服务器系统的想法。
3. 2018年12月30日，我求助“勤哲公司”技术支持，通过QQ在线与客服从员联系，并将详细问题写邮件给他们，2019年1月3日得到他们的反馈“无法解决我们的问题”。问题还得自己解决。
4. 为暂时解决问题，我参考“微软将分布式事务处理协调器（DTC）配置为通过防火墙”文章，对两台服务进行了相同的DTC配置，但问题依旧。之后我关闭了两台服务器的防火墙及卸载了杀毒软件，不过还是不能从根本上解决问题，只是偶尔能保存成功。
5. 通过近一周的研究，我发现SQLNCLI更新的思路可能是先查询出所有记录，再用查询条件去过滤，这种方式导致数据表的索引可能用不上，百度查询结果也是这样的。数据量小的时候没有问题。我的思路定格在如何能让数据集变小，其实需要生产的零件并不多，大多数记录都是已经完工的数据。我尝试通过“SQL视图”解决问题，在创建视图时加入查询条件减少记录集数据量。控制台测试用SQL语句为：”update V_PartsPerProduct set [PurchasePlan]='OS19-0011J', [PurchasePlanXH]='W1901030009', [PurchaseHaveRead]=1 from [swpmerp].[swpmerp].[dbo].[V_PartsPerProduct] as V_PartsPerProduct where (V_PartsPerProduct.[UniProduct]=N'2018-216-014' and V_PartsPerProduct.[ItemCode] = N'11-2992-47-61-24' )”其中，V_PartsPerProduct为视图名称，测试更新速度在3到4秒以内。至此我先在Excel服务器ES管理台上注册外部数据源，然后更改Excel报表模版回写规则，回写数据源从实体数据表至视图生成的内部表swpm_V_PartsPerProduct,并设置UniProduct及ItemCode二个字段为主键（如下图所示）。

    经测试并查阅SQL跟踪信息，发现已经启用DTC事务处理，并且在进行更新的时候启用事务处理及事务回滚（SET XACT_ABORT ON）。

跟踪到的存储过程更新语句如下：exec sp_cursor 180150013,33,1, N'[swpmerp].[dbo].[V_PartsPerProduct]', @PurchasePlan=N'OS19-0018J', @PurchasePlanXH=N'W1901040068', @PurchaseHaveRead=1, @PurchaseUser=N'Admin', @ToPurchaseDate='2019-01-04 20:31:55.523'，更新在10秒以内完成。经多次测试没有出现“应用规则失败”的提示。一周时间的努力，终于解决了这个小问题。

 

通过这次解决问题的过程得到如下几点启示：

1. 要解决问题，需要综合分析，不能一条路走到黑，我的经历就说明软件总有不完善的地方，如微软数据访问接口SQLNCLI，它是OLDDB的后续版本，以前我从不认为它会有问题。事实是只要涉及远程更新，它的速度就是比本地慢不止一个数量级。
2. 系统管理员需要仔细检查服务器配置，比如防火墙，是否允许DTC通过，SQL服务器是否允许远程访问及启用事务用于服务器到服务器间通讯，以及SQLNCLI接口的配置等。
3. Excel服务器是一个很好的基于Excel模版的管理软件，开放的架构允许用户进行模版定制。缺点就是需要IT管理人员具备一定的SQL开发能力，本次涉及的SQLNCLI接口应该算是微软的BUG,最后通过SQL视图方式尽量在远端减小数据集大小，这样在模版提交条件过滤时加快速度。如果执行“Update 数据表 Set 字段=？Where 条件”将出现灾难，但“Update 视图 Set 字段= ？where 条件” 就能很好地控制更新时间。

以上几条供使用Excel服务器作为管理系统的公司系统管理员及SQL相关软件开发人员参考。

 

本文作者：马安红，现就职于上海人造板机器厂有限公司，担任信息系统管理员，文章不到之处欢迎批评指正。