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

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

Question about debouncing - delay of state change

When a candle burns, why does the top of wick glow if bottom of flame is hottest?

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

Time to Settle Down!

Hangman Game with C++

How to write the following sign?

Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?

Project Euler #1 in C++

What is the difference between globalisation and imperialism?

What is the topology associated with the algebras for the ultrafilter monad?

Effects on objects due to a brief relocation of massive amounts of mass

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

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

How to react to hostile behavior from a senior developer?

Do wooden building fires get hotter than 600°C?

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

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

Is a ledger board required if the side of my house is wood?

How do living politicians protect their readily obtainable signatures from misuse?

Chinese Seal on silk painting - what does it mean?

Trademark violation for app?

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

How come Sam didn't become Lord of Horn Hill?



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
    3 hours ago











  • Aside from there being a big difference between an in-line TVF and a multi-statement TVF, if your TVF returns the same row for every row in the outer query (since it only takes constants), and you only care about one output column, why are you putting it in a subquery in the select list? This just makes it possible to execute over and over again for no reason. Assign the output to a variable and then use the variable in your query.

    – Aaron Bertrand
    20 mins 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
    3 hours ago











  • Aside from there being a big difference between an in-line TVF and a multi-statement TVF, if your TVF returns the same row for every row in the outer query (since it only takes constants), and you only care about one output column, why are you putting it in a subquery in the select list? This just makes it possible to execute over and over again for no reason. Assign the output to a variable and then use the variable in your query.

    – Aaron Bertrand
    20 mins 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 3 hours ago









user9516827user9516827

369110




369110







  • 1





    What does the query plan say?

    – David Browne - Microsoft
    3 hours ago











  • Aside from there being a big difference between an in-line TVF and a multi-statement TVF, if your TVF returns the same row for every row in the outer query (since it only takes constants), and you only care about one output column, why are you putting it in a subquery in the select list? This just makes it possible to execute over and over again for no reason. Assign the output to a variable and then use the variable in your query.

    – Aaron Bertrand
    20 mins ago












  • 1





    What does the query plan say?

    – David Browne - Microsoft
    3 hours ago











  • Aside from there being a big difference between an in-line TVF and a multi-statement TVF, if your TVF returns the same row for every row in the outer query (since it only takes constants), and you only care about one output column, why are you putting it in a subquery in the select list? This just makes it possible to execute over and over again for no reason. Assign the output to a variable and then use the variable in your query.

    – Aaron Bertrand
    20 mins ago







1




1





What does the query plan say?

– David Browne - Microsoft
3 hours ago





What does the query plan say?

– David Browne - Microsoft
3 hours ago













Aside from there being a big difference between an in-line TVF and a multi-statement TVF, if your TVF returns the same row for every row in the outer query (since it only takes constants), and you only care about one output column, why are you putting it in a subquery in the select list? This just makes it possible to execute over and over again for no reason. Assign the output to a variable and then use the variable in your query.

– Aaron Bertrand
20 mins ago





Aside from there being a big difference between an in-line TVF and a multi-statement TVF, if your TVF returns the same row for every row in the outer query (since it only takes constants), and you only care about one output column, why are you putting it in a subquery in the select list? This just makes it possible to execute over and over again for no reason. Assign the output to a variable and then use the variable in your query.

– Aaron Bertrand
20 mins ago










2 Answers
2






active

oldest

votes


















4














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






