Performance Through Persistence

MILY: Verdana; mso-fareast-font-family: 宋體; mso-bidi-font-family: 宋體; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA">Tom Moreau

Translated by mschen

T-SQL允許你使用不同的方法解決一個問題.有的時候,盡管選擇不是那么明顯,但是卻可以讓你得到令人滿意的和快樂的驚奇.下邊讓我們解讀Dr. Tom Moreau對同一問題不同的可能性的探索.可能我們可以在那些不同的方法之中發現一些珍貴的東西.

  讓我們以我們的老朋友Northwind數據庫為例,這里我們用到的是[order details]表,這個表是一個定單的明細表,和order表是多對一的關系.也就是一個定單對應多個訂購的產品.假設你想得到每個定單訂購的總價值,但是不包括59號產品.Listing 1給了我們第一種解法:

select
  OrderID,sum (Quantity * UnitPrice) value
from
  [Order Details] o1
where
  ProductID <> 59
group by
  OrderID

上邊的語句很簡單,它排除掉了59號產品的定單明細條目,然后進行分組統計.但是如果我們需要忽略掉訂購59號產品的定單呢？也就是說我們要統計沒有包含59號產品的定單的價值.你想到了WHERE, NOT EXIST(S)關鍵詞了嗎？Listing 2給了我們第二種方法:

select
  o1.OrderID,sum (o1.Quantity * o1.UnitPrice) value
from
  [Order Details] o1
where not exists
(
  select
    *
  from
    [Order Details] o2
  where
    o2.OrderID   = o1.OrderID
  and o2.ProductID = 59
)
group by
  o1.OrderID

如果你不喜歡用exist的話,你可以轉化成使用not in:
Listing 3

select
  o1.OrderID,sum (o1.Quantity * o1.UnitPrice) value
from
  [Order Details] o1
where 59 not in
(
  select
    ProductID
  from
    [Order Details] o2
  where
    o2.OrderID   = o1.OrderID
)
group by
  o1.OrderID

盡管Listing 1不滿足我們現在的查詢條件.但是從性能發面考慮,Listing 1還是最好的,因為它只用到了一次表的掃描.而后邊的兩個查詢都是用到了相關子查詢,如果你查看查詢計劃就回看到,他們都涉及到了兩次表的掃描.如果你曾經在 T-SQL用過交叉表查詢的話,你就不會對聚集函數里邊的case結構陌生.現在我們就把這個非常有趣的方法應用到我們的問題中來:
Listing 4

select
  OrderID,sum (Quantity * UnitPrice) value
from
  [Order Details] o1
group by
  OrderID
having
  sum (case when ProductID = 59 then 1 else 0 end) = 0

HAVING子句起到了對分組的結果進行過濾的作用.如果沒有包含59號產品,就會出現0=0,顯然這是滿足條件的.如果包含了59號產品的訂購,就會出現n=0(n<>0),這樣的定單就回被過濾掉.查看執行計劃你就回發現是一次表的掃描,非常棒!
  再來舉一個例子:我們這回用到的表是order表,假設我們要統計只通過一個雇員雇員下定單的顧客.你可以想到用子查詢not exist來實現:

select distinct
  o1.CustomerID
from
  Orders o1
where not exists
(
  select
    *
  from
    Orders o2
    where
      o2.CustomerID = o1.CustomerID
    and o2.EmployeeID <> o1.EmployeeID
)

同樣的,這個語句可以通過帶有HAVING子句的分組來實現.
Listing 6

select
  CustomerID
from
  Orders
group by
  CustomerID
having
  min (EmployeeID) = max (EmployeeID)

另一種方法:
Listing 7

select
CustomerID
from
  Orders
group by
  CustomerID
having
  count (distinct EmployeeID) = 1

Listing 6和Listing 7查詢消耗都要小于Listing 5.相比Listing 5的兩次表掃描,他們只進行一次表的掃描.而Listing 6的損耗還要稍微小于Listing 7.但是,Listing 7的一個顯著的特點就是它可以適應到一個顧客對應兩個雇員,三個雇員......
其實大家可能現在明白了這篇文章將的是什么?它就是教我們怎么用having 子句來達到過濾組的目的.可以達到避免兩次表掃描的目的.可以達到更高的性能.我從這篇文章學到了很多的方法,你呢?