2

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

  • 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
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).

NUTS

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.

NUTS

NUTS

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.

NUTS

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.