How can I get rid of an unhelpful parallel branch when unpivoting a single row?NUMA Nodes - MAXDOP - PLESQL Server thread statuswhy sql server has high Worker threads?SQL Server Threads and Degree Of ParallelismIs it possible to see which SPID uses which scheduler (worker thread)?Who is using my worker threads? SQL Server 2014 - HADRMeasure Agent Job failure and running jobs with 'execution_status'SQL Server instance running out of worker threadsWhat's the easiest and most accurate way to visualize parallel thread usage in SQL Server?MAX worker thread in SQL server 2012/14/16
Is Electric Central Heating worth it if using Solar Panels?
What does "function" actually mean in music?
Do I need to watch Ant-Man and the Wasp and Captain Marvel before watching Avengers: Endgame?
Drawing a german abacus as in the books of Adam Ries
How bug prioritization works in agile projects vs non agile
What is the most expensive material in the world that could be used to create Pun-Pun's lute?
How can I get rid of an unhelpful parallel branch when unpivoting a single row?
What makes accurate emulation of old systems a difficult task?
Philosophical question on logistic regression: why isn't the optimal threshold value trained?
Find a stone which is not the lightest one
Can a Bard use the Spell Glyph option of the Glyph of Warding spell and cast a known spell into the glyph?
I preordered a game on my Xbox while on the home screen of my friend's account. Which of us owns the game?
What is purpose of DB Browser(dbbrowser.aspx) under admin tool?
Unknown code in script
Mistake in years of experience in resume?
What does MLD stand for?
Find the identical rows in a matrix
Nails holding drywall
Why must Chinese maps be obfuscated?
How do I check if a string is entirely made of the same substring?
Is there a word for the censored part of a video?
Is there metaphorical meaning of "aus der Haft entlassen"?
How can I wire a 9-position switch so that each position turns on one more LED than the one before?
Are there moral objections to a life motivated purely by money? How to sway a person from this lifestyle?
How can I get rid of an unhelpful parallel branch when unpivoting a single row?
NUMA Nodes - MAXDOP - PLESQL Server thread statuswhy sql server has high Worker threads?SQL Server Threads and Degree Of ParallelismIs it possible to see which SPID uses which scheduler (worker thread)?Who is using my worker threads? SQL Server 2014 - HADRMeasure Agent Job failure and running jobs with 'execution_status'SQL Server instance running out of worker threadsWhat's the easiest and most accurate way to visualize parallel thread usage in SQL Server?MAX worker thread in SQL server 2012/14/16
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
Consider the following query that unpivots a few handfuls of scalar aggregates:
SELECT A, B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
UNPIVOT(B FOR A IN (
VAL1
,VAL2
,VAL3
,VAL4
,VAL5
,VAL6
,VAL7
,VAL16
)) U
OPTION (MAXDOP 4);
On SQL Server 2017, I get a plan with two parallel branches. The left parallel branch feels out of place to me. The optimizer has a guarantee that there will be only a single row output from the global scalar aggregate, yet the parent operator of it is a Distribute Streams with round robin partitioning:
When I execute the query all of the rows go to a single thread as expected. There's no performance problem with this query, but the query reserves 8 parallel threads with MAXDOP set to 4. Again, I feel that this is out of place. It's impossible for both parallel branches to execute at the same time. I want to avoid unnecessary worker thread reservation because I have TF 2467 enabled which changes the scheduling algorithm to look at the number of worker threads per scheduler.
Is it possible to rewrite the query to have exactly one parallel branch that contains the table scan and local aggregate? For example, I would be fine with the general shape below except that I want the nested loop to execute in a serial zone:
For Application Reasons™ I strongly prefer to avoid splitting this query up into parts. If desired, you can view the actual query plan here. If you'd like to play along at home, here is T-SQL to create the table used in the query:
DROP TABLE IF EXISTS dbo.PARALLEL_ZONE_REPRO;
CREATE TABLE dbo.PARALLEL_ZONE_REPRO (
ID BIGINT,
FILLER VARCHAR(100)
);
INSERT INTO dbo.PARALLEL_ZONE_REPRO WITH (TABLOCK)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 15
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
sql-server sql-server-2017
add a comment |
Consider the following query that unpivots a few handfuls of scalar aggregates:
SELECT A, B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
UNPIVOT(B FOR A IN (
VAL1
,VAL2
,VAL3
,VAL4
,VAL5
,VAL6
,VAL7
,VAL16
)) U
OPTION (MAXDOP 4);
On SQL Server 2017, I get a plan with two parallel branches. The left parallel branch feels out of place to me. The optimizer has a guarantee that there will be only a single row output from the global scalar aggregate, yet the parent operator of it is a Distribute Streams with round robin partitioning:
When I execute the query all of the rows go to a single thread as expected. There's no performance problem with this query, but the query reserves 8 parallel threads with MAXDOP set to 4. Again, I feel that this is out of place. It's impossible for both parallel branches to execute at the same time. I want to avoid unnecessary worker thread reservation because I have TF 2467 enabled which changes the scheduling algorithm to look at the number of worker threads per scheduler.
Is it possible to rewrite the query to have exactly one parallel branch that contains the table scan and local aggregate? For example, I would be fine with the general shape below except that I want the nested loop to execute in a serial zone:
For Application Reasons™ I strongly prefer to avoid splitting this query up into parts. If desired, you can view the actual query plan here. If you'd like to play along at home, here is T-SQL to create the table used in the query:
DROP TABLE IF EXISTS dbo.PARALLEL_ZONE_REPRO;
CREATE TABLE dbo.PARALLEL_ZONE_REPRO (
ID BIGINT,
FILLER VARCHAR(100)
);
INSERT INTO dbo.PARALLEL_ZONE_REPRO WITH (TABLOCK)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 15
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
sql-server sql-server-2017
add a comment |
Consider the following query that unpivots a few handfuls of scalar aggregates:
SELECT A, B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
UNPIVOT(B FOR A IN (
VAL1
,VAL2
,VAL3
,VAL4
,VAL5
,VAL6
,VAL7
,VAL16
)) U
OPTION (MAXDOP 4);
On SQL Server 2017, I get a plan with two parallel branches. The left parallel branch feels out of place to me. The optimizer has a guarantee that there will be only a single row output from the global scalar aggregate, yet the parent operator of it is a Distribute Streams with round robin partitioning:
When I execute the query all of the rows go to a single thread as expected. There's no performance problem with this query, but the query reserves 8 parallel threads with MAXDOP set to 4. Again, I feel that this is out of place. It's impossible for both parallel branches to execute at the same time. I want to avoid unnecessary worker thread reservation because I have TF 2467 enabled which changes the scheduling algorithm to look at the number of worker threads per scheduler.
Is it possible to rewrite the query to have exactly one parallel branch that contains the table scan and local aggregate? For example, I would be fine with the general shape below except that I want the nested loop to execute in a serial zone:
For Application Reasons™ I strongly prefer to avoid splitting this query up into parts. If desired, you can view the actual query plan here. If you'd like to play along at home, here is T-SQL to create the table used in the query:
DROP TABLE IF EXISTS dbo.PARALLEL_ZONE_REPRO;
CREATE TABLE dbo.PARALLEL_ZONE_REPRO (
ID BIGINT,
FILLER VARCHAR(100)
);
INSERT INTO dbo.PARALLEL_ZONE_REPRO WITH (TABLOCK)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 15
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
sql-server sql-server-2017
Consider the following query that unpivots a few handfuls of scalar aggregates:
SELECT A, B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
UNPIVOT(B FOR A IN (
VAL1
,VAL2
,VAL3
,VAL4
,VAL5
,VAL6
,VAL7
,VAL16
)) U
OPTION (MAXDOP 4);
On SQL Server 2017, I get a plan with two parallel branches. The left parallel branch feels out of place to me. The optimizer has a guarantee that there will be only a single row output from the global scalar aggregate, yet the parent operator of it is a Distribute Streams with round robin partitioning:
When I execute the query all of the rows go to a single thread as expected. There's no performance problem with this query, but the query reserves 8 parallel threads with MAXDOP set to 4. Again, I feel that this is out of place. It's impossible for both parallel branches to execute at the same time. I want to avoid unnecessary worker thread reservation because I have TF 2467 enabled which changes the scheduling algorithm to look at the number of worker threads per scheduler.
Is it possible to rewrite the query to have exactly one parallel branch that contains the table scan and local aggregate? For example, I would be fine with the general shape below except that I want the nested loop to execute in a serial zone:
For Application Reasons™ I strongly prefer to avoid splitting this query up into parts. If desired, you can view the actual query plan here. If you'd like to play along at home, here is T-SQL to create the table used in the query:
DROP TABLE IF EXISTS dbo.PARALLEL_ZONE_REPRO;
CREATE TABLE dbo.PARALLEL_ZONE_REPRO (
ID BIGINT,
FILLER VARCHAR(100)
);
INSERT INTO dbo.PARALLEL_ZONE_REPRO WITH (TABLOCK)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 15
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
sql-server sql-server-2017
sql-server sql-server-2017
asked 2 hours ago
Joe ObbishJoe Obbish
22.3k43493
22.3k43493
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I am able to get the desired plan shape with a serial loop join when all of the following are true:
- An
APPLY
orCROSS JOIN
is used instead ofUNPIVOT
- The
APPLY
contains no outer references - The source of rows in the
APPLY
is a table value constructor as opposed to a table
For example, here is one way to do it:
SELECT A, B
FROM
(
SELECT A
, MAX(
CASE
WHEN A = 'VAL1' THEN VAL1
WHEN A = 'VAL2' THEN VAL2
WHEN A = 'VAL3' THEN VAL3
WHEN A = 'VAL4' THEN VAL4
WHEN A = 'VAL5' THEN VAL5
WHEN A = 'VAL6' THEN VAL6
WHEN A = 'VAL7' THEN VAL7
WHEN A = 'VAL16' THEN VAL16
ELSE NULL
END
) B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
CROSS APPLY (
VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
('VAL5'), ('VAL6'), ('VAL7'), ('VAL16')
) ca (A)
GROUP BY A
) q
WHERE q.B IS NOT NULL
OPTION (MAXDOP 4);
I get the desired plan plan shape as claimed with just one parallel branch:
I tried many other things that did not work. This answer is unsatisfactory in that I don't know why it works and it may not work in a future version of SQL Server, but it did solve my problem.
I love APPLY versus UNPIVOT, but one would expect the declarative nature of T-SQL to result in the same plan shape for both variants. It'll be interesting to see what happens with this.
– Max Vernon
44 mins ago
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%2f236732%2fhow-can-i-get-rid-of-an-unhelpful-parallel-branch-when-unpivoting-a-single-row%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I am able to get the desired plan shape with a serial loop join when all of the following are true:
- An
APPLY
orCROSS JOIN
is used instead ofUNPIVOT
- The
APPLY
contains no outer references - The source of rows in the
APPLY
is a table value constructor as opposed to a table
For example, here is one way to do it:
SELECT A, B
FROM
(
SELECT A
, MAX(
CASE
WHEN A = 'VAL1' THEN VAL1
WHEN A = 'VAL2' THEN VAL2
WHEN A = 'VAL3' THEN VAL3
WHEN A = 'VAL4' THEN VAL4
WHEN A = 'VAL5' THEN VAL5
WHEN A = 'VAL6' THEN VAL6
WHEN A = 'VAL7' THEN VAL7
WHEN A = 'VAL16' THEN VAL16
ELSE NULL
END
) B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
CROSS APPLY (
VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
('VAL5'), ('VAL6'), ('VAL7'), ('VAL16')
) ca (A)
GROUP BY A
) q
WHERE q.B IS NOT NULL
OPTION (MAXDOP 4);
I get the desired plan plan shape as claimed with just one parallel branch:
I tried many other things that did not work. This answer is unsatisfactory in that I don't know why it works and it may not work in a future version of SQL Server, but it did solve my problem.
I love APPLY versus UNPIVOT, but one would expect the declarative nature of T-SQL to result in the same plan shape for both variants. It'll be interesting to see what happens with this.
– Max Vernon
44 mins ago
add a comment |
I am able to get the desired plan shape with a serial loop join when all of the following are true:
- An
APPLY
orCROSS JOIN
is used instead ofUNPIVOT
- The
APPLY
contains no outer references - The source of rows in the
APPLY
is a table value constructor as opposed to a table
For example, here is one way to do it:
SELECT A, B
FROM
(
SELECT A
, MAX(
CASE
WHEN A = 'VAL1' THEN VAL1
WHEN A = 'VAL2' THEN VAL2
WHEN A = 'VAL3' THEN VAL3
WHEN A = 'VAL4' THEN VAL4
WHEN A = 'VAL5' THEN VAL5
WHEN A = 'VAL6' THEN VAL6
WHEN A = 'VAL7' THEN VAL7
WHEN A = 'VAL16' THEN VAL16
ELSE NULL
END
) B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
CROSS APPLY (
VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
('VAL5'), ('VAL6'), ('VAL7'), ('VAL16')
) ca (A)
GROUP BY A
) q
WHERE q.B IS NOT NULL
OPTION (MAXDOP 4);
I get the desired plan plan shape as claimed with just one parallel branch:
I tried many other things that did not work. This answer is unsatisfactory in that I don't know why it works and it may not work in a future version of SQL Server, but it did solve my problem.
I love APPLY versus UNPIVOT, but one would expect the declarative nature of T-SQL to result in the same plan shape for both variants. It'll be interesting to see what happens with this.
– Max Vernon
44 mins ago
add a comment |
I am able to get the desired plan shape with a serial loop join when all of the following are true:
- An
APPLY
orCROSS JOIN
is used instead ofUNPIVOT
- The
APPLY
contains no outer references - The source of rows in the
APPLY
is a table value constructor as opposed to a table
For example, here is one way to do it:
SELECT A, B
FROM
(
SELECT A
, MAX(
CASE
WHEN A = 'VAL1' THEN VAL1
WHEN A = 'VAL2' THEN VAL2
WHEN A = 'VAL3' THEN VAL3
WHEN A = 'VAL4' THEN VAL4
WHEN A = 'VAL5' THEN VAL5
WHEN A = 'VAL6' THEN VAL6
WHEN A = 'VAL7' THEN VAL7
WHEN A = 'VAL16' THEN VAL16
ELSE NULL
END
) B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
CROSS APPLY (
VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
('VAL5'), ('VAL6'), ('VAL7'), ('VAL16')
) ca (A)
GROUP BY A
) q
WHERE q.B IS NOT NULL
OPTION (MAXDOP 4);
I get the desired plan plan shape as claimed with just one parallel branch:
I tried many other things that did not work. This answer is unsatisfactory in that I don't know why it works and it may not work in a future version of SQL Server, but it did solve my problem.
I am able to get the desired plan shape with a serial loop join when all of the following are true:
- An
APPLY
orCROSS JOIN
is used instead ofUNPIVOT
- The
APPLY
contains no outer references - The source of rows in the
APPLY
is a table value constructor as opposed to a table
For example, here is one way to do it:
SELECT A, B
FROM
(
SELECT A
, MAX(
CASE
WHEN A = 'VAL1' THEN VAL1
WHEN A = 'VAL2' THEN VAL2
WHEN A = 'VAL3' THEN VAL3
WHEN A = 'VAL4' THEN VAL4
WHEN A = 'VAL5' THEN VAL5
WHEN A = 'VAL6' THEN VAL6
WHEN A = 'VAL7' THEN VAL7
WHEN A = 'VAL16' THEN VAL16
ELSE NULL
END
) B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
, MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
, MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
, MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
, MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
, MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
, MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
FROM dbo.PARALLEL_ZONE_REPRO
) q
CROSS APPLY (
VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
('VAL5'), ('VAL6'), ('VAL7'), ('VAL16')
) ca (A)
GROUP BY A
) q
WHERE q.B IS NOT NULL
OPTION (MAXDOP 4);
I get the desired plan plan shape as claimed with just one parallel branch:
I tried many other things that did not work. This answer is unsatisfactory in that I don't know why it works and it may not work in a future version of SQL Server, but it did solve my problem.
answered 2 hours ago
Joe ObbishJoe Obbish
22.3k43493
22.3k43493
I love APPLY versus UNPIVOT, but one would expect the declarative nature of T-SQL to result in the same plan shape for both variants. It'll be interesting to see what happens with this.
– Max Vernon
44 mins ago
add a comment |
I love APPLY versus UNPIVOT, but one would expect the declarative nature of T-SQL to result in the same plan shape for both variants. It'll be interesting to see what happens with this.
– Max Vernon
44 mins ago
I love APPLY versus UNPIVOT, but one would expect the declarative nature of T-SQL to result in the same plan shape for both variants. It'll be interesting to see what happens with this.
– Max Vernon
44 mins ago
I love APPLY versus UNPIVOT, but one would expect the declarative nature of T-SQL to result in the same plan shape for both variants. It'll be interesting to see what happens with this.
– Max Vernon
44 mins ago
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%2f236732%2fhow-can-i-get-rid-of-an-unhelpful-parallel-branch-when-unpivoting-a-single-row%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