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
meme (imported from SE)
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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.