易飞程序执行效率优化(操作篇)

• 大型的报表、批次，应该放到服务器上进行处理，而不应 该在客户端直接处理
由于此类程序运算的数据量大，需要耗用的资源比较多，一般的客户 端的配置很难达到其要求，所以杀牛焉用鸡刀，要用牛刀。
• 对于执行时间较长的程序，其处理的时间应该安排在服务 器工作负荷较低的时候（比如凌晨、周末等）
对于特殊的程序要在特殊的时段来处理。如果在服务器的负荷高峰 期进行这类大型程序的运行，则会造成资源阻塞，不仅自身执行出现 问题，其他的程序也会出现变慢甚至卡住的情况。
四.SQL语句本身的优化
• >=4 比 >3好，前者可以快速定位。 • 用OR的字句如果太慢时，可以考虑分解成多个查询，并且 通过UNION 连接多个查询。 • 最好不要用NOT IN,考虑用NOT EXISTS,或者采用连接，然 后加上外表的某个不可空白栏位的条件来筛选（比如 TA003 IS NULL)。 • 如果某个COUNTER对这家客户没有用，就改到本地执行好 了。（发现我们的COUNTER有滥用的情况）（比如单位换 算之类的COUNTER） • 如果SQL比较复杂，有嵌套SQL的情况，请不要忘记给内部 SQL加条件，缩小内部SQL的返回数据量。（这个类似于循 环层的概念）
步骤三：检查客户是否有自建触发器
如果发现客户有自建触发器，处理措施请参考文档 “客户自建触发器相关事项”
如果以上措施仍然不能解决问题，就需要保存好 案发现场，提供有价值的信息给后方的研发部 门。
需要提供的信息如下：
• 程序执行的LOG档（批次、建档审核段）。 • 客户数据库（此点视客户数据保密性情况而定） • 使用事件探查器，来追踪SQL的执行情况，并将追踪文件发 给后方支持部门
四.SQL语句本身的优化
• 总是使用索引的第一个列
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才 会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全 表扫描而忽略了索引 （比如建立一个组合索引 ,TB003+TB004+TB005,如果是TB003作为条件被使用，则该索引起效，如果 不是,比如用TB004作为条件，则该索引失效。）
二.数据库环境的优化
• 一个客户实例如下：使用易飞已经几年，平时数据量很大。现在执行 月结速度越来越慢。最近运行十几小时后异常中止。 • 分析如下：该客户品号就有十万多笔（大家试着用品号数*仓库数*日 期数来估算下数据量）。由于做月结，所以其前端选项条件只有库存 年月。主要的表如下：
-INVLA交易明细信息 (KEY：来源单别、单号、序号、出入别） -INVLB品号每月统计单头（KEY:品号、库存年月） -INVLC品号每月统计单身（KEY:品号、库存年月、仓库） -INVLE品号每月统计子单身（KEY:品号、库存年月、仓库、库位、批号）
四.SQL语句本身的优化
撰写SQL语句的原则有三条：
1. 只选出满足需求的最少数据，增一点则多，减 一点则错。 2. 如果SQL有多部分组成，每部分的原则请参考第 一条。 3. 请参考前两条。
四.SQL语句本身的优化
原理性东西大家可以多看看SQL的相关文档，这里主要说一些小的 方法和注意事项。 能对所有的SQL进行优化是最好，但成本很高。我们就SQL的重要 性和执行频率、目前耗时等因素来量化分析。（此处需要服务 或者客户提供的SQL追踪档）。 SQL追踪档中的重要列说明如下：
与TIMEOUT属性一 致：可以视情况调 整，比如3000 >> 30000 >> 300000
可以视情况调整，比如 3000>> 30000>> 300000
此类错误提示： 中文提示：“没有足够的内存执行该操作” 英文提示：“Insufficient memory to complete operation”
一.业务逻辑的优化
• 我们优化的程序往往比较复杂，因此在优化前需 要了解到设计该程序的目的以及使用场景。因为 许多规格在开立过程中，由于图省事，许多段落 都是从别的地方复制过来，或者逻辑本身比较庞 大、前后多人开立等原因，造成业务逻辑的偏差 和大量冗余。 • 如果能做到对该程序的大体逻辑和应用场景胸有 成竹，则能看出并可简化掉许多的不必要逻辑 （许多的批次、报表、建档都有此现象）
服务人员篇
在此处说明的异常是指：易飞关键程序执行速度过慢而造成程序异常 中止，常见异常现象如下：
• 批次、报表执行已经执行有一段时间但后续无反应 • 有诸如”Lock Time Out”或者“SQL Server 连接超时”等信息提示 当然，如果是数据本身造成的异常---比如SQL追踪档提示ROLLBACK，然后放 到事件查询器里面执行也提示错误等，则需要服务人员进行数据以及流 程排查，不在此讨论。
下面介绍下技术组杨大鹏一个分析SQL的简便方法
-将得到SQL追踪文件导入到事件探查器。 -选择“文件” ，指向“另存为”菜单，然后单击“跟踪表”选项。 -然后将数据导入到新建立的一个表，比如TEST中，以便于下面用专门的SQL分析语句进 行分析。然后执行下面SQL语句： select TOP 10 convert(varchar(100),TextData) SQL,sum(CPU) CPU,sum(Duration) Duration,sum(Reads) Reads, sum(Writes) Writes,count(*) countsum,sum(Duration)/count(*) AvgDuration from TEST where HostName = ‘ERP0‘—选择需要进行分析的用户数据 group by convert(varchar(100),TextData) HAVING(sum(Duration)) >= 100 order by sum(Duration) DESC,convert(varchar(100),TextData) -从上面的SQL语句我们可以得到某个SQL语句的平均CPU时间、总计执行耗时、总计读写、 以及平均执行次数，然后按照总耗时大小得到前十名。然后重点对这些SQL语句进行调 优。
四.SQL语句本身的优化
一些注意事项
• 尽可能的不要用select *,特别是在选出来的栏位非常多的时候。 • 如果该SQL的目的在于判断有效性，请首先了解到是否该数据集是不 是只返回一笔，如果不能确定，请用TOP 1（包括建档的检核）
(我前两年遇到一程序，在展单身的时候，要判断某个栏位是否存在，结果该老兄写了一检核，关联了 好几个表，并且还有重复关联的情况，遇到一客户数据狂多，没有用TOP 1，直接返回上百万笔，直 接死机。。。）
二.数据库环境的优化
• 数据库优化目前主要使用在建立合适的索引方 面和索引维护方面。 • 此点对于大型报表和批次有比较好的效果。一 般对程序运行的主SQL或者运行量较大的SQL进 行分析。在其查询条件上进行适当的索引。 • 索引的原理讲解篇请参照杨大鹏的SQL优化培 训。 • 索引的操作应用篇请参照黄长圣的索引介绍。
• 在WHERE 条件里面将建有索引栏位的条件放在最前面（比如TA008建 有索引，且也有它作为条件，则应该将它放到最前面。 • 不要用TD003+TD004=TH001+TH002之类作为连接条件或者WHERE条件， 而应该用TD003=TH001 AND TD004=TH002。 • DISTINCT/UNION/ORDER 能不用就不要用。
• • •
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况 在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最 后面，减少判断的次数 一次更新多条记录比分多次更新每次一条快,就是说批处理好
常见于我们的复杂批次或者报表、把大部分的数据都放到主SQL中一次性取出，而不是下很多 SQL来分别取出—---但主SQL太复杂也有维护困难的隐患。 比如： Select „..from „. Where„„ While not EOF Begin Insert„values„.. Next End 可以改成下面的写法： INSERT INTO „FROM 的格式来进行整批插入
三.程序撰写层次优化
• 尘世间最悲痛的事情莫过于明明一个SQL其实执行一 次就OK，你却将它放到了一个多层循环的最内层来跑。 • 大家在上数据结构的时候就计算过类似的东西（一个 多层循环假定为4层好了，每层执行1000次，那么其 最终执行次数为为万亿次）。所以该项原则其实很简 单：对于某段SQL(可别忘了COUNTER也是SQL哈），在 不影响到业务逻辑情况下，能转到循环外层就转。
• 在组SQL条件的时候，尽量不要在SQL条件上建索引的栏位上使用函数。
举例如下（如果LA004建有索引）： WHERE SUBSTRING(LA004,1,6)=FBegin----效果差 WHERE LA004 LIKE ‘FBegin%’----效果好 但如果是 LA004 LIKE ‘%FBegin’ ，则同样没有效果。
此处需要注意的是，追踪特定数据列才会有参考价值，因此我们提供 了相应的追踪模板，只需要追踪的时候使用该模板即可（别忘了调整该 模板的限定条件-HostName为当前客户端的HostName) 至于如何使用事件探查器并设定相应模板，请参考文档SQL Server Profiler.ppt。
开发人员篇
在2-003里面没有看到针对库存年月的索引。因此在INVLB/INVLC/INVLE 上对库存年月建立了聚集索引，在INVLA上对交易日期建立了聚集索引。 按照此方法执行后，执行效率有较多的提升。（该客户具体SQL方面也做 了优化，后续进行说明）
如果客户表的数据量特别大，在企业管理器里面进行索引操作速度会比 较慢，可以用SQL命令的方式来执行
前言
• 有许多的客户已经使用我们的产品好几年, 随着数据量的逐步增大，易飞执行效率问 题慢慢浮现，程序执行速度越来越慢，甚 至出现白屏、死机等。 • 这种问题最常体现在我们关键的批次和报 表上面。比如存货系统中的月结程序，进 耗存统计表等。生产模块中的批次需求计 划、物料需求计划等。