用户定义的函数替换WHERE col IN(...)
用户定义的函数替换WHERE col IN(...)
提问于 2009-03-26 12:26:49
我已经创建了一个用户定义的函数来提高查询的性能，这些查询包含“WHERE col (...)”就像这个例子：
SELECT myCol1, myCol2
FROM myTable
WHERE myCol3 IN (100, 200, 300, ..., 4900, 5000);查询是从web应用程序生成的，在某些情况下要复杂得多。函数定义如下所示：
CREATE FUNCTION [dbo].[udf_CSVtoIntTable]
(
@CSV VARCHAR(MAX),
@Delimiter CHAR(1) = ','
)
RETURNS
@Result TABLE
(
[Value] INT
)
AS
BEGIN
DECLARE @CurrStartPos SMALLINT;
SET @CurrStartPos = 1;
DECLARE @CurrEndPos SMALLINT;
SET @CurrEndPos = 1;
DECLARE @TotalLength SMALLINT;
-- Remove space, tab, linefeed, carrier return
SET @CSV = REPLACE(@CSV, ' ', '');
SET @CSV = REPLACE(@CSV, CHAR(9), '');
SET @CSV = REPLACE(@CSV, CHAR(10), '');
SET @CSV = REPLACE(@CSV, CHAR(13), '');
-- Add extra delimiter if needed
IF NOT RIGHT(@CSV, 1) = @Delimiter
SET @CSV = @CSV + @Delimiter;
-- Get total string length
SET @TotalLength = LEN(@CSV);
WHILE @CurrStartPos < @TotalLength
BEGIN
SET @CurrEndPos = CHARINDEX(@Delimiter, @CSV, @CurrStartPos);
INSERT INTO @Result
VALUES (CAST(SUBSTRING(@CSV, @CurrStartPos, @CurrEndPos - @CurrStartPos) AS INT));
SET @CurrStartPos = @CurrEndPos + 1;
END
RETURN
END该函数的用法如下(或作为内部连接)：
SELECT myCol1, myCol2
FROM myTable
WHERE myCol3 IN (
SELECT [Value]
FROM dbo.udf_CSVtoIntTable('100, 200, 300, ..., 4900, 5000', ',');有没有人对我的函数或其他方法有一些优化的想法来提高我的性能？有没有什么我没注意到的缺点？
我使用的是MS SQL Server2005STD和.NET 2.0框架。
回答 5
Stack Overflow用户
回答已采纳
发布于 2009-04-16 10:34:34
CLR解决方案没有给我带来良好的性能，所以我将使用递归查询。下面是我将使用的SP的定义(主要基于Erland Sommarskog示例)：
CREATE FUNCTION [dbo].[priudf_CSVtoIntTable]
(
@CSV VARCHAR(MAX),
@Delimiter CHAR(1) = ','
)
RETURNS
@Result TABLE
(
[Value] INT
)
AS
BEGIN
-- Remove space, tab, linefeed, carrier return
SET @CSV = REPLACE(@CSV, ' ', '');
SET @CSV = REPLACE(@CSV, CHAR(9), '');
SET @CSV = REPLACE(@CSV, CHAR(10), '');
SET @CSV = REPLACE(@CSV, CHAR(13), '');
WITH csvtbl(start, stop) AS
(
SELECT start = CONVERT(BIGINT, 1),
stop = CHARINDEX(@Delimiter, @CSV + @Delimiter)
UNION ALL
SELECT start = stop + 1,
stop = CHARINDEX(@Delimiter, @CSV + @Delimiter, stop + 1)
FROM csvtbl
WHERE stop > 0
)
INSERT INTO @Result
SELECT CAST(SUBSTRING(@CSV, start, CASE WHEN stop > 0 THEN stop - start ELSE 0 END) AS INT) AS [Value]
FROM csvtbl
WHERE stop > 0
OPTION (MAXRECURSION 1000)
RETURN
ENDStack Overflow用户
发布于 2009-03-26 12:32:25
我不确定性能是否会提高，但我会将其用作内部连接，并避免使用内部select语句。
Stack Overflow用户
发布于 2009-03-26 12:53:58
在WHERE子句或(更糟糕的)子查询中使用UDF是自找麻烦。优化器有时会得到正确的结果，但经常会出现错误，并为查询中的每一行计算一次函数，这是您不希望看到的。
如果您的参数是静态的(看起来是静态的)，并且您可以发出多语句批处理，那么我会将UDF的结果加载到一个表变量中，然后对表变量使用连接来进行过滤。这应该会更可靠地工作。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/685542
复制相关文章
相似问题
腾讯云开发者
