T-SQL操作几个数百万行到上千万行数据的表的过程中提高效率的几点体会

最近做一个OLAP项目，要用T-SQL频繁操作几个大表，数据量大概在四、五百万到一千多万之间。这些T-SQL需要在几个小时内执行完毕。开发、测试过程遇到过几次T-SQL执行超时的情况。最慢的时候要18个小时才执行完毕。还有时候太慢了就先停止了。经过多次调整，现在整批代码大概4个小时内可以执行完毕。以下是几点重要的体会：

1：对数据库来说，写比读消耗资源高许多倍。具体多少倍当然和软硬件配置、数据库逻辑设计（表之间的关系）、数据库物理设计（索引、约束、主键等）有密切关系。在这个项目中我觉得大概有100倍。
有一步操作要把不合格的数据删除。大概会把一个500万条记录的表的数据删掉一半。删除这些数据大概要花三个小时。后来改为不删除而是仅仅做标记。显然后面用到这个表的时候会话更多的时间去搜索表和索引。但是在这个项目来看这样做是合算的。后面的步骤中的操作时间并没有明显增加。

2：通过限制数据集数量，改一步执行为循环执行的方法，可以有效提高效率。开发中遇到过几次大表之间join导致执行时间超长。最终的解决方案之一就是用row_number函数加CTE加while循环，让每一次join涉及到的记录集都比较小。虽然需要循环多次，但是最终的时间要比一次执行快了许多倍。