Adding an additional “order by” column gives me a much worse planHelp optimizing MySQL slow queryOptimizing ORDER BY for simple MySQL queryDatabase Implementations of ORDER BY in a Subqueryquery performance gains by removing operator hash match inner joinWhy are these two queries having such different executions?How can I update statistics adding the data of the last day only?left outer join - sort operations in the query plan - any ways of tuning this simple query?Why does changing the declared join column order introduce a sort?When can SARGable predicates be pushed into a CTE or derived table?Why does the location of a join change performance?

why `nmap 192.168.1.97` returns less services than `nmap 127.0.0.1`?

Did arcade monitors have same pixel aspect ratio as TV sets?

Difference between -| and |- in TikZ

Perfect Cadence in minor key

Python script not running correctly when launched with crontab

Longest common substring in linear time

Is it improper etiquette to ask your opponent what his/her rating is before the game?

Did US corporations pay demonstrators in the German demonstrations against article 13?

Question about alert, surprise, and crit failing

Melting point of aspirin, contradicting sources

Can I use my Chinese passport to enter China after I acquired another citizenship?

Why do IPv6 unique local addresses have to have a /48 prefix?

Will adding a BY-SA image to a blog post make the entire post BY-SA?

How can "mimic phobia" be cured or prevented?

Is XSS in canonical link possible?

How does the reference system of the Majjhima Nikaya work?

Is there a name for this algorithm to calculate the concentration of a mixture of two solutions containing the same solute?

How can Trident be so inexpensive? Will it orbit Triton or just do a (slow) flyby?

Freedom of speech and where it applies

Pre-mixing cryogenic fuels and using only one fuel tank

How much character growth crosses the line into breaking the character

Why did the HMS Bounty go back to a time when whales are already rare?

Open problems concerning all the finite groups

Why did the EU agree to delay the Brexit deadline?



Adding an additional “order by” column gives me a much worse plan


Help optimizing MySQL slow queryOptimizing ORDER BY for simple MySQL queryDatabase Implementations of ORDER BY in a Subqueryquery performance gains by removing operator hash match inner joinWhy are these two queries having such different executions?How can I update statistics adding the data of the last day only?left outer join - sort operations in the query plan - any ways of tuning this simple query?Why does changing the declared join column order introduce a sort?When can SARGable predicates be pushed into a CTE or derived table?Why does the location of a join change performance?













3















in other words, how can I get rid of the sort operator on the picture below?



enter image description here



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.










share|improve this question
























  • Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?

    – Aaron Bertrand
    Mar 12 at 18:34






  • 1





    @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
    Mar 12 at 19:33







  • 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
    Mar 12 at 20:57















3















in other words, how can I get rid of the sort operator on the picture below?



enter image description here



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.










share|improve this question
























  • Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?

    – Aaron Bertrand
    Mar 12 at 18:34






  • 1





    @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
    Mar 12 at 19:33







  • 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
    Mar 12 at 20:57













3












3








3


1






in other words, how can I get rid of the sort operator on the picture below?



enter image description here



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.










share|improve this question
















in other words, how can I get rid of the sort operator on the picture below?



enter image description here



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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 12 at 22:22









Martin Smith

63.9k10173257




63.9k10173257










asked Mar 12 at 18:26









marcello miorellimarcello miorelli

5,9462162142




5,9462162142












  • Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?

    – Aaron Bertrand
    Mar 12 at 18:34






  • 1





    @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
    Mar 12 at 19:33







  • 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
    Mar 12 at 20:57

















  • Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?

    – Aaron Bertrand
    Mar 12 at 18:34






  • 1





    @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
    Mar 12 at 19:33







  • 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
    Mar 12 at 20:57
















Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?

– Aaron Bertrand
Mar 12 at 18:34





Those cost % are just estimates and can actually end up being WAY, WAY off. Is the top query actually noticeably slower?

– Aaron Bertrand
Mar 12 at 18:34




1




1





@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
Mar 12 at 19:33






@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
Mar 12 at 19:33





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
Mar 12 at 20:57





@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
Mar 12 at 20:57










1 Answer
1






active

oldest

votes


















8














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;


enter image description here



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


enter image description here



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


enter image description here



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





share|improve this answer




















  • 1





    A+ for the first time I've seen someone use TOP WITH TIES for a good reason.

    – Erik Darling
    Mar 13 at 2:01











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%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









8














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;


enter image description here



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


enter image description here



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


enter image description here



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





share|improve this answer




















  • 1





    A+ for the first time I've seen someone use TOP WITH TIES for a good reason.

    – Erik Darling
    Mar 13 at 2:01
















8














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;


enter image description here



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


enter image description here



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


enter image description here



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





share|improve this answer




















  • 1





    A+ for the first time I've seen someone use TOP WITH TIES for a good reason.

    – Erik Darling
    Mar 13 at 2:01














8












8








8







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;


enter image description here



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


enter image description here



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


enter image description here



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





share|improve this answer















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;


enter image description here



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


enter image description here



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


enter image description here



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






share|improve this answer














share|improve this answer



share|improve this answer








edited Mar 12 at 20:54

























answered Mar 12 at 19:11









Martin SmithMartin Smith

63.9k10173257




63.9k10173257







  • 1





    A+ for the first time I've seen someone use TOP WITH TIES for a good reason.

    – Erik Darling
    Mar 13 at 2:01













  • 1





    A+ for the first time I've seen someone use TOP WITH TIES for a good reason.

    – Erik Darling
    Mar 13 at 2:01








1




1





A+ for the first time I've seen someone use TOP WITH TIES for a good reason.

– Erik Darling
Mar 13 at 2:01






A+ for the first time I've seen someone use TOP WITH TIES for a good reason.

– Erik Darling
Mar 13 at 2:01


















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%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





















































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







-optimization, order-by, query-performance, sql-server, sql-server-2016

Popular posts from this blog

Frič See also Navigation menuinternal link

Identify plant with long narrow paired leaves and reddish stems Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?What is this plant with long sharp leaves? Is it a weed?What is this 3ft high, stalky plant, with mid sized narrow leaves?What is this young shrub with opposite ovate, crenate leaves and reddish stems?What is this plant with large broad serrated leaves?Identify this upright branching weed with long leaves and reddish stemsPlease help me identify this bulbous plant with long, broad leaves and white flowersWhat is this small annual with narrow gray/green leaves and rust colored daisy-type flowers?What is this chilli plant?Does anyone know what type of chilli plant this is?Help identify this plant

fontconfig warning: “/etc/fonts/fonts.conf”, line 100: unknown “element blank” The 2019 Stack Overflow Developer Survey Results Are In“tar: unrecognized option --warning” during 'apt-get install'How to fix Fontconfig errorHow do I figure out which font file is chosen for a system generic font alias?Why are some apt-get-installed fonts being ignored by fc-list, xfontsel, etc?Reload settings in /etc/fonts/conf.dTaking 30 seconds longer to boot after upgrade from jessie to stretchHow to match multiple font names with a single <match> element?Adding a custom font to fontconfigRemoving fonts from fontconfig <match> resultsBroken fonts after upgrading Firefox ESR to latest Firefox