2

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:

enter image description here

PS: query has been simplified for posting.

3

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.

1

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.