share|improve this answer






























    3














    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









      4














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






      share|improve this answer



























        4














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






        share|improve this answer

























          4












          4








          4







          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,5811820




          2,5811820























              3














              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





























                3














                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



























                  3












                  3








                  3







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

























                  answered 1 hour ago









                  Erik DarlingErik Darling

                  23k1369113




                  23k1369113



























                      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

                      Францішак Багушэвіч Змест Сям'я | Біяграфія | Творчасць | Мова Багушэвіча | Ацэнкі дзейнасці | Цікавыя факты | Спадчына | Выбраная бібліяграфія | Ушанаванне памяці | У філатэліі | Зноскі | Літаратура | Спасылкі | НавігацыяЛяхоўскі У. Рупіўся дзеля Бога і людзей: Жыццёвы шлях Лявона Вітан-Дубейкаўскага // Вольскі і Памідораў з песняй пра немца Адвакат, паэт, народны заступнік Ашмянскі веснікВ Минске появится площадь Богушевича и улица Сырокомли, Белорусская деловая газета, 19 июля 2001 г.Айцец беларускай нацыянальнай ідэі паўстаў у бронзе Сяргей Аляксандравіч Адашкевіч (1918, Мінск). 80-я гады. Бюст «Францішак Багушэвіч».Яўген Мікалаевіч Ціхановіч. «Партрэт Францішка Багушэвіча»Мікола Мікалаевіч Купава. «Партрэт зачынальніка новай беларускай літаратуры Францішка Багушэвіча»Уладзімір Іванавіч Мелехаў. На помніку «Змагарам за родную мову» Барэльеф «Францішак Багушэвіч»Памяць пра Багушэвіча на Віленшчыне Страчаная сталіца. Беларускія шыльды на вуліцах Вільні«Krynica». Ideologia i przywódcy białoruskiego katolicyzmuФранцішак БагушэвічТворы на knihi.comТворы Францішка Багушэвіча на bellib.byСодаль Уладзімір. Францішак Багушэвіч на Лідчыне;Луцкевіч Антон. Жыцьцё і творчасьць Фр. Багушэвіча ў успамінах ягоных сучасьнікаў // Запісы Беларускага Навуковага таварыства. Вільня, 1938. Сшытак 1. С. 16-34.Большая российская1188761710000 0000 5537 633Xn9209310021619551927869394п

                      Беларусь Змест Назва Гісторыя Геаграфія Сімволіка Дзяржаўны лад Палітычныя партыі Міжнароднае становішча і знешняя палітыка Адміністрацыйны падзел Насельніцтва Эканоміка Культура і грамадства Сацыяльная сфера Узброеныя сілы Заўвагі Літаратура Спасылкі НавігацыяHGЯOiТоп-2011 г. (па версіі ej.by)Топ-2013 г. (па версіі ej.by)Топ-2016 г. (па версіі ej.by)Топ-2017 г. (па версіі ej.by)Нацыянальны статыстычны камітэт Рэспублікі БеларусьШчыльнасць насельніцтва па краінахhttp://naviny.by/rubrics/society/2011/09/16/ic_articles_116_175144/А. Калечыц, У. Ксяндзоў. Спробы засялення краю неандэртальскім чалавекам.І ў Менску былі мамантыА. Калечыц, У. Ксяндзоў. Старажытны каменны век (палеаліт). Першапачатковае засяленне тэрыторыіГ. Штыхаў. Балты і славяне ў VI—VIII стст.М. Клімаў. Полацкае княства ў IX—XI стст.Г. Штыхаў, В. Ляўко. Палітычная гісторыя Полацкай зямліГ. Штыхаў. Дзяржаўны лад у землях-княствахГ. Штыхаў. Дзяржаўны лад у землях-княствахБеларускія землі ў складзе Вялікага Княства ЛітоўскагаЛюблінская унія 1569 г."The Early Stages of Independence"Zapomniane prawdy25 гадоў таму было аб'яўлена, што Язэп Пілсудскі — беларус (фота)Наша вадаДакументы ЧАЭС: Забруджванне тэрыторыі Беларусі « ЧАЭС Зона адчужэнняСведения о политических партиях, зарегистрированных в Республике Беларусь // Министерство юстиции Республики БеларусьСтатыстычны бюлетэнь „Полаўзроставая структура насельніцтва Рэспублікі Беларусь на 1 студзеня 2012 года і сярэднегадовая колькасць насельніцтва за 2011 год“Индекс человеческого развития Беларуси — не было бы нижеБеларусь занимает первое место в СНГ по индексу развития с учетом гендерного факцёраНацыянальны статыстычны камітэт Рэспублікі БеларусьКанстытуцыя РБ. Артыкул 17Трансфармацыйныя задачы БеларусіВыйсце з крызісу — далейшае рэфармаванне Беларускі рубель — сусветны лідар па дэвальвацыяхПра змену коштаў у кастрычніку 2011 г.Бядней за беларусаў у СНД толькі таджыкіСярэдні заробак у верасні дасягнуў 2,26 мільёна рублёўЭканомікаГаласуем за ТОП-100 беларускай прозыСучасныя беларускія мастакіАрхитектура Беларуси BELARUS.BYА. Каханоўскі. Культура Беларусі ўсярэдзіне XVII—XVIII ст.Анталогія беларускай народнай песні, гуказапісы спеваўБеларускія Музычныя IнструментыБеларускі рок, які мы страцілі. Топ-10 гуртоў«Мясцовы час» — нязгаслая легенда беларускай рок-музыкіСЯРГЕЙ БУДКІН. МЫ НЯ ЗНАЕМ СВАЁЙ МУЗЫКІМ. А. Каладзінскі. НАРОДНЫ ТЭАТРМагнацкія культурныя цэнтрыПублічная дыскусія «Беларуская новая пьеса: без беларускай мовы ці беларуская?»Беларускія драматургі па-ранейшаму лепш ставяцца за мяжой, чым на радзіме«Працэс незалежнага кіно пайшоў, і дзяржаву турбуе яго непадкантрольнасць»Беларускія філосафы ў пошуках прасторыВсе идём в библиотекуАрхіваванаАб Нацыянальнай праграме даследавання і выкарыстання касмічнай прасторы ў мірных мэтах на 2008—2012 гадыУ космас — разам.У суседнім з Барысаўскім раёне пабудуюць Камандна-вымяральны пунктСвяты і абрады беларусаў«Мірныя бульбашы з малой краіны» — 5 непраўдзівых стэрэатыпаў пра БеларусьМ. Раманюк. Беларускае народнае адзеннеУ Беларусі скарачаецца колькасць злачынстваўЛукашэнка незадаволены мінскімі ўладамі Крадзяжы складаюць у Мінску каля 70% злачынстваў Узровень злачыннасці ў Мінскай вобласці — адзін з самых высокіх у краіне Генпракуратура аналізуе стан са злачыннасцю ў Беларусі па каэфіцыенце злачыннасці У Беларусі стабілізавалася крымінагеннае становішча, лічыць генпракурорЗамежнікі сталі здзяйсняць у Беларусі больш злачынстваўМУС Беларусі турбуе рост рэцыдыўнай злачыннасціЯ з ЖЭСа. Дазволіце вас абкрасці! Рэйтынг усіх службаў і падраздзяленняў ГУУС Мінгарвыканкама вырасАб КДБ РБГісторыя Аператыўна-аналітычнага цэнтра РБГісторыя ДКФРТаможняagentura.ruБеларусьBelarus.by — Афіцыйны сайт Рэспублікі БеларусьСайт урада БеларусіRadzima.org — Збор архітэктурных помнікаў, гісторыя Беларусі«Глобус Беларуси»Гербы и флаги БеларусиАсаблівасці каменнага веку на БеларусіА. Калечыц, У. Ксяндзоў. Старажытны каменны век (палеаліт). Першапачатковае засяленне тэрыторыіУ. Ксяндзоў. Сярэдні каменны век (мезаліт). Засяленне краю плямёнамі паляўнічых, рыбакоў і збіральнікаўА. Калечыц, М. Чарняўскі. Плямёны на тэрыторыі Беларусі ў новым каменным веку (неаліце)А. Калечыц, У. Ксяндзоў, М. Чарняўскі. Гаспадарчыя заняткі ў каменным векуЭ. Зайкоўскі. Духоўная культура ў каменным векуАсаблівасці бронзавага веку на БеларусіФарміраванне супольнасцей ранняга перыяду бронзавага векуФотографии БеларусиРоля беларускіх зямель ва ўтварэнні і ўмацаванні ВКЛВ. Фадзеева. З гісторыі развіцця беларускай народнай вышыўкіDMOZGran catalanaБольшая российскаяBritannica (анлайн)Швейцарскі гістарычны15325917611952699xDA123282154079143-90000 0001 2171 2080n9112870100577502ge128882171858027501086026362074122714179пппппп

                      ValueError: Expected n_neighbors <= n_samples, but n_samples = 1, n_neighbors = 6 (SMOTE) The 2019 Stack Overflow Developer Survey Results Are InCan SMOTE be applied over sequence of words (sentences)?ValueError when doing validation with random forestsSMOTE and multi class oversamplingLogic behind SMOTE-NC?ValueError: Error when checking target: expected dense_1 to have shape (7,) but got array with shape (1,)SmoteBoost: Should SMOTE be ran individually for each iteration/tree in the boosting?solving multi-class imbalance classification using smote and OSSUsing SMOTE for Synthetic Data generation to improve performance on unbalanced dataproblem of entry format for a simple model in KerasSVM SMOTE fit_resample() function runs forever with no result