用户定义的函数替换WHERE col IN(...) [英] User defined function replacing WHERE col IN(...)
问题描述
我已经创建了一个用户定义的函数来通过包含"WHERE col IN(...)"的查询来提高性能,就像这种情况:
I have created a user defined function to gain performance with queries containing 'WHERE col IN (...)' like this case:
SELECT myCol1, myCol2
FROM myTable
WHERE myCol3 IN (100, 200, 300, ..., 4900, 5000);
查询是从Web应用程序生成的,在某些情况下更为复杂. 函数定义如下:
The queries are generated from an web application and are in some cases much more complex. The function definition looks like this:
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
该函数应按以下方式使用(或作为INNER JOIN使用):
The function is intended to be used like this (or as an INNER JOIN):
SELECT myCol1, myCol2
FROM myTable
WHERE myCol3 IN (
SELECT [Value]
FROM dbo.udf_CSVtoIntTable('100, 200, 300, ..., 4900, 5000', ',');
在我的案例中,有人对我的功能或其他提高性能的方式有一些优化的想法吗? 有什么我想念的缺点吗?
Do anyone have some optimiztion idears of my function or other ways to improve performance in my case? Is there any drawbacks that I have missed?
我正在使用MS SQL Server 2005 Std和.NET 2.0框架.
I am using MS SQL Server 2005 Std and .NET 2.0 framework.
推荐答案
CLR解决方案的性能不佳,因此我将使用递归查询.因此,这是我将使用的SP的定义(主要基于Erland Sommarskogs的示例):
The CLR solution did not give me an good performance so I will use a recursive query. So here is the definition of the SP I will use (mostly based on Erland Sommarskogs examples):
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
END
这篇关于用户定义的函数替换WHERE col IN(...)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!