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

fcuandy 2010-03-30 07:45:57

加精

http://topic.csdn.net/u/20090615/01/41b07065-8eed-42d9-91e9-9ac895ca3803.html

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

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.id

    from t_1 a

left join  t_2 b

    on b.date=

        (select max(date) from t_2 x 

            where date<=a.date

                and not exists(select 1 from t_1 y

                        where id<a.id and date>=x.date

                            and not exists(select 1 from t_2 where date>x.date and date<=y.date)

                        )

        )





--or

update a set mid=b.id 

        from   t_1   a 

left   join     t_2   b 

        on   b.date= 

                (select   max(date)   from   t_2   x   

                        where   date <=a.date 

                and (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)

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

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

select a.*,isnull(b.cnt,0) from ta a,

	left join (select group_id,count(*) from tb group by group_id) b

	on a.group_id = b.group_id

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

2)
在一个stored-proc中

select distinct b.sku_no, b.vend_no

	into #sku

	from part b, inv_qty a

	where a.sku_no = b.sku_no

		and 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开销

调优处理:

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 =300

exec('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中，以上五个语句是一个批，
如果写成:

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 =300

create 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_no

delete from #sku where vend_no is null

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

3)



update #orders

	set cust_no = b.cust_no

	from #orders a,Cur..history_header b

	where a.order_no = b.order_no

		and 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

改用循环来更新

set rowcount 10000

select @@rowcount

while @@rowcount>0

update #orders

	set cust_no = b.cust_no

	from #orders a,Cur..history_header b

	where a.order_no = b.order_no

		and a.order_type = b.order_type

		and a.cust_no is null

		and b.cust_no is not null

set 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。

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

...全文