SQL Server: правильная оценка строк из функции разбиения строк

Я использую SQL Server 2014. У меня есть функция табличного значения с несколькими операторами для разделения строки в таблицу через разделитель.

Я не разделяю длинные строки, но я использую эту функцию в хранимых процедурах, как это:

ALTER PROCEDURE dbo.example
  @parameters
AS
Begin
SELECT *
FROM TableA
LEFT JOIN Table B on B.ID = A.FID
WHERE B.ID IN (SELECT Data FROM dbo.fn_Split(@parameters, ',')
END

Реальный sproc имеет multi-соединяет и много параметров. Когда я смотрел на план выполнения, проверка таблицы оператора (fn_split) стоимостью 0% всегда возвращала неточную оценку строк. Для 11 параметров будет оценено 100 строк.

Я слышал, что функция табличного значения с несколькими операторами работает медленно, но с использованием встроенного,
XML или разделитель Джеффа Модена медленнее, чем мой исходный, при использовании в предложении WHERE. У них ужасные планы исполнения и худшие оценки строк

Есть ли способ получить правильную оценку строк при разбиении строки на таблицу?

Моя функция:

ALTER FUNCTION dbo.fn_Split(    
  @RowData NVARCHAR(MAX),
  @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE (
  ID INT IDENTITY(1,1),
  Data NVARCHAR(MAX)
) 
AS
BEGIN 
 DECLARE @Iterator INT
 SET @Iterator = 1
 DECLARE @FoundIndex INT
 SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
 WHILE (@FoundIndex>0)
 BEGIN
    INSERT INTO @RtnValue (data)
    SELECT 
        Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
    SET @RowData = SUBSTRING(@RowData,
            @FoundIndex + DATALENGTH(@Delimeter) / 2,
            LEN(@RowData))
    SET @Iterator = @Iterator + 1
    SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END
INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))
RETURN
END

