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

Can an alien society believe that their star system is the universe?

How do I find out the mythology and history of my Fortress?

The code below, is it ill-formed NDR or is it well formed?

What is the difference between globalisation and imperialism?

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

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

NumericArray versus PackedArray in MMA12

What was the first language to use conditional keywords?

Is there any word for a place full of confusion?

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

Why aren't air breathing engines used as small first stages?

Why does the remaining Rebel fleet at the end of Rogue One seem dramatically larger than the one in A New Hope?

Drawing without replacement: why is the order of draw irrelevant?

Can the Great Weapon Master feat's "Power Attack" apply to attacks from the Spiritual Weapon spell?

Sum letters are not two different

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

If Windows 7 doesn't support WSL, then what does Linux subsystem option mean?

Question about debouncing - delay of state change

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

Performance gap between vector<bool> and array

Chinese Seal on silk painting - what does it mean?

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

How would a mousetrap for use in space work?

Do any jurisdictions seriously consider reclassifying social media websites as publishers?



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 57 mins ago

























                  answered 1 hour 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

                      Oświęcim Innehåll Historia | Källor | Externa länkar | Navigeringsmeny50°2′18″N 19°13′17″Ö / 50.03833°N 19.22139°Ö / 50.03833; 19.2213950°2′18″N 19°13′17″Ö / 50.03833°N 19.22139°Ö / 50.03833; 19.221393089658Nordisk familjebok, AuschwitzInsidan tro och existensJewish Community i OświęcimAuschwitz Jewish Center: MuseumAuschwitz Jewish Center

                      Valle di Casies Indice Geografia fisica | Origini del nome | Storia | Società | Amministrazione | Sport | Note | Bibliografia | Voci correlate | Altri progetti | Collegamenti esterni | Menu di navigazione46°46′N 12°11′E / 46.766667°N 12.183333°E46.766667; 12.183333 (Valle di Casies)46°46′N 12°11′E / 46.766667°N 12.183333°E46.766667; 12.183333 (Valle di Casies)Sito istituzionaleAstat Censimento della popolazione 2011 - Determinazione della consistenza dei tre gruppi linguistici della Provincia Autonoma di Bolzano-Alto Adige - giugno 2012Numeri e fattiValle di CasiesDato IstatTabella dei gradi/giorno dei Comuni italiani raggruppati per Regione e Provincia26 agosto 1993, n. 412Heraldry of the World: GsiesStatistiche I.StatValCasies.comWikimedia CommonsWikimedia CommonsValle di CasiesSito ufficialeValle di CasiesMM14870458910042978-6

                      Typsetting diagram chases (with TikZ?) Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)How to define the default vertical distance between nodes?Draw edge on arcNumerical conditional within tikz keys?TikZ: Drawing an arc from an intersection to an intersectionDrawing rectilinear curves in Tikz, aka an Etch-a-Sketch drawingLine up nested tikz enviroments or how to get rid of themHow to place nodes in an absolute coordinate system in tikzCommutative diagram with curve connecting between nodesTikz with standalone: pinning tikz coordinates to page cmDrawing a Decision Diagram with Tikz and layout manager