Mdthbs

How did Einstein know the speed of light was constant?

Speeding up thousands of string parses

How do both sides know the MTU

What is the maximum amount of diamond in one Minecraft game?

Why does the Batman "crack his knuckles" in "Batman: Arkham Origins"?

Will electrically joined dipoles of different lengths, at right angles, behave as a multiband antenna?

CPA filed late returns, stating I would get money; IRS says they were filed too late

Do I need to be legally qualified to install a Hive smart thermostat?

Two queries on triangles, the sides of which have rational lengths

Should I increase my 401(k) contributions, or increase my mortgage payments

Should I hide my travel history to the UK when I apply for an Australian visa?

Could you sell yourself into slavery in the USA?

Was Wolfgang Unzicker the last Amateur GM?

What do you call the angle of the direction of an airplane?

How can I effectively map a multi-level dungeon?

Platform Event Design when Subscribers are Apex Triggers

What is meaning of 4 letter abbreviations in Roman names like Titus Flavius T. f. T. n. Sabinus?

Why do we need a bootloader separate than our application program in MCU's?

Explain how 'Sharing the burden' puzzle from Professor Layton and the Miracle Mask should be solved

Isn't "Dave's protocol" good if only the database, and not the code, is leaked?

Should I warn my boss I might take sick leave

Did Stalin kill all Soviet officers involved in the Winter War?

About opening an LLC with little to report in the beginning

Creating patterns

how can i make this execution plan more efficient?

Can I make this multiple join query faster?Deadlock — Way to make this better?Fix for slow SQL_INLINE_TABLE_VALUED_FUNCTIONPerformance tuning on a queryRow estimates always too lowWhy is selecting all resulting columns of this query faster than selecting the one column I care about?SQL Server chooses Nested Loop join with dimensional table and make seek for each rowHow can I make this nested query more efficient?Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?Forcing execution plan with local join hints

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

I have worked out all the implicit conversion but I still see mentions of it in the plan. I have attached the plan, any recommendation will help. thank you.

select cardholder_index, sum(value) as [RxCost] 

into #rxCosts 

from RiskPredictionStatistics with (nolock) where model_name = 'prescription_cost_12_months'

 and model_set_name = 'rx_updated' and run_id in (select value from #runIds) 

 and exists (select 1 from StringContainsHelper with (nolock) where IntValue = cardholder_index and ReferenceId = @stringContainsHelperRefId)

group by cardholder_index

https://www.brentozar.com/pastetheplan/?id=SyHl9xFeS

sql-server sql-server-2017

share

edited 9 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 10 hours ago

Merveille TchoudaMerveille Tchouda

822 bronze badges

New contributor

Merveille Tchouda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It appears the bulk of the plan is spent on a Primary Key Lookup on dbo.RiskPredictionStatistics. Would we be able to get the table schema and index information in your question? We will probably want details for how the temporary table #runIDs is built as well as the schema and index information for StringContainsHelper. Thank you
  
  – Kirk Saunders
  9 hours ago
  
  
  

  
  
  
  

add a comment | 

1

I have worked out all the implicit conversion but I still see mentions of it in the plan. I have attached the plan, any recommendation will help. thank you.

select cardholder_index, sum(value) as [RxCost] 

into #rxCosts 

from RiskPredictionStatistics with (nolock) where model_name = 'prescription_cost_12_months'

 and model_set_name = 'rx_updated' and run_id in (select value from #runIds) 

 and exists (select 1 from StringContainsHelper with (nolock) where IntValue = cardholder_index and ReferenceId = @stringContainsHelperRefId)

group by cardholder_index

https://www.brentozar.com/pastetheplan/?id=SyHl9xFeS

sql-server sql-server-2017

share

edited 9 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 10 hours ago

Merveille TchoudaMerveille Tchouda

822 bronze badges

New contributor

Merveille Tchouda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It appears the bulk of the plan is spent on a Primary Key Lookup on dbo.RiskPredictionStatistics. Would we be able to get the table schema and index information in your question? We will probably want details for how the temporary table #runIDs is built as well as the schema and index information for StringContainsHelper. Thank you
  
  – Kirk Saunders
  9 hours ago
  
  
  

  
  
  
  

add a comment | 

I have worked out all the implicit conversion but I still see mentions of it in the plan. I have attached the plan, any recommendation will help. thank you.

select cardholder_index, sum(value) as [RxCost] 

into #rxCosts 

from RiskPredictionStatistics with (nolock) where model_name = 'prescription_cost_12_months'

 and model_set_name = 'rx_updated' and run_id in (select value from #runIds) 

 and exists (select 1 from StringContainsHelper with (nolock) where IntValue = cardholder_index and ReferenceId = @stringContainsHelperRefId)

group by cardholder_index

https://www.brentozar.com/pastetheplan/?id=SyHl9xFeS

sql-server sql-server-2017

share

edited 9 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 10 hours ago

Merveille TchoudaMerveille Tchouda

822 bronze badges

New contributor

Merveille Tchouda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

select cardholder_index, sum(value) as [RxCost] 

into #rxCosts 

from RiskPredictionStatistics with (nolock) where model_name = 'prescription_cost_12_months'

 and model_set_name = 'rx_updated' and run_id in (select value from #runIds) 

 and exists (select 1 from StringContainsHelper with (nolock) where IntValue = cardholder_index and ReferenceId = @stringContainsHelperRefId)

group by cardholder_index

share

edited 9 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 10 hours ago

Merveille TchoudaMerveille Tchouda

822 bronze badges

New contributor

Merveille Tchouda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

edited 9 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 10 hours ago

Merveille TchoudaMerveille Tchouda

822 bronze badges

New contributor

Merveille Tchouda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 9 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

edited 9 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 10 hours ago

Merveille TchoudaMerveille Tchouda

822 bronze badges

New contributor

Merveille Tchouda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 10 hours ago

Merveille TchoudaMerveille Tchouda

822 bronze badges

1 Answer
1

active

oldest

votes

5

It looks like when you populate the #runIds table -- and I'm just taking a wild guess here -- you're using a string splitter function that outputs the values as NVARCHAR(MAX).

You could try converting the values there to get rid of the implicit conversion warnings.

Another possible improvement would be to alter the NonClustereIndex-Cardholder index on RiskProductionStatistics to have model_set_name as a key column, and model_name, run_id, value as included columns. This would address the Key Lookup.

You may also want to check the datatype of model_name. It appears in a Filter operator, and my fear is that it's a MAX datatype, which may prevent the predicate from being pushed down.

Since this is an estimated plan, and you haven't included any metrics about the query, it's hard to say how much improvement these changes will have.

share|improve this answer

answered 9 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

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
});


}
});

