Function is slow but query runs fast

Question

I have a simple Table-Valued function that takes around 5 second to execute. The function holds a query which returns the data in 1 sec. I have read through some blogs where it is said that this might be due to parameter sniffing but couldn't find a resolution yet. How can I fix the function if it is due to parameter sniffing?

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
        BuyerName=@Name
        )
INSERT INTO @result
SELECT
    ID,
    Name,
    BasePrice,
    BasePrice*10.25
FROM
    CTE_Purchase
RETURN;

END

Answer 1

All of the examples here seem to be SQL Server Functions which just do a simple SELECT statement.

I had some SQL which did some pre-processing using temporary tables before doing a main SELECT command, but it ran incredibly quickly (1-2 seconds over 50,000 records). However, when I put it into a SQL Server Function, the same SQL took several minutes to run.

My solution (which no one else has suggested) is to try to put that same SQL into a Stored Procedure instead.

For certain situations, it's an incredibly simple change to try out, and the performance results are impressive.

Answer 2

If parameter sniffing is happening it's the least of your worries - Sean hit nail on the head when saying that Multi-statement Table Valued Functions (mTVFs) should be avoided like the plague. By design, they're going to be much slower than an inline Table Valued Function (iTVF) in that you define a table, populate it, then return it. iTVF's, on the other hand, can be thought of as views that accept parameters and returns data directly from the underlying tables.

Another HUGE problem with mTVFs is that they kill parallelism; this means that if you have 2 CPUS or 2,000 CPUs only only ONE will work on resolving your query. No exceptions. Looks have a look at Jeff Moden's 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

Now let's build an mTVF version like so and do a performance test...

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

Before continuing I want to address @John Cappelletti 's statement:

I've seen claims like this before [about MAX data types], but I've yet to see any compelling stats

For some compelling stats let's make a minor tweek to the iTVF version of delimitedSplit8K and change the input string to varchar(max):

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

Now we have three versions of the function: the original iTVF, one that accepts varchar(max) and an mTVF version. Now a performance test.

-- 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

and the results:

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.

Both the mTVF and iTVF version that takes varchar(max) are 4-5 times slower. Again: Avoid mTVFs like the plague and avoid max data types whenever possible.

Answer 3

Why not a single-statement 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 BuyerName=@Name
)