10

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.

Improve this question
2
  • 3
    What does the query plan say?
    – David Browne - Microsoft
    Commented Apr 18, 2019 at 21:28
  • 2
    Aside from there being a big difference between an in-line TVF and a multi-statement TVF, if your TVF returns the same row for every row in the outer query (since it only takes constants), and you only care about one output column, why are you putting it in a subquery in the select list? This just makes it possible to execute over and over again for no reason. Assign the output to a variable and then use the variable in your query.
    – Aaron Bertrand
    Commented Apr 19, 2019 at 0:09

3 Answers 3

Reset to default
5

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);
Improve this answer
11

Forrest is mostly right, but the finer details are:

SQL Server can't parallelize modifications to table variables, which your function uses.

Prior to SQL Server 2017's Interleaved Execution, row estimates from Multi-Statement Table Valued Functions were very low.

One side effect of this is that plans were costed very poorly on the low end, and often wouldn't break cost threshold for parallelism.

Improve this answer
3
  • 1
    OkayCan you suggets any alternate solution so that my main query can run in parallel mode.Looking at my scalar funciton ,is it possible to convert my scalar function to Inline TVF?
    – user9516827
    Commented Apr 19, 2019 at 14:01
  • 1
    @user9516827 you could probably chain some CTEs together to do something similar, but I have no idea if it would go parallel, perform better, etc. Testing it out is up to you.
    – Erik Reasonable Rates Darling
    Commented Apr 19, 2019 at 14:05
  • @MartinSmith : My actual query is a very complex query with lots of join and joins to views etc.This function is used for a select column in the main query and that is why i was trying to get that fixed.Also this is a LINQ-SQL(exec sp_executesql form) generated query and i have the limitation of not hacking the .net code.Thanks
    – user9516827
    Commented Apr 19, 2019 at 15:46
10

SQL Server cannot parallelize multi-statement TVFs, which is what yours is. Only Inline TVFs can be parallelized.

Improve this answer
1
  • 1
    Since i have variables in there i am not able to convert the scalar function to Inline TVF?
    – user9516827
    Commented Apr 19, 2019 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.