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

The performance of a multi-statement table valued function is generally even worse than scalar functions. They are simply dreadful and should be avoided like the plague. Also, since this appears to be financial application of some sort I would strongly suggest you stop the bad habit of splattering NOLOCK hints everywhere. That hint has a LOT of baggage that most people don't realize. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Jun 01 '17 at 18:20
I would also suggest using exact datatype for monetary values instead of approximate datatypes like float. Last but not least, you should post your actual function, not the dummy function that you stated in a comment below that this is. — Sean Lange, Jun 01 '17 at 18:22
I've solved this by removing CTEs from the TVFs. I suspect the CTEs turn a single statement query into a stealth multi-statement query. — Russell Fox, Apr 22 '20 at 22:56

score 2 · Answer 1 · answered Jun 01 '17 at 17:36

2

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
)

answered Jun 01 '17 at 17:36

John Cappelletti

79,615
7
44
66

I have removed the actual business logic from the function and created a sample function. I need to use IF inside the function hence MSTVF. – Amaan Khan Jun 01 '17 at 17:43
@AmaanKhan Sorry, but without seeing the actual function, I don't see how anyone can make an informed suggestion. – John Cappelletti Jun 01 '17 at 18:59
A minor point. Why use nvarchar(max) for @name? LOB data is stored in a completely separate data page and will slow down the query. Wouldn't nvarchar(4000) be plenty? If so, that will speed things up. It's not the root of the problem, but would definitely increase performance. – Robert Sievers Jun 01 '17 at 20:36
1

@RobertSievers I've seen claims like this before, but I've yet to see any compelling stats. – John Cappelletti Jun 01 '17 at 21:11
@JohnCappelletti, I think the lack of stats may be due to the variability of the conditions. For example, in my case, I had a view with about 30 columns, about 10K rows, about 800 bytes/row on average. This was running on a server with no SSD drive, nor latest speedy processors. Changing one of two LOB data fields to varchar(200) gave me a 15% improvement on speed. Not great, but not terrible either. I would expect YMMV. – Robert Sievers Jun 07 '17 at 14:38

score 1 · Answer 2 · answered Jun 02 '17 at 17:59

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.

Function is slow but query runs fast

2 Answers2

Linked