複雜的資料查詢：手工打造 SQL 指令 vs. 撰寫程式碼

對於複雜的資料查詢，我們是該用手工打造的 SQL 指令呢，還是該寫程式碼來處理？在甚麼情況下該選擇甚麼方法呢？

我的看法是應該盡量以撰寫程式的方式來處理。

在解釋原因之前，要先聲明一件事：我不是 SQL 高手，所以在一開始打算寫這篇文章時，我就在想我的結論可能過於偏頗。不過，若能拋磚引玉，得到其他前輩、高手的意見，因而修正自己的想法，倒也是好事一樁。

其實在多年以前，我便覺得撰寫 SQL 對我來說是一件「寫時容易讀時難」的工作。我不是說兩三兩個資料表 join 這類簡單的 SQL 查詢，我說的是包含七八層、甚至更多層子查詢的 SQL。因此在我自己寫的程式裡面，我都盡量不寫太複雜的 SQL，而我的 SQL 功力自然也就一直停留在小學生的階段（好吧，也許是幼稚園階段）。

兩種解法

會想把這個議題寫下來，其中一個原因是不久前又碰到了複雜 SQL 導致嚴重效能問題的情形。另一個原因，是正好一個朋友（T 先生）最近 msn 給我，提到他們公司裡面有一位 SA 寫了一個超級 SQL。這個超級 SQL 指令多達 8463 bytes，據他描述，若以每一列都填滿 80 個字元來計算，也有一百多列。

對一個 SQL 功力只有小學程度的我來說，這道一百多列的 SQL 指令自然讓我覺得不可思議，便半開玩笑的回他：「快請他回火星吧！」然而他說這個 SQL 不僅解救了他們的難題，執行速度還很快哩。

我比較擔心的，是萬一將來 business rule 有變，原作者又離開團隊了，當初也沒寫文件，那麼這些 SQL 誰有辦法改？朋友回答的大意是：在時程壓力下，能解決問題的就是好方法。言下之意是顧不了那麼多了。我不禁想到《軟體工程與 VSTS》裡面提到的「時程膽小雞」（schedule chicken）。

架構問題：效能 vs. 可維護性

對我來說，要用手寫的複雜 SQL，還是撰寫程式來解決，是屬於架構面的議題。在決定採用何種解法時，必須同時考慮系統的整體效能和往後的維護問題。我不喜歡為了執行效能而捨棄可維護性，更何況我覺得兩者並非魚與熊掌。很多時候，程式開發人員並沒有仔細分析問題，寫出來的程式跑得慢，就想用 SQL 來一次解決。其實加快程式執行效能的技巧何其多（例如：資料快取、預先產生暫時資料、建立索引等），如果未經深思熟慮和多方測試，便認定哪一種解法就一定有更佳的執行效能，對此結論我會持相當保留的態度。

工作方式與習慣問題

有句諺語說，只會用鐵槌的木匠，看到甚麼東西都覺得是釘子，會想敲它一下。的確，每個人都會盡量用自己熟悉的工具來解決問題（我也是）。這便產生了另一個問題。

以我自己碰到的情況來說，開發團隊經過效能調校和檢視 log 之後，發現有一位 SA 負責的子系統都跑得特別慢，只要有使用者上線執行他負責的子系統，整個系統效能就 down 下來，變成龜速，甚至導致應用程式伺服器完全無法回應。進一步查看其程式碼，發現該子系統的程式很多都是超過 10 個 subquery 的 SQL，也就是說，那位 SA 開的規格書大都如此，所有資料查詢的處理都完全用複雜 SQL 指令一次解決，而未曾想過有些情況可能用程式碼處理比較適當。更令人驚訝的是，有些複雜 SQL 就只是取一兩個 count 值而已（select count(*) from many, many, many subqueries），而且還用到了許多會造成 full table scan 的條件判斷式。不禁納悶：這當中的 subquery 難道是必要的嗎？會不會有在迴圈裡面反覆執行此 SQL 的情況（這會是多大的效能負擔與資源浪費啊！）？

我並不是對 SQL 專家有意見，也並非全然反對寫複雜 SQL。為了提升效能，且實在別無他法時，自然還是得用。但前提是必須把 SQL 指令的 business rule 寫成文件，以便日後維護時可對照著 SQL 拆解、調整。這是為了避免 SA/SD 直接在規格書裡面寫好 SQL，程式設計師不明就裡直接照著 coding 而埋下的炸彈。少了說明文件，日後他人接手維護程式時，有多少人有把握完全理解那些複雜 SQL 的用意並修改它呢？我想這是我對超級 SQL 退避三舍的最主要原因。

小結

或許有人會認為，那我一定不敢用 regular expression，因為用它寫出來的東西更像天書，比 SQL 還難懂。恰好相反，我喜歡用 regular expression。但除非必要，我都盡量找別人寫好的字串樣板來用，而不自己動手寫。因為網路上已經有許多現成的了，而且這種東西不像 SQL 指令那樣對系統的整體效能和資料的正確性有那麼大的影響，如果發現目前使用的字串樣板有缺點，大不了再找一個，或者自己想辦法稍微改一下就行了。

最後，容我耍點引用他人論述來支持自己觀點的小伎倆。在《Release It! Design and Deploy Production Ready Software》這本書裡，作者進一步建議應盡量使用 O-R mapping（物件-關聯對應）框架，而不要手工打造 SQL，其理由與前面談的問題有點關係。且摘錄部分內容如下：

The problem is that developer-crafted SQL tends to be so idiomatic and unpredictable. All the database tuning for the rest of the application won’t help for these beastly one-offs.
開發人員手工打造的 SQL 往往帶有濃厚的個人色彩，而且無法預測。就算對應用程式的其餘部分進行資料庫調校，也無法減輕這類怪獸級 SQL 帶來的傷害。

What makes these handcrafted SQL queries so bad? They usually suffer from a handful of common mistakes. First, they often join on nonindexed columns. Second, they usually join too many tables.Third, developers .... will issue some query that joins five tables to select exactly the one row they're looking for, and then they'll issue that query 100 more times to find other individual rows.
手工打造的 SQL 容易犯幾個毛病：第一，它們常常 join 非索引欄位；第二，它們通常都 join 太多資料表了；第三，開發人員可能會單單為了取得一筆資料列而 join 五個資料表，而且可能還重複執行該 SQL 指令一百次以上，以便取得其他特定的資料列。