功能很慢，但查询运行速度很快

问题描述：

我有一个简单的Table-Valued函数，需要大约5秒钟才能执行。该函数保存一个查询，它在1秒内返回数据。我读过一些博客，据说这可能是由于参数嗅探，但无法找到解决方案。如果由于参数嗅探导致该函数如何修复？功能很慢，但查询运行速度很快

CREATE FUNCTION [dbo].[fn_PurchaseRecord] 
(
@ID INT = NULL, 
@Name nvarchar(MAX), 
@PurchaseDate DATE 
) 
RETURNS @result TABLE 
(
[ID] [int] NULL, 
[Name] [varchar](20) NULL, 
[BasePrice] [FLOAT] NULL, 
[Amount] [FLOAT] 
) 

AS BEGIN 
WITH CTE_Purchase AS 
    (
    SELECT 
     ht.ID, 
     ProductName        AS Name, 
     BasePrice        AS BasePrice 
    FROM 
     data.PurchaseRecord i (NOLOCK) 
    WHERE 
     i.ID = @ID 
     AND 
     Date = @PurchaseDate 
     AND 
     [email protected] 
     ) 
INSERT INTO @result 
SELECT 
    ID, 
    Name, 
    BasePrice, 
    BasePrice*10.25 
FROM 
    CTE_Purchase 
RETURN;

END

你能发表功能代码吗？ –

@MikhailLobanov我刚刚添加了代码。 –

多语句表值函数的性能通常比标量函数更差。它们简直是可怕的，应该像瘟疫一样避免。此外，由于这似乎是某种财务应用程序，我强烈建议你停止在任何地方散布NOLOCK提示的坏习惯。这个暗示有很多人没有意识到的包袱。 http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ –

答

为什么不单语句TVF？

CREATE FUNCTION [dbo].[fn_PurchaseRecordTESTFIRST] 
(
@ID INT = NULL, 
@Name nvarchar(MAX), 
@PurchaseDate DATE 
) 
RETURNS TABLE 

Return (

    SELECT ID 
      ,Name = ProductName 
      ,BasePrice 
      ,Amount = BasePrice*10.25 
    FROM data.PurchaseRecord i 
    WHERE i.ID = @ID 
     AND Date = @PurchaseDate 
     AND [email protected] 
)

我已经从函数中删除了实际的业务逻辑并创建了一个示例函数。我需要在函数中使用IF，因此MSTVF。 –

@AmaanKhan对不起，但没有看到实际的功能，我没有看到任何人可以做出明智的建议。 –

小点。为什么在@name中使用nvarchar（max）？ LOB数据存储在完全独立的数据页面中，会减慢查询速度。 nvarchar（4000）不会很多吗？如果是这样，那会加快速度。这不是问题的根源，但肯定会提高性能。 –

答

如果参数嗅探是发生它是你最担心的 - 说多语句表值函数（mTVFs）应该避免像瘟疫时，肖恩击中头部钉。通过设计，它们将比内联表值函数（iTVF）慢得多，因为您可以定义一个表，填充它，然后返回它。另一方面，iTVF可以被认为是接受参数并直接从底层表返回数据的视图。

mTVFs的另一个巨大问题是它们会杀死并行性;这意味着如果您有2个CPUS或2,000个CPU，则只有一个将解决您的查询问题。没有例外。看起来看看杰夫MODEN的delimitedsplit8K：

CREATE FUNCTION [dbo].[DelimitedSplit8K] 
--===== Define I/O parameters 
     (@pString VARCHAR(8000), @pDelimiter CHAR(1)) 
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! 
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN 
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... 
    -- enough to cover VARCHAR(8000) 
    WITH E1(N) AS (
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
       ),       --10E+1 or 10 rows 
     E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows 
     E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max 
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front 
        -- for both a performance gain and prevention of accidental "overruns" 
       SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 
       ), 
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) 
       SELECT 1 UNION ALL 
       SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter 
       ), 
cteLen(N1,L1) AS(--==== Return start and length (for use in substring) 
       SELECT s.N1, 
         ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) 
        FROM cteStart s 
       ) 
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), 
     Item  = SUBSTRING(@pString, l.N1, l.L1) 
    FROM cteLen l; 
GO

现在让我们建立一个mTVF版本，像这样做性能测试...

CREATE FUNCTION [dbo].[DelimitedSplit8K_MTVF] 
     (@pString VARCHAR(8000), @pDelimiter CHAR(1)) 
