SQL Server 2008 R2 – Where Clause from OUTER APPLY Using LEFT Function

sql serversql-server-2008-r2t-sql

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.

Best Answer

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.

Related Solutions

Sql-server – SQL Server Query: Inefficient where clause

If you don't need the other data from the NETWORK_STATUS table, how about:

select *
from dbo.NETWORK AS n
inner join dbo.vwNETWORK_KEYMSTN AS km 
  on n.Network_ID = km.Network_ID
inner join dbo.vwAPPROVAL_LATEST AS a 
  on n.Network_ID = a.Network_ID
inner join dbo.APPROVAL_VINTAGE  AS av 
  on a.Approval_ID = av.Approval_ID 
  and km.Milestone_Type_ID = av.Milestone_Type_ID
inner join dbo.NETWORK_MILESTONE AS m 
  on A.Approval_ID = m.Approval_ID 
  and km.Milestone_Type_ID = m.Milestone_Type_ID
inner join dbo.REF_MILESTONE AS rm 
  on km.Milestone_Type_ID = rm.Milestone_Type_ID
WHERE EXISTS
(
  SELECT 1 FROM dbo.NETWORK_STATUS 
    WHERE Network_ID = n.Network_ID
    and Status_Type_ID = 2
    --and Status_Type_ID = rm.Status_Type_ID
);

Understanding Strange Looking WHERE Clause in SQL Server

SQL Server does NOT have guaranteed short circuiting in that scenario. The point of this construct is so that businessLogicCondition is only evaluated as part of the WHERE clause in the case where @term1 is populated. It may still be evaluated as part of query processing, because you can only control order of evaluation in a few special cases (no pun intended).

This WHERE clause would actually have different meaning, unless businessLogicCondition actually referenced @term1 explicitly and in such a way that it could only return true if @term1 is NOT NULL:

WHERE @term1 IS NULL OR (businessLogicCondition)

businessLogicCondition may still be evaluated, but this form of the WHERE clause could, potentially, return rows based on businessLogicCondition, regardless of whether @term1 is NULL or NOT NULL.

Now, with the pertinent info added to your question:

No, in this specific case, the @term1 IS NOT NULL AND is redundant and cannot possibly help the optimizer come up with a better plan (except maybe in the case where there is a filtered index with that same). In fact in the above scenario I mentioned before I had that information, it won't lead to a better plan either, but it can change whether the results are correct or not.

It is important to note that as written SQL Server will optimize and cache an execution plan based on the first execution. It may be the case that a better plan can be used in the scenario where @term1 is NOT NULL (e.g. when @term1 is something like somestring%, it may be able to seek, but not with %somestring%). So you might consider using dynamic SQL to formulate one version of the query or the other, depending on the value of the parameter, especially if you have multiple optional parameters (I call this "the kitchen sink" procedure). The OR that remains may still be problematic; there are a lot of variable factors.

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.

Best Answer

Related Solutions

Sql-server – SQL Server Query: Inefficient where clause

Understanding Strange Looking WHERE Clause in SQL Server

Related Question