首頁猿問與動態(tài) SQL C#...

與動態(tài) SQL C# 相比，內(nèi)部帶有動態(tài) SQL 的存儲過程有什么優(yōu)勢？

C#

狐的傳說 2023-04-29 18:09:52

問題我發(fā)現(xiàn)自己需要編寫一個帶有分頁和動態(tài)排序的存儲過程（其中 case 語句不起作用）這個答案似乎是唯一的選擇，但是在這一點上，有什么充分的理由讓它成為一個存儲過程嗎？會不會有任何真正的好處，大概不會以任何有用的方式進行預編譯？因此，我更傾向于簡單地在 C# 代碼本身中將其作為動態(tài) SQL 來執(zhí)行，并跳過使用存儲過程的復雜性（我覺得它實際上在 C#構(gòu)建的字符串中更具可讀性）但這會明顯變慢嗎然后使用存儲過程。更有趣的是，這是一個 EF 實際上會更快/相同的領域嗎，因為它確實確實在構(gòu)建動態(tài) SQL 嗎？（見背景）背景我們的大部分數(shù)據(jù)庫代碼都是用存儲過程編寫的（90%），但有一小部分代碼是用實體框架編寫的。我被要求用存儲過程替換 EF，因為這部分代碼已被確定為瓶頸，他們希望對其進行優(yōu)化。作為 EF 的粉絲，我想推遲最佳點，但作為一致性的粉絲，我最初沒有看到任何理由不將這 10% 與其他 90% 協(xié)調(diào)。...現(xiàn)在我做到了，操作有問題涉及分頁和動態(tài)排序！

查看完整描述

1 回答

波斯汪

TA貢獻1811條經(jīng)驗獲得超4個贊

優(yōu)點

SQL 在存儲過程中的優(yōu)點是：

抽象，您可以在不更改 .net 應用程序的情況下重構(gòu)數(shù)據(jù)庫
安全性，您可以通過設置正確的執(zhí)行權(quán)限來進行更好的安全檢查。您也可以限制數(shù)據(jù)集不輸出安全意識信息。
透明度：對數(shù)據(jù)庫執(zhí)行了哪些查詢？
查詢查詢的優(yōu)化，使用正確的索引等。

缺點

存儲過程中動態(tài) SQL 的缺點：

動態(tài) SQL（在 SQL 服務器中）在安全性和性能方面可能很棘手。

安全

SQL Server 防止注入的“工具”較少。變量不能隨處使用，所以只有當你硬編碼所有按選項排序的時候，你才是安全的。但是對于動態(tài)過濾，這可能很困難。

例如這是安全的：

posts?=?context.Posts??
????.FromSql("SELECT?*?FROM?mytable")
????.OrderByDescending(p?=>?p.CreateDate)

這是不安全的（是的，有更好的寫法）：

declare?varchar(200)?@orderby?=?'createDate?DESC'
SET?@sqlCommand?=?'SELECT?*?FROM?mytable?ORDER?by?+?'@orderby
EXEC?(@sqlCommand)

因此，您需要列出所有選項或選中所有選項。

表現(xiàn)

在性能方面，存儲過程中的動態(tài) SQL可能會出現(xiàn)問題。返回非常動態(tài)數(shù)據(jù)的存儲過程可能會選擇錯誤的查詢計劃，性能不佳。

*您可以重新編譯每個存儲過程調(diào)用，但這也有缺點。

表值函數(shù)

另一種選擇是使用表值函數(shù) (TVF)。這些比存儲過程更受限制，但它們返回一個表，您可以對其進行分頁和排序。

如上所述，它們具有存儲過程的優(yōu)點，但沒有數(shù)據(jù)庫中動態(tài) SQL 的缺點！

示例 TVF 和 EF Core

有一個很好的表值函數(shù)和 EF 示例，

.NET Core 與 TFV GetMatchingPostByTitle

posts?=?context.Posts??
????.FromSql("SELECT?*?FROM?dbo.GetMatchingPostByTitle({0})",?searchTerm)
????.Where(p?=>?p.BlogId?==?1)
????.OrderByDescending(p?=>?p.CreateDate)
????.ToList();

將生成此 SQL：