SQL查询艺术(T-SQL)[节选]SQL调优之大事务分段处理

经常我们在论坛上看到很多人提问题：一条语句实现*****.
几年前我也追求这种，别人几个循环嵌套实现的，自己力争一条语句利用关系逻辑来实现，弄完之后自我感觉良好，在数据仓库部门工作一段时间后越来越发现这些东西的不实用。
在这很多情况下，最原始的写法可能最好:
1,具有最好的可读性， 像下面这种，不看原始需求，光看语句，我以前自己写的我都不知道这语句是做什么用的

SQL code

create table t_1(ID int,           MID  int,           Date datetime)insert t_1 select 1,             NULL,           '2007-1-21'insert t_1 select 2,             NULL,           '2007-3-25'insert t_1 select 3,             NULL,           '2007-3-26'create table t_2(ID int,           Date datetime)insert t_2 select 1,             '2007-1-22'insert t_2 select 2,             '2007-1-25'insert t_2 select 3,             '2007-1-29'update a set mid=b.idfrom t_1 aleft join  t_2 bon b.date=(select max(date) from t_2 xwhere date<=a.dateand not exists(select 1 from t_1 ywhere id<a.id and date>=x.dateand not exists(select 1 from t_2 where date>x.date and date<=y.date)))--orupdate a set mid=b.idfrom   t_1   aleft   join     t_2   bon   b.date=(select   max(date)   from   t_2   xwhere   date <=a.dateand (select count(*) from t_2 where date>=x.date and date<=a.date)=(select count(*) from t_1 where id<a.id and date>=x.date)+1)drop table t_1,t_2

2, 具有可能较好的性能，即便不具有最快的执行时间，也会从整个系统角度来说影响最小。

以下是几个例子.


1)

SQL code

select a.*,cnt = (select count(*) from tb where group_id= a.group_id) from ta a

这条语句，很直白，也很容易理解，但是从逻辑上来看它的效率呢,确实很差，从逻辑执行上来看，每扫一条ta表记录，都要在tb中count一次。(这里我们暂不管你查询优化器多么智能,只讲语句的逻辑写法上)
语句稍做改进就成为

SQL code

select a.*,isnull(b.cnt,0) from ta a,left join (select group_id,count(*) from tb group by group_id) bon a.group_id = b.group_id

为什么这里用left join而不inner join, 只是说可能tb中并不存在ta中group_id对应的记录，为防止结果数据丢失所以左连。如果业务规则上tb中必有ta中对应的group_id之存在，那么内连接获取更好的性能。
与第一句相比，先聚合形成较少的结果集， 再连接， 这是从逻辑上 对事务的分批。

2)
在一个stored-proc中

SQL code

select distinct b.sku_no, b.vend_nointo #skufrom part b, inv_qty awhere a.sku_no = b.sku_noand a.inv_type = 300

以上为原始语句, 分析实际情况及数据:
part 为产品表， 百万级 inv_qty 为库存表 , 每个part都会在其中有记录, inv_type 值从1-300有 300种值，但不一定每个part都有300个inv_type
另外，也可能part表中的一些变更，有的inv_qty 维护不及时，在inv_qty表中的sku并不存在于part表。
part表聚集索引sku_no, inv_qty 聚集索引 sku_no, inv_type为普通索引。

在实际上执行这条语句时，实际IO约为1M , 单条语句执行时间为 70-90s
实际上随着时间的变更，数据的变化，有可能某时inv_qty 中type为300的part会剧增, 会有更大的执行成本和IO开销

调优处理:

SQL code

create table #sku(sku_no int null, vend_no int null)insert #sku(sku_no) select distinct sku_no from inv_qty where inv_type =300exec('create index idx1 on #sku(sku_no)')exec('update #sku set vend_no = b.vend_no from #sku a,part b where a.sku_no = b.sku_no')delete from #sku where vend_no is null

实际最大IO 为200K, 批执行时间为 15-30s.
这里有几个需要说明的问题:
(a) ,先create 再 insert 来替代select into
select into 在创建表并复制数据时会锁定系统表，特别是当大事务或大批量数据处理时，会对整个系统造成比较大的影响，即便从单个进程上来select into比之insert select减少了一些对于目的表结构、约束的检查，但是它对整个系统是不利的
(b) ,这里为什么用动态语句去创建索引及update
在整个sp中，以上五个语句是一个批，
如果写成:

SQL code

create table #sku(sku_no int null, vend_no int null)insert #sku(sku_no) select distinct sku_no from inv_qty where inv_type =300create index idx1 on #sku(sku_no)update #sku set vend_no = b.vend_no from #sku a,part b where a.sku_no = b.sku_nodelete from #sku where vend_no is null

在这个批中，优化器进行操作时，实际上语句并未执行，所以这里貌似有索引，实际上用不到
用动态语句，那么，在新的内存空间中创建索引，在另一新的内存空间执行update时，动态语句会又开始选择优化，就会用到先前创建的索引
(c) ,为什么要有最后的一条delete语句
上面说了，inv_qty的sku并不一定存在于part表，调优的目的在于性能及对整个系统的影响，如果改变了结果，那么调优是绝对失败的。

3)

SQL code

update #ordersset cust_no = b.cust_nofrom #orders a,Cur..history_header bwhere a.order_no = b.order_noand a.order_type = b.order_type

#orders 300w左右记录的临时表, Cur..history_header 数千万, HIS..history_header更大好多个数量级
#orders order_no + order_type 普通索引, history_header order_no + order_type 聚集索引
直接运行时,马上报错 IO over the limit 5MB

改用循环来更新

SQL code

set rowcount 10000select @@rowcountwhile @@rowcount>0update #ordersset cust_no = b.cust_nofrom #orders a,Cur..history_header bwhere a.order_no = b.order_noand a.order_type = b.order_typeand a.cust_no is nulland b.cust_no is not nullset rowcount 0

这里用了set rowcount, 当然有些人会使用在#order创建时就加identity列，再建索引于其上，然后以此来操作
and a.cust_no is null 这个容易理解, 为什么要加 and b.cust_no is not null
当history_header中cust_no 本身是null时，如果不限制，会陷入死循环, 因为它不停的又把null赋给#order.cust_no。

以上并没有什么技术难度，入门者都可以做的，写在这里，只是代表一种观念，花里胡梢的不一定是好的