我有一个存储过程,它将逗号分隔的字符串作为输入。有时可能太大,大约超过 8000 个字符或更多。在那种情况下,查询性能有时会下降。而且我认为 IN
子句中的字符长度有限制。为此,有时我会出错。现在,我需要知道使用 Custom TABLE TYPE 作为参数并使用 Inner JOIN
来查找结果是否更好。如果是那么为什么会这样。这是我的 2 个存储过程(最少代码):
CREATE TYPE [dbo].[INTList] AS TABLE(
[ID] [int] NULL
)
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
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
现在,哪个程序更好用。
注意:原始存储过程确实有几个Join
。
最佳答案
由于这个问题确实在评论中引起了很多讨论,但没有得到任何可行的答案,我想补充一下要点,以帮助以后的研究。
这个问题是关于:如何将(大)值列表传递到查询中?
在大多数情况下,人们在 WHERE SomeColumn IN(SomeValueList)
中需要这个-filter 或到 JOIN
用类似 FROM MyTable INNER JOIN SomeValueList ON...
的东西来反对这个.
非常重要的是 SQL-Server 的版本,与 v2016 一样,我们有两个很棒的工具:native STRING_SPLIT()
(位置不安全!)和 JSON 支持。
此外,很明显,我们必须考虑规模和值(value)。
[ { "
或 XML 中的 < &
- 还有更多......)?有几个选项:
CREATE TYPE ...
),表值参数(TVP - 最佳选择)
表值参数 (TVP) 必须提前创建(这可能是一个缺点),但一旦创建,其行为将与任何其他表相同。您可以添加索引,可以在各种用例中使用它,而不必为幕后的任何事情操心。
有时我们无法使用它,因为缺少使用权 CREATE TYPE
...
字符分隔值 (CSV)
对于 CSV,我们看到了三种方法
动态 Sql:创建一个语句,其中 CSV 列表被简单地填充到 IN()
中并动态执行。这可以是一种非常有效的方法,但会遇到各种障碍(没有临时 - 使用、注入(inject)威胁、破坏不良值(value)观......)
字符串拆分函数:周围有大量示例......所有这些函数的共同点是,分离的字符串将作为项目列表返回。此处的常见问题:性能、缺少序号位置、分隔符的限制、重复值或空值的处理、引号或转义值的处理、内容中分隔符的处理。 Aaron Bertrand 做了一些 great research关于字符串拆分的各种方法。与 TVPs 类似,一个缺点可能是,这个函数必须提前存在于数据库中,或者我们需要被允许执行 CREATE FUNCTION
如果没有。
ad-hoc-splitters:在 v2016 之前,最常用的方法是基于 XML,从那时起我们已经转向基于 JSON 的拆分器。两者都使用一些字符串方法将 CSV 字符串转换为 1) 分隔元素 (XML) 或 2) 转换为 JSON 数组。结果由 1) XQuery(.value()
和 .nodes()
)或 2) JSON 的 OPENJSON()
查询或 JSON_VALUE()
.
基于文本的容器
我们可以将列表作为字符串传递,但在定义的格式内:
["a","b","c"]
而不是 a,b,c
允许立即使用 OPENJSON()
.<x>a</x><x>b</x><x>c</x>
而是允许 XML 查询。这里最大的优势:任何编程语言都提供对这些格式的支持。
隐式解决了日期和数字格式等常见障碍。在大多数情况下,传递 JSON 或 XML 只是几行代码。
这两种方法都允许进行类型和位置安全的查询。
我们可以解决我们的需求,而无需依赖任何预先存在的东西。
关于sql - 传递大的逗号分隔值时,使用 Custom TABLE TYPE 作为参数而不是 SQL "IN"子句更好吗,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/70014709/
相关文章:
kotlin - 如何在 Kotlin Multiplatform 中获取特定于平台的换行符?
node.js - 如何解决错误 : Cannot find module 'express-rat
python - "' str ' object has no attribute ' 将 slug
amazon-web-services - 如何查看 AWS 备份库的大小?
python - Spyder 5.1.5 内核挂起,5.2.1 无法与 conda 一起使用
debugging - A/libc : Fatal signal 11 (SIGSEGV), 代码
python - 如何处理两个事件循环? Pyrogram 和 Tkinter 的
javascript - 按顺序播放多个音轨,而不是同时播放