2 ответа

  1. Как насчет использования временной таблицы?

    Begin
        create table #split (data varchar(255) primary key);
    
        insert into #split(data)
            select data
            from dbo.fn_Split(@parameters, ',');
    
        SELECT *
        FROM TableA LEFT JOIN
             Table B
             on B.ID = A.FID
        WHERE B.ID IN (SELECT Data FROM #split);
    
    END;
    
  2. Извинения necroing 6-месячный пост, но я думал, что OP все еще может слушать, и это может помочь другим в будущем.

    Я задокументировал большую часть того, что хотел сказать в комментариях в коде. Кажется, что это имеет больше смысла «insitu», как это.

    Однако, прежде чем мы начнем, любые претензии на производительность с моей или чьей-либо стороны должны быть тщательно рассмотрены, особенно без кодированных доказательств. Имея это в виду, давайте сначала построим пару миллионов строк тестовых таблиц, так что мы можем сделать некоторые проверки производительности и проверки.

    Вот код для тестовых таблиц…

    --=================================================================================================
    --      Create and populate the two test tables.
    --      Nothing in this section is a part of the solution. We're just setting up a test.
    --=================================================================================================
    --===== Drop the two test tables to make reruns in SSMS easier
         IF OBJECT_ID('dbo.TableA','U') IS NOT NULL DROP TABLE dbo.TableA;
         IF OBJECT_ID('dbo.TableB','U') IS NOT NULL DROP TABLE dbo.TableB;
    GO
    --===== Create the two test tables. I'm assuming some form of index on the 2 columns in question.
     CREATE TABLE dbo.TableA (FID BIGINT NOT NULL PRIMARY KEY);
     CREATE TABLE dbo.TableB (ID  BIGINT NOT NULL PRIMARY KEY);
    
    --===== Populate TableA with a sequence from 1 to a million.
     INSERT INTO dbo.TableA WITH(TABLOCK)
            (FID)
     SELECT TOP 1000000
            FID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
       FROM      sys.all_columns ac1 --CROSS JOIN acts like a looped rowsource (Pseudo-Cursor)
      CROSS JOIN sys.all_columns ac2 --and uses nothing from these tables except the presence of rows.
    ;
    --===== Populate TableB with a sequence from 1 to 10,000 with a couple of pieces missing that
         -- will appear in @Parameters for testing.
     INSERT INTO dbo.TableB WITH(TABLOCK)
            (ID)
     SELECT ID = FID
       FROM dbo.TableA
      WHERE FID NOT IN (7363,805,34) --We'll include these in @Parameters for testing.
    ;
    GO
    

    Теперь давайте рассмотрим, почему исходный код может быть настолько медленным, когда используется функция DelimitedSplit8K. Включите фактический план выполнения, выполните следующее и просмотрите комментарии в коде, чтобы понять, почему требуется почти 3 секунды для запуска даже на таких простых и хорошо индексированных таблицах.

    --=================================================================================================
    --      Let's see why the original query using the dbo.DelimitedSplit8K function might be so slow.
    --      After making minor corrections to the original code, here's what we end up with.  Run it
    --      with the actual execution plan and discover two things.
    --      1. The query is not SARGable and causes clustered index scans on both tables.
    --      2. Because of the way the query was designed, the TABLE SPOOL is actually the result of a
    --         CROSS JOIN between the 8 elements in the parameters and result of the JOIN between 
    --         TableA and TableB. In other words, it has to generate nearly 8 million internal rows in
    --         this case.  Imagine if you had 50 elements in your @Parameters.
    --
    --      Bottom line is that it's not the DelimitedSplit8K function that's slow. It's the way the
    --      optimizer used an iTVF (Inline Table Valued Function) in this case and WHERE IN can
    --      sometimes create a case of "HIDDEN RBAR".
    --
    --      P.S. The LEFT OUTER JOIN acts as if it where an inner join here because of the limit you've
    --      placed on the RIGHT table with the function.
    --=================================================================================================
    --===== Start measuring stuff. NEVER do this with a SCALAR or mTVF (Multi-statment Table Valued
         -- function because it will make them seem hundreds of times slower. The proof is too long to
         -- show here so here's a reference for you. If you don't want to join to read the article
         -- (not sure you need to anymore), then you'll have to test that on your own.
         -- http://www.sqlservercentral.com/articles/T-SQL/91724/ "How to make Scalar UDFs Run Faster"
        SET STATISTICS IO,TIME ON
    ;
    --===== Simulate the stored procedure having parameters passed to it.
    DECLARE @Parameters VARCHAR(8000);  
     SELECT @Parameters = '8097,345,7363,805,34,8745,13,987654'
    ;
         -- Corrected code from the original post using DelimitedSplit8K instead of fnSplit.
     SELECT *
       FROM      TableA a
       LEFT JOIN TableB b ON b.ID = a.FID
      WHERE b.ID IN (SELECT Item FROM dbo.DelimitedSplit8K(@parameters, ','))
    ;
    --===== Stop measuring stuff.
        SET STATISTICS IO,TIME OFF
    ;
    GO
    

    Вы можете увидеть случайное перекрестное соединение в» рабочем столе » в выводе статистики ниже. 2,000,054 считывает 16,38 ГБ данных, созданных внутри. Это занимает много времени.

    SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
    (5 row(s) affected)
    Table 'TableA'. Scan count 5, logical reads 2126, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TableB'. Scan count 5, logical reads 2309, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 4, logical reads 2000054, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:
       CPU time = 11670 ms,  elapsed time = 3163 ms.
    

    Как вы можете видеть, iTVFs (встроенные функции с табличным значением) могут быть немного привередливыми. Давайте посмотрим, что происходит, когда мы используем его в соединении. И проверка фактических подсчетов строк в фактическом плане выполнения по сравнению с исходным кодом выше.

    --=================================================================================================
    --      If we use the iTVF (DelimitedSplit8K) in a JOIN instead of in a WHERE, there is no 
    --      accidental CROSS JOIN because the results of the function are materialized just once.
    --      The results are returned more than 3100 times faster.
    --=================================================================================================
    --===== Start measuring stuff.
        SET STATISTICS IO,TIME ON
    ;
    --===== Simulate the stored procedure having parameters passed to it.
    DECLARE @Parameters VARCHAR(8000);  
     SELECT @Parameters = '8097,345,7363,805,34,8745,13,987654'
    ;
     SELECT a.*,b.* 
       FROM dbo.TableA a
       LEFT JOIN dbo.TableB b
         ON a.FID = b.ID --I make my ON statements look like the outer join for ease of reading
       JOIN (SELECT Item FROM dbo.DelimitedSplit8K(@parameters, ',')) s
         ON b.ID = s.Item
    ;
    --===== Stop measuring stuff.
        SET STATISTICS IO,TIME OFF
    ;
    GO
    

    Вот результаты выполнения для этого кода. Более чем в 3100 раз быстрее и более чем в 42 000 раз меньше операций ввода-вывода в виде логических операций чтения (даже производительность памяти улучшается на 5 порядков).

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
    (5 row(s) affected)
    Table 'TableA'. Scan count 0, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TableB'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 1 ms.
    

    p.s. Оценки того, сколько строк будет возвращено из каждой таблицы, по-прежнему только «1», вероятно, потому, что функция строит свою собственную Подсчетную структуру на лету. Не уверен, что это имеет значение в этом случае, хотя.