【IT168 专稿】当所需要抽取的数据量越来越大，中间的计算越来越复杂的时候我们当初的ETL程序很有可能不能在窗口时间内完成了。这个时候我们就需要对ETL进行优化。当然如果能在设计之初就考虑到优化是最好的。

    在讨论ETL优化之前我们应该首先讨论的是当前程序的性能。首先要定义ETL过程的基线标准。在建立了基线标准的基础上我们再来讨论该如何对已有过程进行优化。

    基线标准也应该包括标准情况下网络传输速度，硬盘读取速度等等的硬件指标。在此之上测量多次ETL过程得到比较稳定的 数据量(M)/时间(s) 的比值作为基线标准。当然前提是在一定的范围内数据量的增长和计算量的增长一定要呈线性关系。一种比较理想的假设就是每条数据都经过相同的计算/转换过程。

    建立基线的好处在于有了调试以及优化的依据。最大程度地避免了人的主观感觉所造成的错误判断。
“经过优化，现在的ETL性能好了一些！”这种话应该换成“经过优化，现在性能比基线性能提升了1%”

    当然我们最终的目的是进行ETL过程的优化。优化的方面包括索引的调整，数据抽取的并行处理，复杂运算的程序处理等手段。编写出高效的SQL则应该是必备的条件了！

    笔者当初经历过一个项目。用的SQL SERVER自带的DTS作为ETL工具。在项目设计阶段没有考虑ETL的性能问题。但项目本身对时间要求还比较严格。要求每半小时同步一次。在原形阶段根本体现不出问题，只重点讨论了展现的方式和内容，确认了ETL过程的逻辑。但随着开发过程中实现的功能越来越多，算法越来越复杂，需要处理的数据也是越来越多 每次ETL过程所需要的时间不断增长，无法满足每半小时同步一次的需求。没办法减少需求和数据量，只好着手开始优化。

    开始优化之前先分析了一下整个ETL过程。发现大致可以分为两类操作。一类操作是直接从数据源拷备数据到目的数据库，一类是从数据源获得原始数据在ETL过程中进行计算再把结果存到目的数据库中。

    从传输的数据量来看直接拷备的部分数据量比较小，需要计算的部分数据量较大。两者比例大概 30% / 70%。

    我们首先从直接拷备的数据开始入手。先测试了一下网络环境，因为两台服务器在一个局域网里，网络很稳定。再分析两边的数据库情况。数据源是Oracle没法动。目的数据库是SQL SERVER目的表建立了多种索引是为了提高前端展现时的效率。

    众所周知索引的作用在于查询时的效能提升。但这是以降低插入，修改，删除操作的性能为代价换来的。比如在插入数据的过程中，数据引擎既要将数据写入，又要同时维护索引，可能还要判断主键重复等情况，对性能有一定影响。
 
    理论上，在插入数据的过程中，如果目的数据表没有索引，不检查约束，不触发，不做事务记录是速度最快的。但一般情况下都难这样理想。所以我们考虑如果需要插入的数据量很大，可以在插入数据前先删除索引，插入操作完毕后再建立索引。这样避免了引擎在插入数据的同时维护索引，新建的索引也会更整齐连贯。

    经过实际测试，确认这种方法确实对性能有所提升。现在只要能排除锁表，并发等操作冲突的话基本没有什么优化的余地了。
    再看需要计算的部分。由于整个过程的逻辑比较复杂，数据量较大所以耗时较长。用掉了整个ETL过程 90% 时间。这部分理所当然的成为此次优化的重点。

    开发这个ETL程序的是个新手，以前做过ASP的WEB开发。很少用SQL脚本写逻辑程序。想当然的用游标实现了大部分逻辑运算。把SQL SERVER的表当成Record Set了！

    而且SELECT后面只跟“*”从来不写字段名，哪怕下面的计算只用到了这张表的一百个字段中的一个！这样的程序运行起来能快才怪！

    知道了原因就开始着手改，把大部分的游标操作用多张临时表之间的“INSTRT”、“UPDATE”代替，把 “SELECT *”换成 “SELECT Column1, Column2……”。

    这样一改程序的运行时间就由40分钟变成3分钟！可见游标的效率之低下！ 

    其实游标的原理很简单，你对一个一千条数据的表做一个游标就等于 SELECT了一千次。而关系数据库的长项是对数据集进行操作。对单条数据库的操作还不如应用程序。把需要运算一千次单条数据的程序变成只需要运算几次数据集的程序。效率自然大大提升。 

    至此时间已经满足需求，我们也圆满的完成了此次优化任务。但做到这一步以后还有没有优化的余地呢？从技术角度看，我觉得还可以做些别的事情。比如单纯的逻辑运算，不和数据库交互的情况下考虑可以用程序实现。做好接口，传入需要计算的原始数据。传出结果直接存入数据库即可。在计算的过程中，可以并行做数据传输操作。又比如所有的临时表都用表变量，或者临时表干脆变成中间表这样就更省去了每次创建和删除的时间。