Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)SQL Server 2017: How does Interleaved Execution work?Why would call to scalar function inside a Table Value Function be slower than outside the TVF?Dynamic Condition Creation functionSQL Server 2014 - Compute scalar over computed indexed columnMultiple column concatenationMySQL query taking too longimprove mysql query in large tablesFind Max Value for each month for the last 3 months, properlyT-SQL Daylight Saving lookup table - poorly performing table-valued functionInvestigating errors from strange querySimilar query, run times much different

An adverb for when you're not exaggerating

Can anything be seen from the center of the Boötes void? How dark would it be?

As a beginner, should I get a Squier Strat with a SSS config or a HSS?

Why does it sometimes sound good to play a grace note as a lead in to a note in a melody?

Did Krishna say in Bhagavad Gita "I am in every living being"

Most bit efficient text communication method?

Putting class ranking in CV, but against dept guidelines

What is this clumpy 20-30cm high yellow-flowered plant?

Is it possible for SQL statements to execute concurrently within a single session in SQL Server?

Crossing US/Canada Border for less than 24 hours

Take 2! Is this homebrew Lady of Pain warlock patron balanced?

Should I use a zero-interest credit card for a large one-time purchase?

Is grep documentation about ignoring case wrong, since it doesn't ignore case in filenames?

How to write the following sign?

Hangman Game with C++

Is there a kind of relay only consumes power when switching?

Central Vacuuming: Is it worth it, and how does it compare to normal vacuuming?

Did Deadpool rescue all of the X-Force?

Disembodied hand growing fangs

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

How fail-safe is nr as stop bytes?

How do I use the new nonlinear finite element in Mathematica 12 for this equation?

SF book about people trapped in a series of worlds they imagine

How does light 'choose' between wave and particle behaviour?



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



Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)SQL Server 2017: How does Interleaved Execution work?Why would call to scalar function inside a Table Value Function be slower than outside the TVF?Dynamic Condition Creation functionSQL Server 2014 - Compute scalar over computed indexed columnMultiple column concatenationMySQL query taking too longimprove mysql query in large tablesFind Max Value for each month for the last 3 months, properlyT-SQL Daylight Saving lookup table - poorly performing table-valued functionInvestigating errors from strange querySimilar query, run times much different



.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








1















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.










share|improve this question

















  • 1





    What does the query plan say?

    – David Browne - Microsoft
    2 hours ago

















1















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.










share|improve this question

















  • 1





    What does the query plan say?

    – David Browne - Microsoft
    2 hours ago













1












1








1








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.










share|improve this question














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.







sql-server query-performance performance-tuning parallelism scalar-function






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked 2 hours ago









user9516827user9516827

369110




369110







  • 1





    What does the query plan say?

    – David Browne - Microsoft
    2 hours ago












  • 1





    What does the query plan say?

    – David Browne - Microsoft
    2 hours ago







1




1





What does the query plan say?

– David Browne - Microsoft
2 hours ago





What does the query plan say?

– David Browne - Microsoft
2 hours ago










2 Answers
2






active

oldest

votes


















3














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






share|improve this answer






























    2














    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.






    share|improve this answer

























      Your Answer








      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "182"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235227%2fconverted-a-scalar-function-to-a-tvf-function-for-parallel-execution-still-runni%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      3














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






      share|improve this answer



























        3














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






        share|improve this answer

























          3












          3








          3







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






          share|improve this answer













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







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 2 hours ago









          ForrestForrest

          2,5711820




          2,5711820























              2














              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.






              share|improve this answer





























                2














                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.






                share|improve this answer



























                  2












                  2








                  2







                  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.






                  share|improve this answer















                  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.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited 47 mins ago

























                  answered 58 mins ago









                  Erik DarlingErik Darling

                  22.9k1369113




                  22.9k1369113



























                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235227%2fconverted-a-scalar-function-to-a-tvf-function-for-parallel-execution-still-runni%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Isurus Índice Especies | Notas | Véxase tamén | Menú de navegación"A compendium of fossil marine animal genera (Chondrichthyes entry)"o orixinal"A review of the Tertiary fossil Cetacea (Mammalia) localities in wales port taf Museum Victoria"o orixinalThe Vertebrate Fauna of the Selma Formation of Alabama. Part VII. Part VIII. The Mosasaurs The Fishes50419737IDsh85068767Isurus2548834613242066569678159923NHMSYS00210535017845105743

                      Wolfenstein 3D Contents Availability Essential improvements Game data Video settings Input settings Audio settings Network VR support Issues fixed Other information System requirements NotesReferences    3D Realms Wolfenstein 3D pageGOG.com Community DiscussionsGOG.com Support PageSteam Community DiscussionsWolfenstein WikiOfficial websiteAmazon.comBethesda.netGamersGateGOG.comGreen Man GamingHumble StoreSteamweb browser versionWolfenstein 3D: Super UpgradesherehereUltraWolfhereWolfMenuECWolf Wolf4SDL WolfGL WinWolf3d NewWolf BetterWolf Sprite Fix and Rotation Project    Wolfenstein 3D VRSplitWolfWolfenstein 3D VRWolfenstein 3D VRWolfenstein 3D VR4DOS command shellFreeDOS's MORE.COMMacBin themthis shim fileWine regeditRELEASE: QUAKE II + III, WOLFENSTEIN 3D, RETURN TO CASTLE WOLFENSTEIN - GOG.com NewsMac Family - Wolfenstein Wiki - WikiaNerdly Pleasures: How many FPS? - DOS Games and Framerates

                      Король Коль Исторические данные | Стихотворение | Примечания | Навигацияверсии1 правкаверсии1 правкаA New interpretation of the 'Artognou' stone, TintagelTintagel IslandАрхивировано