前陣子在網路看到「該靠 EF(LINQ)還是自幹 SQL 語法(甚至一律轉成 SP)」 的討論,我的「個人偏好」挺明確-CRUD 可靠 EF/ORM 省工,複雜查詢或操作則走 Dapper 自己寫 SQL。不過它只算是「偏好」,其效益因客觀條件劇烈變動,若無視開發者背景、人力資源配置、系統需求等各種因素,無限期支持 OOO 一定比 XXX 好,肯定會在特定情境踩坑。既然沒有一體適用的「建議」,我就只從開發老人的角度聊聊決策理由及優劣分析,不陷入追求「最佳解」的迷思。(充其量只會有符合某種情境的「最適解」)

依據看過的專案實踐,我把在 .NET 執行 SQL 邏輯的策略分成三種:EF(LINQ)、自幹 SQL 語法、將邏輯都放入 SP,先分析一下優缺點:

EF(LINQ)

優點:

  1. 開發人員不需要太多太深的 SQL 語法與知識,只需學好 LINQ,由 EF 負責翻譯成具備專業水準的 SQL 語法
    (EF 甚至已考慮許多連中鳥也忽略的細節,例如:讀取資料再更新前先比對欄位是否已被第三方異動,避免更新衝突。)
  2. 支援強型別及 Intellisense,不必擔心敲錯欄位名稱
  3. 相較於傳統 ADO.NET 新増與更新,寫法簡潔許多(例如:一行更新
  4. 完全杜絕 SQL Injection 風險
  5. 支援跨資料庫開發,實現「可抽換不同廠牌資料庫」的夢想

缺點:

  1. 難以應用 SQL 語法專屬特性簡化問題或提升效能(例如:CTE、Cursor、Index Hint…)
  2. 將複雜查詢邏輯轉成單一 LINQ 查詢很耗腦力
    (造成有些開發者會選擇將資料分批拉回再用 .NET 處理,其效率不如在 DB 端直接完成)
  3. 在某些狀況下 LINQ 可能被轉成較無效率的語法,效能低於預期

自幹 SQL 語法

優點:

  1. 得以完全發揮 SQL 語法特性,展現極致效能
  2. 直接提供 SQL 可省去由 LINQ 轉換的過程,效率略好
  3. 開發者充分掌握最終 SQL 指令,較能精準控制執行效能、鎖定範圍等細節
    例如:一次更新多筆資料的某個欄位
  4. 必要時可依 SQL 與 C# 強項拆解運算邏輯,由 SQL 查詢取得半成品再以 C# 加工轉為最終結果,靈活分工可節省可觀開發時間

缺點:

  1. 可能徹底誤用 SQL 語法特性,引發驚人災難(水能載舟,亦能覆舟呀)
  2. 有寫出 SQL Injection 的可能 Orz
  3. 遇到 INSERT/UPDATE 時要逐一列舉欄位名稱、宣告欄位值變數,超級囉嗦
  4. 綁死資料庫廠牌甚至版本,大幅提高抽換資料庫難度

一律寫成 SP

優點:

  1. SP 經過事先編譯,效率優於 .NET 動態傳入 SQL
  2. 採此模式時,一般多由專屬有經驗人員撰寫,與放任開發者自由發揮相比,較易管控品質
  3. 邏輯集中於 SP,有利於跨平台共用(例如:從 Java、PHP 也可直接引用)

缺點:

  1. 所有邏輯限定只能用 SQL 語言(T-SQL、PL/SQL…)實現,難度較高,需要更多相關知識才能勝任
  2. 某些情境將邏輯拆分成兩段,.NET 與 SQL 分別處理自己擅長部分是最省時省力的做法,限定在 SP 實現一切時就喪失此優勢
    (SQLCLR 算是例外,但與純 .NET 相比仍有限制)
  3. SP 的開發偵錯環境遠不如 .NET 程式便利(尤其 Visual Studio 一出,誰與爭峰?) 
  4. 所有運算負擔集中在資料庫端,無法靠增加 .NET 中台、前台主機數量提高系統產量

 

當優缺點已知,依據開發者背景、團隊人力配置、專案需求,要做出抉擇便非難事:

  • 如果你有一批經驗豐富,有能力用 PL/SQL 或 T-SQL 刷 LeetCode 面試題庫的專屬開發人員,就全部寫 SP 吧!不要為難前後端的 .NET 開發人員。
  • 如果系統要求將來資料庫平台可以抽換,EF 是較省力選擇,避免日後換掉 DB 痛到像剝皮。
  • 如果你的開發團隊 SQL 知識背景不深,使用 LINQ 可以確保大家完成水準之上的 SQL 相關系統,還不必擔心豬隊友搞出 SQL Injection 讓系統裸奔。
  • 如果你的系統一秒鐘幾十萬上下、資料量龐大,對資料庫一丁點效能提升也錙銖必較,那麼別猶豫,SQL 絕對要自己寫。

回到我的個人觀點,明知 .NET 端有一堆神兵利器還限定自己只用 T-SQL、PL/SQL 解決問題,是信仰堅定的苦行僧才走的修行之路,專案團隊傾向全端開發沒有專門研究 DB 的人員,全面 SP 化對我來說太苦太難成本太高,優先排除。 那我為什麼偏好「查詢用 Dapper,CRUD 用 EF/LINQ」?

我愛死在 C# 輸入屬性名詞前幾個字,Visual Studio 帶出完整名稱,不用擔心奶油桂花手敲錯字 Bug 半天。而 new EntityObject,Add 後 SubmitChanges 就完成新増,跟自己組 INSERT INTO TXX (C1,C2,C3…) VALUES (@C1,@C2,@C3…),再 AddParameter("@C1")、AddParameter("@C2")、AddParameter("@C3") 相比,根本是跟人火拼要帶手槍還是揮球棒的問題呀!

做 CRUD 借助 EF/ORM 省時又省力,是明智之舉。至於查詢,則有 Dapper 自己寫 SQL vs 寫 LINQ 轉 SQL 兩種選擇,要怎麼選?依我看法,SQL vs LINQ 就像手排車 vs 自排車的差異。

自排固然方便,但要能充分發揮車輛性能極致,手排才是王道!在今天,馬路上跑的幾乎全是自排車,在 F1 賽車界依舊是手排的天下:來源

在分秒必爭的賽道中,搭載扭力轉換器傳輸動力、透過排檔電腦決定檔位的傳統自排變速箱,雖然便利、讓車手可以專注在賽道路線的攻略,但是檔位的切換與動力的傳輸卻顯得遲緩且不直接,無法確實將檔位與引擎轉速維持在車手所需的範圍,結果就是影響實際的單圈速度。
即使傳統手排在主流賽事中已不常見,但追根究柢,序列式變速箱、自手排變速箱仍舊是以手排變速箱為基礎所發展而來,若以廣義而論,手排仍是賽車運動中的主流。

手排更能貫徹車手的意志,是好事也是壞事。手排讓舒馬克在賽道上風馳電掣,也讓菜鳥上坡熄火倒滑害你驚呼What The Fxxk。

EF 查詢轉換 SQL 的過程有許多眉角不易掌控,而只有自己寫 SQL 才能善用 CTE、Index Hint、WITH (NOLOCK) 等技巧讓查詢效能最佳化,我面對的系統需求甚少有抽換 DB 的可能,但對系統效能有較高要求,故只能假裝自己是專業賽車手,不妄想開自排一路打 D 檔拿下名次。(資安宣導:自己寫 SQL 請務必使用參數化查詢,提醒愛用參數組 SQL 字串的同學,閻羅王最近針對 SQL Injection 開發者研發了一批專用刑具在等著你們…)

你不會因為換手排車就會開比較快(也有可能是死比較快),走這條路必須投資時間學習 T-SQL、PL/SQL 語法、查詢技巧、效能考量,搶了原本 EF 代勞的重責大任,知識與經驗不足很容易砸鍋,這也是選擇自己寫 SQL 前必要的認知。

EF 與自寫 SQL 併用的組合,像是用高階語言快速開發,但易成瓶頸的重度運算改用 C/C++ 寫成程式庫從外部呼叫,試著各取其長處。這種做法比起純用高階語言複雜,需要多懂 C 並存在沒管好 Unmanaged 記憶體當機的風險,但如果目標是要省時省力又兼顧效能,這是很值得考慮的做法。

最後我想說的是:不管自排或手排,駕駛永遠是關鍵,別當三寶…


Comments

# by Rico

閒聊補充 Michael Schumacher(一般常見稱呼舒馬克、舒米、大舒,哥哥) Ralf Schumacher( 小舒,弟弟) Mick Schumacher(大舒的兒子,18y,f3進行中)

# by oaww

其實我比較想問SSMS的IDE甚麼時候可以改善(死)

# by 路過

>>"EF 甚至已考慮許多連中鳥也忽略的細節,例如:讀取資料再更新前先比對欄位是否已被第三方異動,避免更新衝突。" 請問此句意思是說"更新前,EF會自行重新比對即時資料庫的該列全部資料欄數值是否已經被異動"嗎? 還是大大的意思是指 "EF會檢測此次更新是否有更新到資料筆數正確與否"? 謝謝~

# by Jeffrey

to 路過,是指檢查該列的資料欄是否跟當初讀取相同,類似這篇文章( http://blog.darkthread.net/post-2009-07-14-linq-to-sql-research1.aspx )中 LINQ to SQL 採行的 WHERE 條件比對。在 EF 將欄位 ConcurrencyMode 設成 Fixed 即可啟用。延伸閱讀:http://www.binaryintellect.net/articles/58216146-d464-42cb-b60d-15215467b107.aspx

# by Minom

這是個值得探究的問題 之前有幾年時間用linq在寫 之後也跟著用EF 但是發現很多時候它會自己加入一些奇怪的語法 或組成複雜的語法 效能不好(大概是我功力不夠) 重點是用sql profiler在追的時候超難看 最近又開始回歸自己寫sql 能夠自己控制語法 效能差很多 用linq或EF是真的很方便 但要寫的好真的是很困難

# by Chuan

請一下,SP有限定用EF或dapper執行嗎? 兩者您會比較建議哪一種呢?

# by Jeffrey

to Chuan, 我偏好Dapper,取其輕巧單純,因少有跨DB要求,某些需配合DB專屬參數型別的場合,回頭用IDbCommand我也接受。

# by Ho.Chun

請問一下 如果 Dapper 和 EF Core 並存於一個專案中的化 SQL Connection Pool 會共用嗎 ? - 另外這邊 「查詢用 Dapper,CRUD 用 EF/LINQ」 應該改為 「查詢用 Dapper,CUD 用 EF/LINQ」 嗎 ?

# by Jeffrey

to Ho.Chun, 連線字串相同的話會共用。至於讀取,如果是單筆資料,我還是會用 EF。

# by Feng

根本是跟人火「併」要帶手槍還是揮球棒的問題呀! 我猜黑大要校稿(到底看哪裡)

# by Jeffrey

to Feng, 校稿也沒用 https://blog.darkthread.net/blog/typo-correction-w-chatgpt/ ,錯字已經錯到成為個人特色了...

Post a comment