經(jīng)常我們在論壇上看到很多人提問(wèn)題：一條語(yǔ)句實(shí)現*****.
幾年前我也追求這種，別人幾個(gè)循環(huán)嵌套實(shí)現的，自己力爭一條語(yǔ)句利用關(guān)系邏輯來(lái)實(shí)現，弄完之后自我感覺(jué)良好，在數據倉庫部門(mén)工作一段時(shí)間后越來(lái)越發(fā)現這些東西的不實(shí)用。
在這很多情況下，最原始的寫(xiě)法可能最好:
1,具有最好的可讀性， 像下面這種，不看原始需求，光看語(yǔ)句，我以前自己寫(xiě)的我都不知道這語(yǔ)句是做什么用的
- 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, 具有可能較好的性能，即便不具有最快的執行時(shí)間，也會(huì )從整個(gè)系統角度來(lái)說(shuō)影響最小。
以下是幾個(gè)例子.
1)
- SQL code
select a.*,cnt = (select count(*) from tb where group_id= a.group_id) from ta a
這條語(yǔ)句，很直白，也很容易理解，但是從邏輯上來(lái)看它的效率呢,確實(shí)很差，從邏輯執行上來(lái)看，每掃一條ta表記錄，都要在tb中count一次。(這里我們暫不管你查詢(xún)優(yōu)化器多么智能,只講語(yǔ)句的邏輯寫(xiě)法上)
語(yǔ)句稍做改進(jìn)就成為
- 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, 只是說(shuō)可能tb中并不存在ta中g(shù)roup_id對應的記錄，為防止結果數據丟失所以左連。如果業(yè)務(wù)規則上tb中必有ta中對應的group_id之存在，那么內連接獲取更好的性能。
與第一句相比，先聚合形成較少的結果集， 再連接， 這是從邏輯上 對事務(wù)的分批。
2)
在一個(gè)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
以上為原始語(yǔ)句, 分析實(shí)際情況及數據:
part 為產(chǎn)品表， 百萬(wàn)級 inv_qty 為庫存表 , 每個(gè)part都會(huì )在其中有記錄, inv_type 值從1-300有 300種值，但不一定每個(gè)part都有300個(gè)inv_type
另外，也可能part表中的一些變更，有的inv_qty 維護不及時(shí)，在inv_qty表中的sku并不存在于part表。
part表聚集索引sku_no, inv_qty 聚集索引 sku_no, inv_type為普通索引。
在實(shí)際上執行這條語(yǔ)句時(shí)，實(shí)際IO約為1M , 單條語(yǔ)句執行時(shí)間為 70-90s
實(shí)際上隨著(zhù)時(shí)間的變更，數據的變化，有可能某時(shí)inv_qty 中type為300的part會(huì )劇增, 會(huì )有更大的執行成本和IO開(kāi)銷(xiāo)
調優(yōu)處理:
- 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
實(shí)際最大IO 為200K, 批執行時(shí)間為 15-30s.
這里有幾個(gè)需要說(shuō)明的問(wèn)題:
(a) ,先create 再 insert 來(lái)替代select into
select into 在創(chuàng )建表并復制數據時(shí)會(huì )鎖定系統表，特別是當大事務(wù)或大批量數據處理時(shí)，會(huì )對整個(gè)系統造成比較大的影響，即便從單個(gè)進(jìn)程上來(lái)select into比之insert select減少了一些對于目的表結構、約束的檢查，但是它對整個(gè)系統是不利的
(b) ,這里為什么用動(dòng)態(tài)語(yǔ)句去創(chuàng )建索引及update
在整個(gè)sp中，以上五個(gè)語(yǔ)句是一個(gè)批，
如果寫(xiě)成:
- 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
在這個(gè)批中，優(yōu)化器進(jìn)行操作時(shí)，實(shí)際上語(yǔ)句并未執行，所以這里貌似有索引，實(shí)際上用不到
用動(dòng)態(tài)語(yǔ)句，那么，在新的內存空間中創(chuàng )建索引，在另一新的內存空間執行update時(shí)，動(dòng)態(tài)語(yǔ)句會(huì )又開(kāi)始選擇優(yōu)化，就會(huì )用到先前創(chuàng )建的索引
(c) ,為什么要有最后的一條delete語(yǔ)句
上面說(shuō)了，inv_qty的sku并不一定存在于part表，調優(yōu)的目的在于性能及對整個(gè)系統的影響，如果改變了結果，那么調優(yōu)是絕對失敗的。
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左右記錄的臨時(shí)表, Cur..history_header 數千萬(wàn), HIS..history_header更大好多個(gè)數量級
#orders order_no + order_type 普通索引, history_header order_no + order_type 聚集索引
直接運行時(shí),馬上報錯 IO over the limit 5MB
改用循環(huán)來(lái)更新
- 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, 當然有些人會(huì )使用在#order創(chuàng )建時(shí)就加identity列，再建索引于其上，然后以此來(lái)操作
and a.cust_no is null 這個(gè)容易理解, 為什么要加 and b.cust_no is not null
當history_header中cust_no 本身是null時(shí)，如果不限制，會(huì )陷入死循環(huán), 因為它不停的又把null賦給#order.cust_no。
以上并沒(méi)有什么技術(shù)難度，入門(mén)者都可以做的，寫(xiě)在這里，只是代表一種觀(guān)念，花里胡梢的不一定是好的
