Sql-server – Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

functionsparallelismperformanceperformance-tuningquery-performancesql server

One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the application.
So i converted those SCALAR functions to TVF functions,but still the query is running in serial mode.

Earlier i have did Scalar to TVF conversion in some other queries and it solved the problem of forced serial execution.

Here is the scalar function:

CREATE FUNCTION [dbo].[FindEventReviewDueDate]
(
       @EventNumber VARCHAR(20),
       @EventID VARCHAR(25),
          @EventIDDate BIT
)

RETURNS DateTime
AS
BEGIN

DECLARE @CurrentEventStatus VARCHAR(20)
DECLARE @EventDateTime DateTime
DECLARE @ReviewDueDate DateTime


SELECT @CurrentEventStatus = (SELECT cis.EventStatus
                                 FROM CurrentEventStatus cis 
                                 INNER JOIN Event1 r WITH (NOLOCK) ON (cis.Event1Id = r.Id)
                                 WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

SELECT @EventDateTime = (SELECT EventDateTime FROM Event1 r 
                          WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

IF @CurrentEventStatus IN ('0','6') AND EventIDDate = 1
BEGIN

       SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)

       WHILE @ReviewDueDate < getdate() 
             SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

       DECLARE @EventDateJournalDate DateTime

       SELECT @EventDateJournalDate = (SELECT TOP 1 ij.Date
                                       FROM EventPage_EventJournal ij 
                                       INNER JOIN EventJournalPages p ON ij.PageId = p.Id 
                                       INNER JOIN Journal f ON p.FormId = f.Id 
                                       INNER JOIN Event1 r WITH (NOLOCK) ON (f.Event1Id = r.Id)
                                       WHERE (r.EventNumber = @EventNumber AND r.EventID = @EventID) AND ij.ReviewType = 'Supervisor Monthly Review' ORDER BY ij.Date DESC)

      IF(DATEADD(DAY, 30, @EventDateTime) < getdate() AND
           (@EventDateJournalDate is null OR DATEADD(DAY, 30, @EventDateJournalDate) < getdate()) AND
              DATEADD(DAY, 14, @ReviewDueDate) > DATEADD(DAY, 30, getdate()))
                  SET @ReviewDueDate = DATEADD(DAY, -30, @ReviewDueDate)
         ELSE IF((@EventDateJournalDate is not null ) AND (DATEADD(DAY, 30, @EventDateJournalDate) >= @ReviewDueDate))
                  SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

END
RETURN @ReviewDueDate

END

Here is the converted TVF function.

CREATE FUNCTION [dbo].[FindEventReviewDueDate_test]
(
       @EventNumber VARCHAR(20),
       @EventID VARCHAR(25),
          @EventIDDate BIT
)

RETURNS @FunctionResultTableVairable TABLE (
 CurrentEventStatus varchar(20),
 Event1DateTime DateTime,
 ReviewDueDate DateTime
 )
AS 
BEGIN

DECLARE @CurrentEventStatus VARCHAR(20)
DECLARE @EventDateTime DateTime
DECLARE @ReviewDueDate DateTime


SELECT @CurrentEventStatus = (SELECT cis.EventStatus
                                 FROM CurrentEventStatus cis 
                                 INNER JOIN Event1 r WITH (NOLOCK) ON (cis.Event1Id = r.Id)
                                 WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

SELECT @EventDateTime = (SELECT EventDateTime FROM Event1 r 
                          WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

IF @CurrentEventStatus IN ('0','6') AND EventIDDate = 1
BEGIN

       SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)

       WHILE @ReviewDueDate < getdate() 
             SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

       DECLARE @EventDateJournalDate DateTime

       SELECT @EventDateJournalDate = (SELECT TOP 1 ij.Date
                                       FROM EventPage_EventJournal ij 
                                       INNER JOIN EventJournalPages p ON ij.PageId = p.Id 
                                       INNER JOIN Journal f ON p.FormId = f.Id 
                                       INNER JOIN Event1 r WITH (NOLOCK) ON (f.Event1Id = r.Id)
                                       WHERE (r.EventNumber = @EventNumber AND r.EventID = @EventID) AND ij.ReviewType = 'Supervisor Monthly Review' ORDER BY ij.Date DESC)

      IF(DATEADD(DAY, 30, @EventDateTime) < getdate() AND
           (@EventDateJournalDate is null OR DATEADD(DAY, 30, @EventDateJournalDate) < getdate()) AND
              DATEADD(DAY, 14, @ReviewDueDate) > DATEADD(DAY, 30, getdate()))
                  SET @ReviewDueDate = DATEADD(DAY, -30, @ReviewDueDate)
         ELSE IF((@EventDateJournalDate is not null ) AND (DATEADD(DAY, 30, @EventDateJournalDate) >= @ReviewDueDate))
                  SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)
                   insert into @FunctionResultTableVairable
      select @CurrentEventStatus,@EventDateTime,@ReviewDueDate          

END
return;
END

GO

Is there anything wrong with my implementation of TVF function which is preventing the query to run in parallel mode.

I use the TVF function in the query as below;

select ReviewDueDate from dbo.FunctionResultTableVairable('a','b','c')

My actual query which uses the view is quite complex and if i comment out the function part in the view and on executing , the query runs in parallel.So it is function which is forcing the query to run in parallel.

My actual query is in the below format.

select 
dv.column1,
dv.column2,
---------
---------
--------
(select ReviewDueDate from dbo.FunctionResultTableVairable('a','b','c')) AS 'Columnx'
from
DemoView dv
Where 
condition1
conditon 2

Any help is appreciated.

Best Answer

is it possible to convert my scalar function to Inline TVF?

Yes. Something like the below would do it.

It is still pretty hefty and if run correlated would likely be quite inefficient. As Aaron points out in the comments you are calling this with constant values though so hopefully the query plan reflects this and only runs it once.

CREATE FUNCTION [dbo].[FindEventReviewDueDateInline] (@EventNumber VARCHAR(20),
                                                      @EventID     VARCHAR(25),
                                                      @EventIDDate BIT)
RETURNS TABLE
AS
    RETURN
      WITH X
           AS (SELECT cis.EventStatus AS CurrentEventStatus,
                      r.EventDateTime
               FROM   CurrentEventStatus cis
                      INNER JOIN Event1 r
                              ON cis.Event1Id = r.Id
               WHERE  r.EventNumber = @EventNumber
                      AND r.EventID = @EventID
                      AND cis.EventStatus IN ( '0', '6' )
                      AND @EventIDDate = 1)
      SELECT X.CurrentEventStatus,
             X.EventDateTime,
             CA4.ReviewDueDate
      FROM   X
             --SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)
             CROSS APPLY(VALUES(DATEADD(DAY, 30, X.EventDateTime))) CA1(ReviewDueDate)
             -- WHILE @ReviewDueDate < getdate() 
             --       SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)
             CROSS APPLY(VALUES( IIF(CA1.ReviewDueDate >= GETDATE(), CA1.ReviewDueDate, DATEADD(DAY, 30 * CEILING(( IIF(CAST(GETDATE() AS TIME) > CAST(CA1.ReviewDueDate AS TIME), 1, 0)
                                                                                                           + DATEDIFF(DAY, CA1.ReviewDueDate, GETDATE()) ) / 30.0), CA1.ReviewDueDate)))) CA2(ReviewDueDate)
             --SELECT @EventDateJournalDate = ....
             CROSS APPLY(SELECT TOP 1 ij.Date
                         FROM   EventPage_EventJournal ij
                                INNER JOIN EventJournalPages p
                                        ON ij.PageId = p.Id
                                INNER JOIN Journal f
                                        ON p.FormId = f.Id
                                INNER JOIN Event1 r WITH (NOLOCK)
                                        ON ( f.Event1Id = r.Id )
                         WHERE  ( r.EventNumber = @EventNumber
                                  AND r.EventID = @EventID )
                                AND ij.ReviewType = 'Supervisor Monthly Review'
                         ORDER  BY ij.Date DESC) CA3(EventDateJournalDate)
             -- IF(DATEADD(DAY, 30, @EventDateTime) < getdate()
             CROSS APPLY(VALUES ( CASE
                          WHEN ( DATEADD(DAY, 30, X.EventDateTime) < GETDATE()
                                 AND ( CA3.EventDateJournalDate IS NULL
                                        OR DATEADD(DAY, 30, CA3.EventDateJournalDate) < GETDATE() )
                                 AND DATEADD(DAY, 14, CA2.ReviewDueDate) > DATEADD(DAY, 30, GETDATE()) )
                            THEN DATEADD(DAY, -30, CA2.ReviewDueDate)
                          WHEN( ( CA3.EventDateJournalDate IS NOT NULL )
                                AND ( DATEADD(DAY, 30, CA3.EventDateJournalDate) >= CA2.ReviewDueDate ) )
                            THEN DATEADD(DAY, 30, CA2.ReviewDueDate)
                          ELSE CA2.ReviewDueDate
                        END )) CA4(ReviewDueDate);

Related Solutions

Sql-server – Use NOLOCK hint when calling table valued function

You cannot use a WITH (NOLOCK) on a Table-Valued Function, unless you put it on every single table in the code inside the function.

Your best bet would be, like you said, to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

To change this back to the default, you need to find out what isolation level is currently set (before changing it above).

This can be done by running DBCC USEROPTIONS and checking the value for isolation level.

The default tends to be READ COMMITTED, so to change it to that, you'll want to write a statement like the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

Create a new, blank database for our tests:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
BEGIN
    ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE mv;
END
GO
CREATE DATABASE mv;
GO

Create a table, a multi-statement function, and a table-valued-function:

USE mv;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT PK_t
        PRIMARY KEY CLUSTERED
);
GO

