经常我们在论坛上看到很多人提问题：一条语句实现*****.
几年前我也追求这种，别人几个循环嵌套实现的，自己力争一条语句利用关系逻辑来实现，弄完之后自我感觉良好，在数据仓库部门工作一段时间后越来越发现这些东西的不实用。
在这很多情况下，最原始的写法可能最好:
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.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)
- 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) b
on 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_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开销
调优处理:
- 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 =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中，以上五个语句是一个批，
如果写成:
- 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 =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)
- SQL code
-
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
改用循环来更新
- SQL code
-
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。
以上并没有什么技术难度，入门者都可以做的，写在这里，只是代表一种观念，花里胡梢的不一定是好的
