Adding an additional “order by” column gives me a much worse plan
in other words, how can I get rid of the sort
operator on the picture below?
the picture above shows the execution plan of the following 2 selects together:
SELECT TOP 1 so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC, ch.DateAdded DESC
SELECT TOP 1 so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC--, ch.DateAdded DESC
the only difference is that on the second query, there is only one column in the order by.
would it make a difference, as I am using top 1
?
I believe all the needed info are on the indexes and table definitions that can be seen on the query plan.
if anything else would help to get rid of that sort
just let me know, tomorrow I will post all the possible info.
sql-server query-performance sql-server-2016 optimization order-by
add a comment |
in other words, how can I get rid of the sort
operator on the picture below?
the picture above shows the execution plan of the following 2 selects together:
SELECT TOP 1 so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC, ch.DateAdded DESC
SELECT TOP 1 so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC--, ch.DateAdded DESC
the only difference is that on the second query, there is only one column in the order by.
would it make a difference, as I am using top 1
?
I believe all the needed info are on the indexes and table definitions that can be seen on the query plan.
if anything else would help to get rid of that sort
just let me know, tomorrow I will post all the possible info.
sql-server query-performance sql-server-2016 optimization order-by
Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?
– Aaron Bertrand♦
11 hours ago
@AaronBertrand -CPU time 756, Elapsed Time 222, Reads 2357
vsCPU time 0, Elapsed Time 0, Reads 10
from the stats in the plan - most of the tables are pretty small though. Table cardinalities11
,19
,67,591
,232,528
– Martin Smith
10 hours ago
1
@MartinSmith Thanks, I didn’t look at the plan (mobile), just try my best to make sure drive-by readers don’t put too much weight into those percentages. Sometimes they’re useful, sometimes they’re extremely misleading.
– Aaron Bertrand♦
9 hours ago
add a comment |
in other words, how can I get rid of the sort
operator on the picture below?
the picture above shows the execution plan of the following 2 selects together:
SELECT TOP 1 so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC, ch.DateAdded DESC
SELECT TOP 1 so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC--, ch.DateAdded DESC
the only difference is that on the second query, there is only one column in the order by.
would it make a difference, as I am using top 1
?
I believe all the needed info are on the indexes and table definitions that can be seen on the query plan.
if anything else would help to get rid of that sort
just let me know, tomorrow I will post all the possible info.
sql-server query-performance sql-server-2016 optimization order-by
in other words, how can I get rid of the sort
operator on the picture below?
the picture above shows the execution plan of the following 2 selects together:
SELECT TOP 1 so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC, ch.DateAdded DESC
SELECT TOP 1 so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC--, ch.DateAdded DESC
the only difference is that on the second query, there is only one column in the order by.
would it make a difference, as I am using top 1
?
I believe all the needed info are on the indexes and table definitions that can be seen on the query plan.
if anything else would help to get rid of that sort
just let me know, tomorrow I will post all the possible info.
sql-server query-performance sql-server-2016 optimization order-by
sql-server query-performance sql-server-2016 optimization order-by
edited 7 hours ago
Martin Smith
63.6k10171255
63.6k10171255
asked 11 hours ago
marcello miorellimarcello miorelli
5,8971962138
5,8971962138
Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?
– Aaron Bertrand♦
11 hours ago
@AaronBertrand -CPU time 756, Elapsed Time 222, Reads 2357
vsCPU time 0, Elapsed Time 0, Reads 10
from the stats in the plan - most of the tables are pretty small though. Table cardinalities11
,19
,67,591
,232,528
– Martin Smith
10 hours ago
1
@MartinSmith Thanks, I didn’t look at the plan (mobile), just try my best to make sure drive-by readers don’t put too much weight into those percentages. Sometimes they’re useful, sometimes they’re extremely misleading.
– Aaron Bertrand♦
9 hours ago
add a comment |
Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?
– Aaron Bertrand♦
11 hours ago
@AaronBertrand -CPU time 756, Elapsed Time 222, Reads 2357
vsCPU time 0, Elapsed Time 0, Reads 10
from the stats in the plan - most of the tables are pretty small though. Table cardinalities11
,19
,67,591
,232,528
– Martin Smith
10 hours ago
1
@MartinSmith Thanks, I didn’t look at the plan (mobile), just try my best to make sure drive-by readers don’t put too much weight into those percentages. Sometimes they’re useful, sometimes they’re extremely misleading.
– Aaron Bertrand♦
9 hours ago
Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?
– Aaron Bertrand♦
11 hours ago
Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?
– Aaron Bertrand♦
11 hours ago
@AaronBertrand -
CPU time 756, Elapsed Time 222, Reads 2357
vs CPU time 0, Elapsed Time 0, Reads 10
from the stats in the plan - most of the tables are pretty small though. Table cardinalities 11
, 19
, 67,591
, 232,528
– Martin Smith
10 hours ago
@AaronBertrand -
CPU time 756, Elapsed Time 222, Reads 2357
vs CPU time 0, Elapsed Time 0, Reads 10
from the stats in the plan - most of the tables are pretty small though. Table cardinalities 11
, 19
, 67,591
, 232,528
– Martin Smith
10 hours ago
1
1
@MartinSmith Thanks, I didn’t look at the plan (mobile), just try my best to make sure drive-by readers don’t put too much weight into those percentages. Sometimes they’re useful, sometimes they’re extremely misleading.
– Aaron Bertrand♦
9 hours ago
@MartinSmith Thanks, I didn’t look at the plan (mobile), just try my best to make sure drive-by readers don’t put too much weight into those percentages. Sometimes they’re useful, sometimes they’re extremely misleading.
– Aaron Bertrand♦
9 hours ago
add a comment |
1 Answer
1
active
oldest
votes
Your question is missing a lot of detail but I can reproduce something similar.
Setup
CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX)
CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y))
INSERT INTO T2
OUTPUT INSERTED.* INTO T1
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1, sys.all_objects o2;
Query 1
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y;
Query 2
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y, T2.Y
Query 3
WITH T AS
(
SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y
)
SELECT TOP 1 *
FROM T
ORDER BY T2Y
Query 1 just picks off the TOP 1
from the index in the desired sort order and does the needed joins on the other table for that row. If the join is successful it stops there otherwise it tries the next one in index order until it finds a row that matches or runs out of rows.
Query 2 When adding the new sort column this plan is no longer valid as there could be multiple matches tied with the TOP 1
value and SQL Server decides to join the whole lot and then get the TOP 1
from that.
Query 3 This encourages SQL Server to stick with the first strategy and then just does a TOP 1 Sort
on any rows tied with the same value for the first sort key.
For my example data Query 3 works out better than Query 2 but if you have many duplicates tied for the value of the first sort key your milage may differ.
You can try this rewrite and see how it fares
WITH T
AS (SELECT TOP 1 WITH TIES so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId,
ch.DateAdded AS chDateAdded
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC)
SELECT TOP 1 OrgType,
Status,
DBSstatusDescription,
ApplicationId
FROM T
ORDER BY chDateAdded DESC
A+ for the first time I've seen someone useTOP WITH TIES
for a good reason.
– Erik Darling
4 hours 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%2f231950%2fadding-an-additional-order-by-column-gives-me-a-much-worse-plan%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
Your question is missing a lot of detail but I can reproduce something similar.
Setup
CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX)
CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y))
INSERT INTO T2
OUTPUT INSERTED.* INTO T1
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1, sys.all_objects o2;
Query 1
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y;
Query 2
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y, T2.Y
Query 3
WITH T AS
(
SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y
)
SELECT TOP 1 *
FROM T
ORDER BY T2Y
Query 1 just picks off the TOP 1
from the index in the desired sort order and does the needed joins on the other table for that row. If the join is successful it stops there otherwise it tries the next one in index order until it finds a row that matches or runs out of rows.
Query 2 When adding the new sort column this plan is no longer valid as there could be multiple matches tied with the TOP 1
value and SQL Server decides to join the whole lot and then get the TOP 1
from that.
Query 3 This encourages SQL Server to stick with the first strategy and then just does a TOP 1 Sort
on any rows tied with the same value for the first sort key.
For my example data Query 3 works out better than Query 2 but if you have many duplicates tied for the value of the first sort key your milage may differ.
You can try this rewrite and see how it fares
WITH T
AS (SELECT TOP 1 WITH TIES so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId,
ch.DateAdded AS chDateAdded
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC)
SELECT TOP 1 OrgType,
Status,
DBSstatusDescription,
ApplicationId
FROM T
ORDER BY chDateAdded DESC
A+ for the first time I've seen someone useTOP WITH TIES
for a good reason.
– Erik Darling
4 hours ago
add a comment |
Your question is missing a lot of detail but I can reproduce something similar.
Setup
CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX)
CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y))
INSERT INTO T2
OUTPUT INSERTED.* INTO T1
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1, sys.all_objects o2;
Query 1
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y;
Query 2
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y, T2.Y
Query 3
WITH T AS
(
SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y
)
SELECT TOP 1 *
FROM T
ORDER BY T2Y
Query 1 just picks off the TOP 1
from the index in the desired sort order and does the needed joins on the other table for that row. If the join is successful it stops there otherwise it tries the next one in index order until it finds a row that matches or runs out of rows.
Query 2 When adding the new sort column this plan is no longer valid as there could be multiple matches tied with the TOP 1
value and SQL Server decides to join the whole lot and then get the TOP 1
from that.
Query 3 This encourages SQL Server to stick with the first strategy and then just does a TOP 1 Sort
on any rows tied with the same value for the first sort key.
For my example data Query 3 works out better than Query 2 but if you have many duplicates tied for the value of the first sort key your milage may differ.
You can try this rewrite and see how it fares
WITH T
AS (SELECT TOP 1 WITH TIES so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId,
ch.DateAdded AS chDateAdded
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC)
SELECT TOP 1 OrgType,
Status,
DBSstatusDescription,
ApplicationId
FROM T
ORDER BY chDateAdded DESC
A+ for the first time I've seen someone useTOP WITH TIES
for a good reason.
– Erik Darling
4 hours ago
add a comment |
Your question is missing a lot of detail but I can reproduce something similar.
Setup
CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX)
CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y))
INSERT INTO T2
OUTPUT INSERTED.* INTO T1
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1, sys.all_objects o2;
Query 1
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y;
Query 2
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y, T2.Y
Query 3
WITH T AS
(
SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y
)
SELECT TOP 1 *
FROM T
ORDER BY T2Y
Query 1 just picks off the TOP 1
from the index in the desired sort order and does the needed joins on the other table for that row. If the join is successful it stops there otherwise it tries the next one in index order until it finds a row that matches or runs out of rows.
Query 2 When adding the new sort column this plan is no longer valid as there could be multiple matches tied with the TOP 1
value and SQL Server decides to join the whole lot and then get the TOP 1
from that.
Query 3 This encourages SQL Server to stick with the first strategy and then just does a TOP 1 Sort
on any rows tied with the same value for the first sort key.
For my example data Query 3 works out better than Query 2 but if you have many duplicates tied for the value of the first sort key your milage may differ.
You can try this rewrite and see how it fares
WITH T
AS (SELECT TOP 1 WITH TIES so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId,
ch.DateAdded AS chDateAdded
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC)
SELECT TOP 1 OrgType,
Status,
DBSstatusDescription,
ApplicationId
FROM T
ORDER BY chDateAdded DESC
Your question is missing a lot of detail but I can reproduce something similar.
Setup
CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX)
CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y))
INSERT INTO T2
OUTPUT INSERTED.* INTO T1
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1, sys.all_objects o2;
Query 1
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y;
Query 2
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y, T2.Y
Query 3
WITH T AS
(
SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y
)
SELECT TOP 1 *
FROM T
ORDER BY T2Y
Query 1 just picks off the TOP 1
from the index in the desired sort order and does the needed joins on the other table for that row. If the join is successful it stops there otherwise it tries the next one in index order until it finds a row that matches or runs out of rows.
Query 2 When adding the new sort column this plan is no longer valid as there could be multiple matches tied with the TOP 1
value and SQL Server decides to join the whole lot and then get the TOP 1
from that.
Query 3 This encourages SQL Server to stick with the first strategy and then just does a TOP 1 Sort
on any rows tied with the same value for the first sort key.
For my example data Query 3 works out better than Query 2 but if you have many duplicates tied for the value of the first sort key your milage may differ.
You can try this rewrite and see how it fares
WITH T
AS (SELECT TOP 1 WITH TIES so.OrgType,
ch.Status,
rcs.DBSstatusDescription,
cid.ApplicationId,
ch.DateAdded AS chDateAdded
FROM tbl_application_crb_initialData cid
INNER JOIN tbl_season_organisationId so
ON cid.OrganisationId = so.OrgId
LEFT JOIN tbl_crbHistory ch
ON cid.ClientReference = ch.ClientReference
LEFT JOIN ref_crbStatus rcs
ON ch.Status = rcs.statusId
ORDER BY cid.DateAdded DESC)
SELECT TOP 1 OrgType,
Status,
DBSstatusDescription,
ApplicationId
FROM T
ORDER BY chDateAdded DESC
edited 9 hours ago
answered 11 hours ago
Martin SmithMartin Smith
63.6k10171255
63.6k10171255
A+ for the first time I've seen someone useTOP WITH TIES
for a good reason.
– Erik Darling
4 hours ago
add a comment |
A+ for the first time I've seen someone useTOP WITH TIES
for a good reason.
– Erik Darling
4 hours ago
A+ for the first time I've seen someone use
TOP WITH TIES
for a good reason.– Erik Darling
4 hours ago
A+ for the first time I've seen someone use
TOP WITH TIES
for a good reason.– Erik Darling
4 hours 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%2f231950%2fadding-an-additional-order-by-column-gives-me-a-much-worse-plan%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
Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?
– Aaron Bertrand♦
11 hours ago
@AaronBertrand -
CPU time 756, Elapsed Time 222, Reads 2357
vsCPU time 0, Elapsed Time 0, Reads 10
from the stats in the plan - most of the tables are pretty small though. Table cardinalities11
,19
,67,591
,232,528
– Martin Smith
10 hours ago
1
@MartinSmith Thanks, I didn’t look at the plan (mobile), just try my best to make sure drive-by readers don’t put too much weight into those percentages. Sometimes they’re useful, sometimes they’re extremely misleading.
– Aaron Bertrand♦
9 hours ago