效率优化

    随着数据量的逐步增大，软件执行效率问题慢慢浮现，程序执行速度越来越慢，
甚至出现白屏、死机等。这种问题最常体现在我们关键的批处理和报表上面。比如
存货系统中的月结程序，进耗存统计表等。生产模块中的批次需求计划、物料需求
计划等。

    在进行问题排查前有两点需要注意：大型的报表、批处理，应该放到服务器上进
行处理，而不应该在客户端直接处理，由于此类程序运算的数据量大，需要耗用的资
源比较多，一般的客户端的配置很难达到其要求，所以杀牛焉用鸡刀，要用牛刀。对
于执行时间较长的程序，其处理的时间应该安排在服务器工作负荷较低的时候（比如
凌晨、周末等），对于特殊的程序要在特殊的时段来处理。如果在服务器的负荷高峰
期进行这类大型程序的运行，则会造成资源阻塞，不仅自身执行出现问题，其他的程
序也会出现变慢甚至卡住的情况。

    首先需要了解的是程序优化是一个不断验证、渐进的过程，客户的应用场景比较
复杂，很多时候需要逐步确认问题发生的特殊时机和环境，逐步筛选出较好的方案来
执行。

优化顺序按照其效果和重要性排序大体如下：
1.业务逻辑的设计优化
2.数据库环境优化
3.程序撰写层次优化
4.SQL语句本身的优化

下面的篇幅就上面所说的四点分别进行说明：

 

一.业务逻辑的优化
    我们优化的程序往往比较复杂，因此在优化前需要了解到设计该程序的目的以及
使用场景。因为许多规格在开立过程中，由于图省事，许多段落都是从别的地方复制
过来，或者逻辑本身比较庞大、前后多人开立等原因，造成业务逻辑的偏差和大量冗
余。如果能做到对该程序的大体逻辑和应用场景胸有成竹，则能看出并可简化掉许多
的不必要逻辑。

二.数据库环境的优化
    数据库优化目前主要使用在建立合适的索引方面和索引维护方面。
此点对于大型报表和批处理有比较好的效果。一般对程序运行的主SQL或者运行量较大
的SQL进行分析。在其查询条件上进行适当的索引。

三.程序撰写层次优化
    尘世间最悲痛的事情莫过于明明一个SQL其实执行一次就OK，你却将它放到了一个
多层循环的最内层来跑。大家在上数据结构的时候就计算过类似的东西（一个多层循
环假定为4层好了，每层执行1000次，那么其最终执行次数为为万亿次）。所以该项原
则其实很简单：对于某段SQL，在不影响到业务逻辑情况下，能转到循环外层就转。

四.SQL语句本身的优化
    撰写SQL语句的原则：只选出满足需求的最少数据，增一点则多，减一点则错。能对

所有的SQL进行优化是最好，但成本很高。我们就SQL的重要性和执行频率、目前耗时

等因素来量化分析。（此处需要服务或者客户提供的SQL追踪档）。

    SQL追踪档中的重要列说明如下：
    -CPU 事件所用的CPU时间总计（以毫秒为单位）
    -Duration 事件所花费的时间总计（以毫秒为单位）
    -Reads 服务器代表事件执行的逻辑磁盘读取数
    -Writes 服务器代表事件执行的物理磁盘写入数
    -HostName 客户正在运行的计算机名（进行当前用户的数据筛选就靠它了）

    将得到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语句进行调优。

 

一些注意事项：

尽可能的不要用select *,特别是在选出来的栏位非常多的时候。

 

如果该SQL的目的在于判断有效性，请首先了解到是否该数据集是不是只返回一笔，如果不
能确定，请用TOP 1（包括建档的检核）
 
在组SQL条件的时候，尽量不要在SQL条件上建索引的栏位上使用函数。
  举例如下（如果LA004建有索引）：
  WHERE SUBSTRING(LA004,1,6)=FBegin----效果差
  WHERE LA004 LIKE ‘FBegin%’----效果好
  但如果是 LA004 LIKE ‘%FBegin’ ，则同样没有效果。

在WHERE 条件里面将建有索引栏位的条件放在最前面（比如TA008建有索引，且也有它作为
条件，则应该将它放到最前面。

不要用TD003+TD004=TH001+TH002之类作为连接条件或者WHERE条件，而应该用TD003=TH001
AND TD004=TH002。

DISTINCT/UNION/ORDER 能不用就不要用。

把限制条件放在分组前而不是分组后。

 

总是使用索引的第一个列.如果索引是建立在多个列上, 只有在它的第一个列(leading column)
被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索
引的第二个列时,优化器使用了全表扫描而忽略了索引（比如建立一个组合索引 ,TB003+TB004
+TB005,如果是TB003作为条件被使用，则该索引起效，如果不是,比如用TB004作为条件，则该索
引失效。）

 

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况 在IN后面值的列表中，

将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数

 

一次更新多条记录比分多次更新每次一条快,就是说批处理好常见于我们的复杂批次或者报表、把

大部分的数据都放到主SQL中一次性取出，而不是下很多SQL来分别取出—---但主SQL太复杂也

有维护困难的隐患。

 

>=4 比 >3好，前者可以快速定位。

用OR的字句如果太慢时，可以考虑分解成多个查询，并且通过UNION??连接多个查询。

 

最好不要用NOT IN,考虑用NOT EXISTS,或者采用连接，然后加上外表的某个不可空白栏位的
条件来筛选（比如TA003 IS NULL)

 

如果SQL比较复杂，有嵌套SQL的情况，请不要忘记给内部SQL加条件，缩小内部SQL的
返回数据量。（这个类似于循环层的概念）

 

在对几个较大的数据集进行UNION时，应尽量在各段SELECT中写入条件而不是在UNION动作
做好了再做过滤。

 

在使用临时表的时候，可以直接使用SELECT…..into ##temp ?from …….的语句，直接用
select 语句创建临时表，这样做的好处在于：这样创建出来的临时表的表结构实时与数据
库中对应表字段的类型一致，同时也可以同时将select出来的数据直接插入到刚创建的临
时表中，效率更高。 但是这样做不会帮你把来源表的索引一并复制过来，需要手工建立，
切记。

 

临时表的命名也很重要，为了防止名字冲突，一般命名原则是使用者+时分秒。
临时表也别忘记了索引

 

ORDER BY按聚集索引列排序效率最高（排序的效率也很重要)

 

判断满足条件的记录是否存在，最好不要用SELECT COUNT(*),可以考虑用 SELECT TOP 1
避免超出INT类型的范围了而提示数据溢出。

 

为了减少用户等待的焦虑心情，可以考虑使用处理进度条，否则没有任何进展，客户会以为
程序执行死掉。