CREATE FUNCTION dbo.t_func
(
    @t_id int
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @r bit;
    IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
        SET @r = 1
    ELSE
        SET @r = 0;
    RETURN @r;
END
GO

CREATE FUNCTION dbo.t_tvf
(
    @min_t_id int
    , @max_t_id int
)
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN (
    SELECT t_id = t.t_id
        , e = dbo.t_func(dbo.t.t_id)
    FROM dbo.t
    WHERE t.t_id >= @min_t_id
        AND t.t_id <= @max_t_id
);
GO

Insert some sample data into the table:

INSERT INTO dbo.t (t_id)
SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
FROM sys.syscolumns c;
GO

Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

CREATE TABLE dbo.function_stats
(
    run_num int NOT NULL
    , object_name sysname NOT NULL
    , execution_count int NULL 
    , CONSTRAINT PK_function_stats
        PRIMARY KEY CLUSTERED (run_num, object_name)
);
GO
INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 1
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o 
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';
GO

Run a query against the TVF:
```
SELECT t.*
FROM dbo.t_tvf(1, 2) t;
GO
```

Capture the execution stats now:

INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 2
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o 
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';

The function stats results:

SELECT *
FROM dbo.function_stats fs
ORDER BY fs.run_num
    , fs.object_name;

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.

Best Answer

Related Solutions

Sql-server – Use NOLOCK hint when calling table valued function

Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF

Related Question