用户定义的函数替换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屋！