首頁(yè) 猿問(wèn) 與動(dòng)態(tài) SQL C#...

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

C#

狐的傳說(shuō) 2023-04-29 18:09:52

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

查看完整描述

1 回答

波斯汪

TA貢獻(xiàn)1811條經(jīng)驗(yàn) 獲得超4個(gè)贊

優(yōu)點(diǎn)

SQL 在存儲(chǔ)過(guò)程中的優(yōu)點(diǎn)是：

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

缺點(diǎn)

存儲(chǔ)過(guò)程中動(dòng)態(tài) SQL 的缺點(diǎn)：

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

安全

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

例如這是安全的：

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

這是不安全的（是的，有更好的寫(xiě)法）：

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

因此，您需要列出所有選項(xiàng)或選中所有選項(xiàng)。

表現(xiàn)

在性能方面，存儲(chǔ)過(guò)程中的動(dòng)態(tài) SQL可能會(huì)出現(xiàn)問(wèn)題。返回非常動(dòng)態(tài)數(shù)據(jù)的存儲(chǔ)過(guò)程可能會(huì)選擇錯(cuò)誤的查詢(xún)計(jì)劃，性能不佳。

*您可以重新編譯每個(gè)存儲(chǔ)過(guò)程調(diào)用，但這也有缺點(diǎn)。

表值函數(shù)

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

如上所述，它們具有存儲(chǔ)過(guò)程的優(yōu)點(diǎn)，但沒(méi)有數(shù)據(jù)庫(kù)中動(dòng)態(tài) SQL 的缺點(diǎn)！

示例 TVF 和 EF Core

有一個(gè)很好的表值函數(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：