我有一個存盤程序，它以逗號分隔的字串作為輸入。有時可能太大，大約超過 8000 個字符或更多。在這種情況下，查詢性能有時會下降。而且我認為IN子句中的字符長度是有限制的。為此，有時我會出錯。現在，我需要知道使用自定義表型別作為引數并用于Inner JOIN查找結果是否更好。如果是，那是為什么。這是我的 2 個存盤程序（最小代碼）：
CREATE TYPE [dbo].[INTList] AS TABLE(
[ID] [int] NULL
)
程式1
CREATE PROCEDURE [report].[GetSKU]
@list [INTList] READONLY,
AS
Select sk.SKUID,sk.Code SCode,sk.SName
FROM SKUs sk
INNER JOIN @list sst ON sst.ID=sk.SKUID
程式2
CREATE PROCEDURE [report].[GetSKU]
@params varchar(max),
AS
Select sk.SKUID,sk.Code SCode,sk.SName
FROM SKUs sk
WHere CHARINDEX(',' cast( sk.SKUID as varchar(MAX)) ',', @params) > 0
現在，哪個程式更好用。
注意：原始存盤程序確實有更多的Joins.
uj5u.com熱心網友回復：
由于這個問題確實在評論中引起了相當多的討論，但沒有得到任何可行的答案，我想添加要點以幫助未來的研究。
這個問題是關于：如何將（大）值串列傳遞到查詢中？
在大多數情況下，人們需要在WHERE SomeColumn IN(SomeValueList)-filter 中使用它或JOIN使用類似FROM MyTable INNER JOIN SomeValueList ON....
非常重要的是 SQL-Server 的版本，與 v2016 一樣，我們有兩個很棒的工具：本機STRING_SPLIT()（不是位置安全的！）和 JSON 支持。
此外，相當明顯的是，我們必須考慮尺度和值。
- 我們是傳遞一些 ID 的簡單串列還是包含數千個值的龐大串列？
- 我們談論的是簡單的整數還是 GUID？
- 什么是文本值，我們必須在其中考慮危險字符（例如
[ { "在 JSON 或< &XML 中 - 還有更多......）？ - CSV 串列呢，分隔符可能出現在內容中（參考/轉義）？
- 在某些情況下，我們甚至可能希望一次傳遞幾列......
有幾種選擇：
- 表值引數 (TVP,
CREATE TYPE ...), - CSV 與字串拆分函式（自 v2016 以來的原生，各種自釀，CLR ...），
- 和基于文本的容器：XML 或 JSON（自 v2016 起）
表值引數（TVP - 最佳選擇）
表值引數 (TVP) 必須提前創建（這可能是一個缺點），但一旦創建，其行為將與任何其他表一樣。您可以添加索引，您可以在各種用例中使用它，而且您不必擔心任何幕后的事情。
有時我們無法使用它，因為缺少使用權CREATE TYPE...
字符分隔值 (CSV)
使用 CSV，我們看到了三種方法
動態 Sql：創建一個陳述句，其中 CSV 串列被簡單地塞入
IN()并動態執行。這可能是一種非常有效的方法，但會面臨各種障礙（沒有臨時使用、注入威脅、破壞錯誤值......）字串拆分函式：周圍有大量的例子......所有這些例子都有一個共同點，即分隔的字串將作為專案串列回傳。這里的常見問題：性能，缺少順序位置，對于分隔的限制，處理重復或空值，處理參考或轉義值，處理分離器內的內容。Aaron Bertrand對字串拆分的各種方法做了一些很棒的研究。與 TVP 類似的一個缺點可能是，該函式必須預先存在于資料庫中，否則我們需要被允許執行
CREATE FUNCTION。ad-hoc-splitters：在 v2016 之前，最常用的方法是基于 XML 的，從那時起我們已經轉向基于 JSON 的拆分器。兩者都使用一些字串方法將 CSV 字串轉換為 1) 分隔元素 (XML) 或 2) 轉換為 JSON 陣列。結果由 1) XQuery (
.value()and.nodes()) 或 2) JSON'sOPENJSON()or 查詢JSON_VALUE()。
基于文本的容器
我們可以將串列作為字串傳遞，但在定義的格式內：
- 使用
["a","b","c"]而不是a,b,c允許立即使用OPENJSON(). <x>a</x><x>b</x><x>c</x>相反，使用允許 XML 查詢。
這里最大的優勢是：任何編程語言都提供對這些格式的支持。
像日期和數字格式這樣的常見障礙得到了隱式解決。在大多數情況下，傳遞 JSON 或 XML 只是幾行代碼。
這兩種方法都允許型別和位置安全的查詢。
我們可以解決我們的需求，而無需依賴任何預先存在的東西。
uj5u.com熱心網友回復：
為了獲得最佳性能，您可以使用此功能：
CREATE FUNCTION [dbo].StringSplit
(
@String VARCHAR(MAX), @Separator CHAR(1)
)
RETURNS @RESULT TABLE(Value VARCHAR(MAX))
AS
BEGIN
DECLARE @SeparatorPosition INT = CHARINDEX(@Separator, @String ),
@Value VARCHAR(MAX), @StartPosition INT = 1
IF @SeparatorPosition = 0
BEGIN
INSERT INTO @RESULT VALUES(@String)
RETURN
END
SET @String = @String @Separator
WHILE @SeparatorPosition > 0
BEGIN
SET @Value = SUBSTRING(@String , @StartPosition, @SeparatorPosition- @StartPosition)
IF( @Value <> '' )
INSERT INTO @RESULT VALUES(@Value)
SET @StartPosition = @SeparatorPosition 1
SET @SeparatorPosition = CHARINDEX(@Separator, @String , @StartPosition)
END
RETURN
END
此函式回傳表 -select * from StringSplit('12,13,14,15,16', ',')因此您可以將此函式加入您的表或可以IN在 where 子句上使用。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/361937.html
標籤：sql sql-server 查询语句 存储过程 条款
上一篇：將值傳遞到T-SQL中的存盤程序