Where clause from OUTER APPLY using LEFT function

Question

What would be a good alternative to improve my where clause that currently uses the LEFT function?

Should I create a persisted computed column and an index to it?

SELECT --columns--
FROM DesvioDesenhoSubstitutivoProposto DSP 
LEFT JOIN DesvioDesenhoSubstitutivo DS ON DS.idSubstituicaoDesenho = DSP.idSubstituicaoDesenho OUTER APPLY
  (SELECT TOP 1 D.Dendis
   FROM Interfaces.dbo.SSOLQ068 D
   WHERE DS.codigoDesenhoSubstitutivo = LEFT(D.NDISCOMPLFAB, LEN(D.NDISCOMPLFAB)-1)
   GROUP BY D.Dendis) AS DESEN

This is the current execution plan for this part:

PS: query has been simplified for posting.

Answer 1

How about:

WHERE D.NDISCOMPLFAB LIKE DS.codigoDesenhoSubstitutivo + '_'

...which will allow an index on (NDISCOMPLFAB, Dendis) to be used effectively.

Please check that the column types are the same kind between D.NDISCOMPLFAB and DS.codigoDesenhoSubstitutivo, such as both nvarchar or both varchar. If they are different, use:

WHERE D.NDISCOMPLFAB LIKE CAST(DS.codigoDesenhoSubstitutivo AS ...) + '_'

...where the ... is whatever the type of NDISCOMPLFAB is.

Also, the GROUP BY clause should be redundant because of the TOP 1.