Ttdfjt

What's the minimum number of sensors for a hobby GPS waypoint-following UAV?

QGIS Zanzibar how to crop?

How can I calculate the sum of 2 random dice out of a 3d6 pool in AnyDice?

What's the point of having a RAID 1 configuration over incremental backups to a secondary drive?

Mathematica notebook opening off the screen

Was I subtly told to resign?

Constructive proof of existence of free algebras for infinitary equational theories

Matchmaker, Matchmaker, make me a match

Has anyone in space seen or photographed a simple laser pointer from Earth?

Are randomly-generated passwords starting with "a" less secure?

Is "I do not want you to go nowhere" a case of "DOUBLE-NEGATIVES" as claimed by Grammarly?

Did the Vulgar Latin verb "toccare" exist?

RPI3B+: What are the four components below the HDMI connector called?

Is a request to book a business flight ticket for a graduate student an unreasonable one?

How would vampires avoid contracting diseases?

Would dual wielding daggers be a viable choice for a covert bodyguard?

How did the hit man miss?

How to loop for 3 times in bash script when docker push fails?

Why isn't pressure filtration popular compared to vacuum filtration?

Why doesn't sea level show seasonality?

Storming Area 51

Received a dinner invitation through my employer's email, is it ok to attend?

How were Martello towers supposed to work?

Managing and organizing the massively increased number of classes after switching to SOLID?

How can I make this execution plan more efficient?

Can I make this multiple join query faster?Index Help - Plan has 2 index-scans, query is slow & high-waitDeadlock — Way to make this better?Row estimates always too lowProblem query - execution planWhy 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;

2

1

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

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 execution-plan sql-server-2017 type-conversion

share|improve this question

edited Jul 3 at 12:36

Josh Darnell

10.3k33 gold badges2323 silver badges5252 bronze badges

asked Jul 2 at 15:49

Merveille TchoudaMerveille Tchouda

1322 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Jul 2 at 16:04
  
  

  
  
  
  

add a comment | 

2

1

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

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 execution-plan sql-server-2017 type-conversion

share|improve this question

edited Jul 3 at 12:36

Josh Darnell

10.3k33 gold badges2323 silver badges5252 bronze badges

asked Jul 2 at 15:49

Merveille TchoudaMerveille Tchouda

1322 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Jul 2 at 16:04
  
  

  
  
  
  

add a comment | 

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

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 execution-plan sql-server-2017 type-conversion

share|improve this question

edited Jul 3 at 12:36

Josh Darnell

10.3k33 gold badges2323 silver badges5252 bronze badges

asked Jul 2 at 15:49

Merveille TchoudaMerveille Tchouda

1322 bronze badges

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|improve this question

edited Jul 3 at 12:36

Josh Darnell

10.3k33 gold badges2323 silver badges5252 bronze badges

asked Jul 2 at 15:49

Merveille TchoudaMerveille Tchouda

1322 bronze badges

share|improve this question

edited Jul 3 at 12:36

Josh Darnell

10.3k33 gold badges2323 silver badges5252 bronze badges

asked Jul 2 at 15:49

Merveille TchoudaMerveille Tchouda

1322 bronze badges

edited Jul 3 at 12:36

Josh Darnell

10.3k33 gold badges2323 silver badges5252 bronze badges

edited Jul 3 at 12:36

Josh Darnell

10.3k33 gold badges2323 silver badges5252 bronze badges

asked Jul 2 at 15:49

Merveille TchoudaMerveille Tchouda

1322 bronze badges

asked Jul 2 at 15:49

Merveille TchoudaMerveille Tchouda

1322 bronze badges

1 Answer
1

active

oldest

votes

10

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 Jul 2 at 16:07

Erik DarlingErik Darling

25.7k1313 gold badges7878 silver badges128128 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
);



);

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

10

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 Jul 2 at 16:07

Erik DarlingErik Darling

25.7k1313 gold badges7878 silver badges128128 bronze badges

add a comment | 

10

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 Jul 2 at 16:07

Erik DarlingErik Darling

25.7k1313 gold badges7878 silver badges128128 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 Jul 2 at 16:07

Erik DarlingErik Darling

25.7k1313 gold badges7878 silver badges128128 bronze badges

share|improve this answer

answered Jul 2 at 16:07

Erik DarlingErik Darling

25.7k1313 gold badges7878 silver badges128128 bronze badges

answered Jul 2 at 16:07

Erik DarlingErik Darling

25.7k1313 gold badges7878 silver badges128128 bronze badges

answered Jul 2 at 16:07

Erik DarlingErik Darling

25.7k1313 gold badges7878 silver badges128128 bronze badges