merveille tchouda imported from SE
sql-server sql-server-2017
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

Top Answer
Erik Darling
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][4].


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.

  [1]: https://i.stack.imgur.com/8xt66.png
  [2]: https://i.stack.imgur.com/9gK1n.png
  [3]: https://i.stack.imgur.com/9TDNx.png
  [4]: https://www.brentozar.com/archive/2016/10/max-data-types/
  [5]: https://i.stack.imgur.com/bP1ZA.png
How can I make this execution plan more efficient?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.