前言前言有许多的客户已经使用我们的产品好几年,随着数据量的逐步增大，易飞执行效率问题慢慢浮现，程序执行速度越来越慢，甚至出现白屏、死机等。这种问题最常体现在我们关键的批次和报表上面。比如存货系统中的月结程序，进耗存统计表等。生产模块中的批次需求计划、物料需求计划等。易飞程序执行效率优化(操作篇)在进行问题排查前有两点需要注意：在进行问题排查前有两点需要注意：大型的报表、批次，应该放到服务器上进行处理，而不应大型的报表、批次，应该放到服务器上进行处理，而不应该在客户端直接处理该在客户端直接处理 由于此类程序运算的数据量大，需要耗用的资源比较多，一般的客户由于此类程序运算的数据量大，需要耗用的资源比较多，一般的客户端的配置很难达到其要求，所以杀牛焉用鸡刀，要用牛刀。端的配置很难达到其要求，所以杀牛焉用鸡刀，要用牛刀。对于执行时间较长的程序，其处理的时间应该安排在服务对于执行时间较长的程序，其处理的时间应该安排在服务器工作负荷较低的时候（比如凌晨、周末等）器工作负荷较低的时候（比如凌晨、周末等） 对于特殊的程序要在特殊的时段来处理。如果在服务器的负荷高峰对于特殊的程序要在特殊的时段来处理。如果在服务器的负荷高峰期进行这类大型程序的运行，则会造成资源阻塞，不仅自身执行出现期进行这类大型程序的运行，则会造成资源阻塞，不仅自身执行出现问题，其他的程序也会出现变慢甚至卡住的情况。问题，其他的程序也会出现变慢甚至卡住的情况。易飞程序执行效率优化(操作篇)服务人员篇服务人员篇在此处说明的异常是指：易飞关键程序执行速度过慢而造成程序异常在此处说明的异常是指：易飞关键程序执行速度过慢而造成程序异常中止，常见异常现象如下：中止，常见异常现象如下：批次、报表执行已经执行有一段时间但后续无反应批次、报表执行已经执行有一段时间但后续无反应有诸如有诸如”Lock Time Out”Lock Time Out”或者或者“SQL Server SQL Server 连接超时连接超时”等信息提示等信息提示当然，如果是数据本身造成的异常当然，如果是数据本身造成的异常-比如比如SQLSQL追踪档提示追踪档提示ROLLBACKROLLBACK，然后放，然后放到事件查询器里面执行也提示错误等，则需要服务人员进行数据以及流程到事件查询器里面执行也提示错误等，则需要服务人员进行数据以及流程排查，不在此讨论。排查，不在此讨论。易飞程序执行效率优化(操作篇)步骤一：检查程序是否为最新，到步骤一：检查程序是否为最新，到PATCH区上区上与时俱进下，没准你所遇到的问题，我们其他与时俱进下，没准你所遇到的问题，我们其他客户早已经遇到并做过对应优化了。客户早已经遇到并做过对应优化了。不知道怎么更新？请查看肖方成同学做的不知道怎么更新？请查看肖方成同学做的易飞正易飞正确打补丁方式确打补丁方式。易飞程序执行效率优化(操作篇)步骤二：检查易飞的环境配置步骤二：检查易飞的环境配置1.1.如果是易飞如果是易飞6060或者或者6060以下版本，首先检查以下版本，首先检查BDEBDE的设置，调的设置，调整整SQLSQL最大等待时间最大等待时间: :可以视情况调整，比如3000 30000 300000与与TIMEOUTTIMEOUT属性一属性一致致：可以视情况调整，比如3000 30000 300000易飞程序执行效率优化(操作篇)可以视情况调整，比如3000 30000 300000易飞程序执行效率优化(操作篇)此类错误提示：此类错误提示：中文提示：“没有足够的内存执行该操作”英文提示：“Insufficient memory to complete operation” 易飞程序执行效率优化(操作篇)如果仍然不行，试着尝试下面的措施：（如果主SQL查询结果非常大时，比如有上百万笔数据，可能错误为：Temporary Table Resource Limit 易飞程序执行效率优化(操作篇)步骤二：检查程序以及环境是否更新步骤二：检查程序以及环境是否更新2.2.如果易飞版本为如果易飞版本为7070，则从，则从PATCHPATCH目录下更新目录下更新dsado_d5.bpldsado_d5.bpl文文件，然后刷新到系统盘的件，然后刷新到系统盘的SYSTEM32SYSTEM32目录下。目录下。易飞程序执行效率优化(操作篇)步骤三：检查客户是否有自建触发器步骤三：检查客户是否有自建触发器如果发现客户有自建触发器，处理措施请参考文档如果发现客户有自建触发器，处理措施请参考文档“客户自建触发器相关事项客户自建触发器相关事项”易飞程序执行效率优化(操作篇)如果以上措施仍然不能解决问题，如果以上措施仍然不能解决问题，就需要保存好就需要保存好案发现场，提供有价值的信息给后方的研发部案发现场，提供有价值的信息给后方的研发部门。门。需要提供的信息如下：需要提供的信息如下：程序执行的程序执行的LOGLOG档（批次、建档审核段）。档（批次、建档审核段）。客户数据库（此点视客户数据保密性情况而定）客户数据库（此点视客户数据保密性情况而定）使用事件探查器，来追踪使用事件探查器，来追踪SQLSQL的执行情况，并将追踪文件发的执行情况，并将追踪文件发给后方支持部门给后方支持部门 此处需要注意的是，追踪特定数据列才会有参考价值，因此我们提供了此处需要注意的是，追踪特定数据列才会有参考价值，因此我们提供了相应的追踪模板，只需要追踪的时候使用该模板即可（别忘了调整该相应的追踪模板，只需要追踪的时候使用该模板即可（别忘了调整该 模板的限定条件模板的限定条件-HostName-HostName为当前客户端的为当前客户端的HostName)HostName) 至于如何使用事件探查器并设定相应模板，请参考文档至于如何使用事件探查器并设定相应模板，请参考文档SQL Server ProSQL Server Pro。易飞程序执行效率优化(操作篇)开发人员篇开发人员篇首先需要了解的是程序优化是一个不断验证、渐首先需要了解的是程序优化是一个不断验证、渐进的过程，客户的应用场景比较复杂，很多时候进的过程，客户的应用场景比较复杂，很多时候需要逐步确认问题发生的特殊时机和环境，逐步需要逐步确认问题发生的特殊时机和环境，逐步筛选出较好的方案来执行。筛选出较好的方案来执行。优化顺序按照其效果和重要性排序大体如下：优化顺序按照其效果和重要性排序大体如下： -业务逻辑的设计优化业务逻辑的设计优化 -数据库环境优化数据库环境优化 - -程序撰写层次优化程序撰写层次优化 -SQL -SQL语句本身的优化语句本身的优化下面的篇幅就上面所说的四点分别进行说明。下面的篇幅就上面所说的四点分别进行说明。易飞程序执行效率优化(操作篇)一.业务逻辑的优化我们优化的程序往往比较复杂，因此在优化前需要了解到设计该程序的目的以及使用场景。因为许多规格在开立过程中，由于图省事，许多段落都是从别的地方复制过来，或者逻辑本身比较庞大、前后多人开立等原因，造成业务逻辑的偏差和大量冗余。如果能做到对该程序的大体逻辑和应用场景胸有成竹，则能看出并可简化掉许多的不必要逻辑（许多的批次、报表、建档都有此现象）易飞程序执行效率优化(操作篇)二.数据库环境的优化数据库优化目前主要使用在建立合适的索引方面和索引维护方面。此点对于大型报表和批次有比较好的效果。一般对程序运行的主SQL或者运行量较大的SQL进行分析。在其查询条件上进行适当的索引。索引的原理讲解篇请参照杨大鹏的SQL优化培训。索引的操作应用篇请参照黄长圣的索引介绍。 易飞程序执行效率优化(操作篇)二.数据库环境的优化一个客户实例如下：使用易飞已经几年，平时数据量很大。现在执行月结速度越来越慢。最近运行十几小时后异常中止。分析如下：该客户品号就有十万多笔（大家试着用品号数*仓库数*日期数来估算下数据量）。由于做月结，所以其前端选项条件只有库存年月。主要的表如下： -INVLA交易明细信息 (KEY：来源单别、单号、序号、出入别） -INVLB品号每月统计单头（KEY:品号、库存年月） -INVLC品号每月统计单身（KEY:品号、库存年月、仓库） -INVLE品号每月统计子单身（KEY:品号、库存年月、仓库、库位、批号）在2-003里面没有看到针对库存年月的索引。因此在INVLB/INVLC/INVLE上对库存年月建立了聚集索引，在INVLA上对交易日期建立了聚集索引。按照此方法执行后，执行效率有较多的提升。（该客户具体SQL方面也做了优化，后续进行说明）如果客户表的数据量特别大，在企业管理器里面进行索引操作速度会比较慢，可以用SQL命令的方式来执行易飞程序执行效率优化(操作篇)三.程序撰写层次优化尘世间最悲痛的事情莫过于明明一个SQL其实执行一次就OK，你却将它放到了一个多层循环的最内层来跑。大家在上数据结构的时候就计算过类似的东西（一个多层循环假定为4层好了，每层执行1000次，那么其最终执行次数为为万亿次）。所以该项原则其实很简单：对于某段SQL(可别忘了COUNTER也是SQL哈），在不影响到业务逻辑情况下，能转到循环外层就转。 易飞程序执行效率优化(操作篇)四.SQL语句本身的优化撰写撰写SQLSQL语句的原则有三条：语句的原则有三条：1.1.只选出满足需求的最少数据，增一点则多，减只选出满足需求的最少数据，增一点则多，减一点则错。一点则错。2.2.如果如果SQLSQL有多部分组成，每部分的原则请参考第有多部分组成，每部分的原则请参考第一条。一条。3.3.请参考前两条。请参考前两条。易飞程序执行效率优化(操作篇)四.SQL语句本身的优化原理性东西大家可以多看看原理性东西大家可以多看看SQLSQL的相关文档，这里主要说一些小的的相关文档，这里主要说一些小的方法和注意事项。方法和注意事项。能对所有的能对所有的SQLSQL进行优化是最好，但成本很高。我们就进行优化是最好，但成本很高。我们就SQLSQL的重要的重要性和执行频率、目前耗时等因素来量化分析。（此处需要服务性和执行频率、目前耗时等因素来量化分析。（此处需要服务或者客户提供的或者客户提供的SQLSQL追踪档）。追踪档）。SQLSQL追踪档中的重要列说明如下：追踪档中的重要列说明如下： -CPU -CPU 事件所用的事件所用的CPUCPU时间总计（以毫秒为单位）时间总计（以毫秒为单位） -Duration -Duration 事件所花费的时间总计（以毫秒为单位）事件所花费的时间总计（以毫秒为单位） -Reads -Reads 服务器代表事件执行的逻辑磁盘读取数服务器代表事件执行的逻辑磁盘读取数 -Writes -Writes 服务器代表事件执行的物理磁盘写入数服务器代表事件执行的物理磁盘写入数 -HostName -HostName 客户正在运行的计算机名（进行当前用户的数据筛选就靠它了）客户正在运行的计算机名（进行当前用户的数据筛选就靠它了） 易飞程序执行效率优化(操作篇)四.SQL语句本身的优化下面介绍下技术组杨大鹏一个分析下面介绍下技术组杨大鹏一个分析SQLSQL的简便方法的简便方法 -将得到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 *,select *,特别是在选出来的栏位非常多的时候。特别是在选出来的栏位非常多的时候。如果该如果该SQLSQL的目的在于判断有效性，请首先了解到是否该数据集是不是的目的在于判断有效性，请首先了解到是否该数据集是不是只返回一笔，如果不能确定，请用只返回一笔，如果不能确定，请用TOP 1TOP 1（包括建档的检核）（包括建档的检核） (我前两年遇到一程序，在展单身的时候，要判断某个栏位是否存在，结果该老兄写了一检核，关联了好几个表，并且还有重复关联的情况，遇到一客户数据狂多，没有用TOP 1，直接返回上百万笔，直接死机。）在组在组SQLSQL条件的时候，尽量不要在条件的时候，尽量不要在SQLSQL条件上建索引的栏位上使用函数。条件上建索引的栏位上使用函数。 举例如下（如果LA004建有索引）： WHERE SUBSTRING(LA004,1,6)=FBegin-效果差 WHERE LA004 LIKE FBegin%-效果好 但如果是 LA004 LIKE %FBegin ，则同样没有效果。在在WHERE WHERE 条件里面将建有索引栏位的条件放在最前面（比如条件里面将建有索引栏位的条件放在最前面（比如TA008TA008建有建有索引，且也有它作为条件，则应该将它放到最前面。索引，且也有它作为条件，则应该将它放到最前面。不要用不要用TD003+TD004=TH001+TH002TD003+TD004=TH001+TH002之类作为连接条件或者之类作为连接条件或者WHEREWHERE条件，条件，而应该用而应该用TD003=TH001 AND TD004=TH002TD003=TH001 AND TD004=TH002。DISTINCT/UNION/ORDER DISTINCT/UNION/ORDER 能不用就不要用。能不用就不要用。易飞程序执行效率优化(操作篇)四.SQL语句本身的优化把限制条件放在分组前而不是分组后。把限制条件放在分组前而不是分组后。 低效: Select JOB , AVG(SAL) FROM EMP GROUP JOB HAVING JOB = PRESIDENT or JOB = MANAGER 高效: Select JOB , AVG(SAL) FROM EMP Where JOB = PRESIDENT or JOB = MANAGER GROUP JOB 易飞程序执行效率优化(操作篇)四.SQL语句本身的优化总是使用索引的第一个列总是使用索引的第一个列如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引 （比如建立一个组合索引 ,TB003+TB004+TB005,如果是TB003作为条件被使用，则该索引起效，如果不是,比如用TB004作为条件，则该索引失效。）ININ适合于外表大而内表小的情况；适合于外表大而内表小的情况；EXISTSEXISTS适合于外表小而内表大的情况适合于外表小而内表大的情况 在在ININ后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数后面，减少判断的次数 一次更新多条记录比分多次更新每次一条快一次更新多条记录比分多次更新每次一条快, ,就是说批处理好就是说批处理好 常见于我们的复杂批次或者报表、把大部分的数据都放到主SQL中一次性取出，而不是下很多SQL来分别取出-但主SQL太复杂也有维护困难的隐患。 比如： Select .from . Where While not EOF Begin Insertvalues. Next End 可以改成下面的写法： INSERT INTO FROM 的格式来进行整批插入易飞程序执行效率优化(操作篇)四.SQL语句本身的优化=4 =4 比比 3 3好，前者可以快速定位。好，前者可以快速定位。用用OROR的字句如果太慢时，可以考虑分解成多个查询，并且的字句如果太慢时，可以考虑分解成多个查询，并且通过通过UNIONUNION连接多个查询。连接多个查询。 最好不要用最好不要用NOT IN,NOT IN,考虑用考虑用NOT EXISTS,NOT EXISTS,或者采用连接，然或者采用连接，然后加上外表的某个不可空白栏位的条件来筛选（比如后加上外表的某个不可空白栏位的条件来筛选（比如TA003 IS NULL)TA003 IS NULL)。如果某个如果某个COUNTERCOUNTER对这家客户没有用，就改到本地执行好对这家客户没有用，就改到本地执行好了。（发现我们的了。（发现我们的COUNTERCOUNTER有滥用的情况）（比如单位换有滥用的情况）（比如单位换算之类的算之类的COUNTERCOUNTER）如果如果SQLSQL比较复杂，有嵌套比较复杂，有嵌套SQLSQL的情况，请不要忘记给内部的情况，请不要忘记给内部SQLSQL加条件，缩小内部加条件，缩小内部SQLSQL的返回数据量。（这个类似于循的返回数据量。（这个类似于循环层的概念）环层的概念）易飞程序执行效率优化(操作篇)四.SQL语句本身的优化进行进行UNIONUNION时，应尽量在各段时，应尽量在各段SELECTSELECT中写入条件而不是在中写入条件而不是在UNIONUNION动作做动作做好了再做过滤。好了再做过滤。如果如果UNIONUNION的段落太多，可以考虑使用临时表。的段落太多，可以考虑使用临时表。在使用临时表的时候，可以直接使用在使用临时表的时候，可以直接使用SELECT.into #temp from SELECT.into #temp from .的语句，直接用的语句，直接用select select 语句创建临时表语句创建临时表 这样做的好处在于：这样创建出来的临时表的表结构实时与数据库中对应表字段的类型一致，同时也可以同时将select出来的数据直接插入到刚创建的临时表中，效率更高。 但是这样做不会帮你把来源表的索引一并复制过来，需要手工建立，切记。但是这样做不会帮你把来源表的索引一并复制过来，需要手工建立，切记。临时表的命名也很重要，为了防止名字冲突，一般命名原则是使用者临时表的命名也很重要，为了防止名字冲突，一般命名原则是使用者+ +时分秒。时分秒。临时表也别忘记了索引临时表也别忘记了索引易飞程序执行效率优化(操作篇)四.SQL语句本身的优化在对几个较大的数据集进行在对几个较大的数据集进行UNIONUNION时，应尽量在各段时，应尽量在各段SELECTSELECT中写入条件而不是在中写入条件而不是在UNIONUNION动作做好了再做过滤。动作做好了再做过滤。ORDER BYORDER BY按聚集索引列排序效率最高（排序的效率也很重按聚集索引列排序效率最高（排序的效率也很重要要)判断满足条件的记录是否存在，最好不要用判断满足条件的记录是否存在，最好不要用SELECT SELECT COUNT(*),COUNT(*),可以考虑用可以考虑用 SELECT TOP 1 SELECT TOP 1 实际的例子实际的例子:PURI13:PURI13在在SetButtomStateSetButtomState函数里面用函数里面用COUNT(*)COUNT(*)来判断存在性，结果没想到来判断存在性，结果没想到客户数据太多，超出客户数据太多，超出INTINT类型的范围了而提示数据溢出。类型的范围了而提示数据溢出。为了减少客户等待的焦虑心情，可以考虑使用处理进度条，为了减少客户等待的焦虑心情，可以考虑使用处理进度条，否则没有任何进展，客户会以为程序执行死掉。否则没有任何进展，客户会以为程序执行死掉。 大家可以想下我们等电梯的时候，电梯的楼层指示灯。大家可以想下我们等电梯的时候，电梯的楼层指示灯。易飞程序执行效率优化(操作篇)谢谢观看！谢谢观看！在此感谢对本文内容提供建议，贡献心得的同事在此感谢对本文内容提供建议，贡献心得的同事（排名按姓名的字母顺序，哈哈）（排名按姓名的字母顺序，哈哈）程杰程杰陈省陈省黄长圣黄长圣江博江博李小刚李小刚肖方成肖方成杨大鹏杨大鹏易飞程序执行效率优化(操作篇)