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

What is the font for "b" letter?

Hangman Game with C++

Why is my ESD wriststrap failing with nitrile gloves on?

Localisation of Category

Denied boarding although I have proper visa and documentation. To whom should I make a complaint?

How fail-safe is nr as stop bytes?

Crossing US/Canada Border for less than 24 hours

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

How can I reduce the gap between left and right of cdot with a macro?

Why doesn't SQL Optimizer use my constraint?

What was the first language to use conditional keywords?

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

Is there hard evidence that the grant peer review system performs significantly better than random?

Performance gap between vector<bool> and array

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

Do wooden building fires get hotter than 600°C?

How to write the following sign?

What initially awakened the Balrog?

Generate an RGB colour grid

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

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

Should I follow up with an employee I believe overracted to a mistake I made?

How were pictures turned from film to a big picture in a picture frame before digital scanning?

Sum letters are not two different



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 1 hour 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 43 mins ago

























                  answered 54 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

                      Disable / Remove link to Product Items in Cart Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?How can I limit products that can be bought / added to cart?Remove item from cartHide “Add to Cart” button if specific products are already in cart“Prettifying” the custom options in cart pageCreate link in cart sidebar to view all added items After limit reachedLink products together in checkout/cartHow to Get product from cart and add it againHide action-edit on cart page if simple productRemoving Cart items - ObserverRemove wishlist items when added to cart

                      Helsingin valtaus Sisällysluettelo Taustaa | Yleistä sotatoimista | Osapuolet | Taistelut Helsingin ympäristössä | Punaisten antautumissuunnitelma | Taistelujen kulku Helsingissä | Valtauksen jälkeen | Tappiot | Muistaminen | Kirjallisuutta | Lähteet | Aiheesta muualla | NavigointivalikkoTeoksen verkkoversioTeoksen verkkoversioGoogle BooksSisällissota Helsingissä päättyi tasan 95 vuotta sittenSaksalaisten ylivoima jyräsi punaisen HelsinginSuomalaiset kuvaavat sotien jälkiä kaupungeissa – katso kuvat ja tarinat tutuilta kulmiltaHelsingin valtaus 90 vuotta sittenSaksalaiset valtasivat HelsinginHyökkäys HelsinkiinHelsingin valtaus 12.–13.4. 1918Saksalaiset käyttivät ihmiskilpiä Helsingin valtauksessa 1918Teoksen verkkoversioTeoksen verkkoversioSaksalaiset hyökkäävät Etelä-SuomeenTaistelut LeppävaarassaSotilaat ja taistelutLeppävaara 1918 huhtikuussa. KapinatarinaHelsingin taistelut 1918Saksalaisten voitonparaati HelsingissäHelsingin valtausta juhlittiinSaksalaisten Helsinki vuonna 1918Helsingin taistelussa kaatuneet valkokaartilaisetHelsinkiin haudatut taisteluissa kaatuneet punaiset12.4.1918 Helsingin valtauksessa saksalaiset apujoukot vapauttavat kaupunginVapaussodan muistomerkkejä Helsingissä ja pääkaupunkiseudullaCrescendo / Vuoden 1918 Kansalaissodan uhrien muistomerkkim

                      Adjektiivitarina Tarinan tekeminen | Esimerkki: ennen | Esimerkki: jälkeen | Navigointivalikko