RETURNS @table TABLE (ItemNumber int, Item varchar(100)) 
AS 
BEGIN 
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... 
    -- enough to cover VARCHAR(8000) 
    WITH E1(N) AS (
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
       ),       --10E+1 or 10 rows 
     E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows 
     E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max 
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front 
        -- for both a performance gain and prevention of accidental "overruns" 
       SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 
       ), 
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) 
       SELECT 1 UNION ALL 
       SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter 
       ), 
cteLen(N1,L1) AS(--==== Return start and length (for use in substring) 
       SELECT s.N1, 
         ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) 
        FROM cteStart s 
       ) 
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
INSERT @table 
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), 
     Item  = SUBSTRING(@pString, l.N1, l.L1) 
    FROM cteLen l; 

RETURN; 
END 
GO

继续我想解决@约翰卡佩莱蒂年代以前声明：

我见过的权利要求像在此之前[关于MAX数据类型]，但我还没有看到任何令人信服的统计

对于一些引人注目的统计让我们做一个小tweek到delimitedSplit8K的iTVF版本和改变输入字符串为varchar（最大）：

CREATE FUNCTION [dbo].[DelimitedSplit8K_VCMAXINPUT] 
     (@pString VARCHAR(max), @pDelimiter CHAR(1)) 
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN 
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... 
    -- enough to cover VARCHAR(8000) 
    WITH E1(N) AS (
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
       ),       --10E+1 or 10 rows 
     E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows 
     E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max 
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front 
        -- for both a performance gain and prevention of accidental "overruns" 
       SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 
       ), 
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) 
       SELECT 1 UNION ALL 
       SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter 
       ), 
cteLen(N1,L1) AS(--==== Return start and length (for use in substring) 
       SELECT s.N1, 
         ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) 
        FROM cteStart s 
       ) 
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), 
     Item  = SUBSTRING(@pString, l.N1, l.L1) 
    FROM cteLen l; 
GO

现在我们有功能的三个版本：原来iTVF，一个接受varchar（max）和mTVF版本。现在是一个性能测试。

-- sample data 
IF OBJECT_ID('tempdb..#string') IS NOT NULL DROP TABLE #string; 
SELECT TOP (10000) 
    id = IDENTITY(int, 1,1), 
    txt = REPLICATE(newid(), ABS(checksum(newid())%5)+1) 
INTO #string 
FROM sys.all_columns a, sys.all_columns b; 

SET NOCOUNT ON; 

-- Performance tests: 
PRINT 'ITVF 8K'+char(13)+char(10)+replicate('-',90); 
GO 
DECLARE @st datetime2 = getdate(), @x varchar(20); 
SELECT @x = ds.Item 
FROM #string s 
CROSS APPLY dbo.DelimitedSplit8K(s.txt, '-') ds; 
PRINT datediff(ms, @st, getdate()); 
GO 5 

PRINT 'MTVF 8K'+char(13)+char(10)+replicate('-',90); 
GO 
DECLARE @st datetime2 = getdate(), @x varchar(20); 
SELECT @x = ds.Item 
FROM #string s 
CROSS APPLY dbo.DelimitedSplit8K_MTVF(s.txt, '-') ds; 
PRINT datediff(ms, @st, getdate()); 
GO 5 

PRINT 'ITVF VCMAX'+char(13)+char(10)+replicate('-',90); 
GO 
DECLARE @st datetime2 = getdate(), @x varchar(20); 
SELECT @x = ds.Item 
FROM #string s 
CROSS APPLY dbo.DelimitedSplit8K_VCMAXINPUT(s.txt, '-') ds; 
PRINT datediff(ms, @st, getdate()); 
GO 5

和结果：

ITVF 8K 
------------------------------------------------------------------------------------------ 
Beginning execution loop 
280 
267 
284 
300 
280 
Batch execution completed 5 times. 

MTVF 8K 
------------------------------------------------------------------------------------------ 
Beginning execution loop 
1190 
1190 
1157 
1173 
1187 
Batch execution completed 5 times. 

ITVF VCMAX 
------------------------------------------------------------------------------------------ 
Beginning execution loop 
1204 
1220 
1190 
1190 
1203 
Batch execution completed 5 times.

两者mTVF和iTVF版本需要VARCHAR（最大）是4-5倍慢。再次：避免mTVFs像瘟疫，并尽可能避免最大数据类型。

功能很慢，但查询运行速度很快

相关推荐