Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode ...
Why is it faster to reheat something than it is to cook it?
Is it a good idea to use CNN to classify 1D signal?
Is it fair for a professor to grade us on the possession of past papers?
Why does it sometimes sound good to play a grace note as a lead in to a note in a melody?
What is the difference between globalisation and imperialism?
How to react to hostile behavior from a senior developer?
Can anything be seen from the center of the Boötes void? How dark would it be?
ArcGIS Pro Python arcpy.CreatePersonalGDB_management
How come Sam didn't become Lord of Horn Hill?
A term for a woman complaining about things/begging in a cute/childish way
How to play a character with a disability or mental disorder without being offensive?
Central Vacuuming: Is it worth it, and how does it compare to normal vacuuming?
Drawing without replacement: why is the order of draw irrelevant?
Do any jurisdictions seriously consider reclassifying social media websites as publishers?
What is this clumpy 20-30cm high yellow-flowered plant?
Is it possible for SQL statements to execute concurrently within a single session in SQL Server?
Is there a kind of relay that only consumes power when switching?
Most bit efficient text communication method?
SF book about people trapped in a series of worlds they imagine
Why do we bend a book to keep it straight?
Why is Nikon 1.4g better when Nikon 1.8g is sharper?
How to compare two different files line by line in unix?
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?
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;
}
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
asked 8 hours ago
user9516827user9516827
374110
add a comment |
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
asked 8 hours ago
user9516827user9516827
374110
1
What does the query plan say?
– David Browne - Microsoft
8 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♦
6 hours ago
add a comment |
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
asked 8 hours ago
user9516827user9516827
374110
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
sql-server query-performance performance-tuning parallelism scalar-function
asked 8 hours ago
user9516827user9516827
374110
asked 8 hours ago
user9516827user9516827
374110
asked 8 hours ago
user9516827user9516827
374110
asked 8 hours ago
user9516827user9516827
374110
asked 8 hours ago
user9516827user9516827
374110
374110
1
What does the query plan say?
– David Browne - Microsoft
8 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♦
6 hours ago
add a comment |
1
What does the query plan say?
– David Browne - Microsoft
8 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♦
6 hours ago
1
1
What does the query plan say?
– David Browne - Microsoft
8 hours ago
What does the query plan say?
– David Browne - Microsoft
8 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♦
6 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♦
6 hours ago
add a comment |
2 Answers
2
active
oldest
votes
SQL Server cannot parallelize multi-statement TVFs, which is what yours is. Only Inline TVFs can be parallelized.
answered 8 hours ago
ForrestForrest
2,5911820
add a comment |
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.
answered 7 hours ago
Erik DarlingErik Darling
23k1369113
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
SQL Server cannot parallelize multi-statement TVFs, which is what yours is. Only Inline TVFs can be parallelized.
answered 8 hours ago
ForrestForrest
2,5911820
add a comment |
SQL Server cannot parallelize multi-statement TVFs, which is what yours is. Only Inline TVFs can be parallelized.
answered 8 hours ago
ForrestForrest
2,5911820
add a comment |
SQL Server cannot parallelize multi-statement TVFs, which is what yours is. Only Inline TVFs can be parallelized.
answered 8 hours ago
ForrestForrest
2,5911820
SQL Server cannot parallelize multi-statement TVFs, which is what yours is. Only Inline TVFs can be parallelized.
answered 8 hours ago
ForrestForrest
2,5911820
answered 8 hours ago
ForrestForrest
2,5911820
answered 8 hours ago
ForrestForrest
2,5911820
answered 8 hours ago
ForrestForrest
2,5911820
2,5911820
add a comment |
add a comment |
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.
answered 7 hours ago
Erik DarlingErik Darling
23k1369113
add a comment |
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.
answered 7 hours ago
Erik DarlingErik Darling
23k1369113
add a comment |
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.
answered 7 hours ago
Erik DarlingErik Darling
23k1369113
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.
answered 7 hours ago
Erik DarlingErik Darling
23k1369113
edited 7 hours ago
answered 7 hours ago
Erik DarlingErik Darling
23k1369113
answered 7 hours ago
Erik DarlingErik Darling
23k1369113
answered 7 hours ago
Erik DarlingErik Darling
23k1369113
23k1369113
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
1
What does the query plan say?
– David Browne - Microsoft
8 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♦
6 hours ago