sql – How to use like in a comparison of fields in different tables?

Question:

How can I use LIKE for a comparison of two fields from different tables? I need to compare the first 5 characters of each field. Tried with SUBSTRING and LEFT , however, the performance is very poor.

Here are two ways I applied, with the comment flagging: 1)

    SELECT(SELECT sum(sd3_sub1.D3_QUANT)
                FROM SD3010 AS sd3_sub1         
                WHERE sd3_sub1.D3_TM = '010'                    
                    AND YEAR(D3_EMISSAO) = YEAR(sd3.D3_EMISSAO)  AND MONTH(D3_EMISSAO) = MONTH(sd3.D3_EMISSAO)
                    AND sd3_sub1.D3_LOCAL BETWEEN '01' AND '02'
                    AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                    /*AQUI UTILIZEI O LEFT*/   
                    AND LEFT(sd3_sub1.D3_CC, 5) = LEFT(sd3.D3_CC,5)
                    AND sd3_sub1.D_E_L_E_T_ <> '*') AS producao
    FROM SD3010 AS sd3

2)

    SELECT(SELECT sum(sd3_sub1.D3_QUANT)
                    FROM SD3010 AS sd3_sub1         
                    WHERE sd3_sub1.D3_TM = '010'                    
                        AND YEAR(D3_EMISSAO) = YEAR(sd3.D3_EMISSAO)  AND MONTH(D3_EMISSAO) = MONTH(sd3.D3_EMISSAO)
                        AND sd3_sub1.D3_LOCAL BETWEEN '01' AND '02'
                        AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                        /*AQUI UTILIZEI O SUBSTRING*/   
                        AND SUBSTRING(sd3_sub1.D3_CC,1,5) = SUBSTRING(sd3.D3_CC,1,5)
                        AND sd3_sub1.D_E_L_E_T_ <> '*) AS producao
    FROM SD3010 AS sd3

Answer:

Thiago, considering that the D3_EMISSAO column is probably declared as var char(8), and with the value stored in the format yyyymmdd, it seems unnecessary, and perhaps inefficient, to use the Year() and Month() functions to compare between the external and the internal query, the comparison can be performed directly in the string environment.

For example, in place of

... YEAR(D3_EMISSAO) = YEAR(sd3.D3_EMISSAO)  
AND MONTH(D3_EMISSAO) = MONTH(sd3.D3_EMISSAO)

can be used

Left(sd3_sub1.D3_EMISSAO, 6) = Left(sd3.D3_EMISSAO, 6)

Both the use of the Year/Month functions and the Left function, in this specific case, make the constraint non sargable . There's even a way to make this construct sargable by implementing something like

sd3_sub1.D3_EMISSAO between (Left(sd3.D3_EMISSAO, 6) + '01') 
                            and (Left(sd3.D3_EMISSAO, 6) + '31')

It doesn't seem to me that the cause of the low performance is the use of the Left (or Substring) function when comparing the D3_CC column. The construction

LEFT(sd3_sub1.D3_CC, 5) = LEFT(sd3.D3_CC,5)

it didn't seem inefficient to me, except, of course, that it was a non sargable construct. But there are several other restrictions present in the WHERE clause that also make it non sargable .

However, there are situations where LIKE can be more efficient than Left or Substring. But this depends on a combination of factors such as available indexes, the list_of_columns , the construction that contains LIKE etc. In the case of your query, perhaps the construction

       and sd3_sub1.D3_CC like Left(sd3.D3_CC,5) + '%'

can be more efficient.


Here's a suggestion for your code, where non sargable constructs have been replaced by other sargable constructs :

-- código #1 v3
SELECT ...,
       (SELECT Sum(sd3_sub1.D3_QUANT)
          from SD3010 as sd3_sub1         
          where sd3_sub1.D3_TM = '010'                    
               and sd3_sub1.D3_LOCAL between '01' AND '02'
               and sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
               and sd3_sub1.D3_EMISSAO between (Left(sd3.D3_EMISSAO, 6) + '01') 
                                           and (Left(sd3.D3_EMISSAO, 6) + '31')
               and sd3_sub1.D3_CC like Left(sd3.D3_CC,5) + '%'
               and sd3_sub1.D_E_L_E_T_ <> '*') as producao
  from SD3010 AS sd3
  where sd3.D_E_L_E_T_ <> '*'
        and ...;
Scroll to Top