Xjyuk

Advice for making/keeping shredded chicken moist?

Minimizing medical costs with HSA

Milky way is orbiting around?

PhD: When to quit and move on?

Why would "dead languages" be the only languages that spells could be written in?

How can I effectively map a multi-level dungeon?

Is it bad to suddenly introduce another element to your fantasy world a good ways into the story?

Is it possible that Curiosity measured its own methane or failed doing the spectrometry?

Taking advantage when the HR forgets to communicate the rules

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

Machine Learning Golf: Multiplication

Why do Klingons use cloaking devices?

Does the Defensive Duelist feat stack with the AC calculation from the Warforged's Integrated Protection trait?

How to travel between two stationary worlds in the least amount of time? (time dilation)

Convenience stores in India

Is there an easy way to index by a binary vector / mask?

Interview Question - Card betting

How do both sides know the MTU

Why did moving the mouse cursor cause Windows 95 to run more quickly?

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

Was Wolfgang Unzicker the last Amateur GM?

How serious is plagiarism in a master’s thesis?

Can a wizard delay learning new spells from leveling up to learn different spells later?

How did שְׁלֹמֹה (shlomo) become Solomon?

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 7 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 9 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
  8 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 7 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 9 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
  8 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 7 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 9 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 7 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 9 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 7 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 9 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 7 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

edited 7 hours ago

Kirk Saunders

51922 silver badges1010 bronze badges

asked 9 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 9 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 8 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 8 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 8 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 8 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

share|improve this answer

answered 8 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

answered 8 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges

answered 8 hours ago

Erik DarlingErik Darling

25.4k1313 gold badges7777 silver badges127127 bronze badges