Merveille Tchouda is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f241904%2fhow-can-i-make-this-execution-plan-more-efficient%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

5

It looks like when you populate the #runIds table -- and I'm just taking a wild guess here -- you're using a string splitter function that outputs the values as NVARCHAR(MAX).

You could try converting the values there to get rid of the implicit conversion warnings.

Another possible improvement would be to alter the NonClustereIndex-Cardholder index on RiskProductionStatistics to have model_set_name as a key column, and model_name, run_id, value as included columns. This would address the Key Lookup.

You may also want to check the datatype of model_name. It appears in a Filter operator, and my fear is that it's a MAX datatype, which may prevent the predicate from being pushed down.

Since this is an estimated plan, and you haven't included any metrics about the query, it's hard to say how much improvement these changes will have.

share|improve this answer

answered 9 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

add a comment | 

5

It looks like when you populate the #runIds table -- and I'm just taking a wild guess here -- you're using a string splitter function that outputs the values as NVARCHAR(MAX).

You could try converting the values there to get rid of the implicit conversion warnings.

Another possible improvement would be to alter the NonClustereIndex-Cardholder index on RiskProductionStatistics to have model_set_name as a key column, and model_name, run_id, value as included columns. This would address the Key Lookup.

You may also want to check the datatype of model_name. It appears in a Filter operator, and my fear is that it's a MAX datatype, which may prevent the predicate from being pushed down.

Since this is an estimated plan, and you haven't included any metrics about the query, it's hard to say how much improvement these changes will have.

share|improve this answer

answered 9 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

add a comment | 

It looks like when you populate the #runIds table -- and I'm just taking a wild guess here -- you're using a string splitter function that outputs the values as NVARCHAR(MAX).

You could try converting the values there to get rid of the implicit conversion warnings.

Another possible improvement would be to alter the NonClustereIndex-Cardholder index on RiskProductionStatistics to have model_set_name as a key column, and model_name, run_id, value as included columns. This would address the Key Lookup.

You may also want to check the datatype of model_name. It appears in a Filter operator, and my fear is that it's a MAX datatype, which may prevent the predicate from being pushed down.

Since this is an estimated plan, and you haven't included any metrics about the query, it's hard to say how much improvement these changes will have.

share|improve this answer

answered 9 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

share|improve this answer

answered 9 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

answered 9 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

answered 9 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges