How to use the like in a field comparison in different tables?

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

For example, instead 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 non sargable < a> . 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 does not seem to me that the cause of 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)

I did not find it inefficient, except, of course, for being a non sargable . But there are several other constraints 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 conjunction of factors such as indexes available, the column list, the construct containing LIKE, and so on. In the case of your query, maybe the

       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 ...;

In this case a JOIN is more interesting than a subquery . Here's the example for 2 :

SELECT sum(sd3_sub1.D3_QUANT) AS producao
  FROM SD3010 AS sd3 WITH(NOLOCK)
       INNER JOIN SD3010 AS sd3_sub1 WITH(NOLOCK) ON sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
  WHERE DATEPART(YEAR, sd3_sub1.D3_EMISSAO) = DATEPART(YEAR, sd3.D3_EMISSAO)
    AND DATEPART(MONTH, sd3_sub1.D3_EMISSAO) = DATEPART(MONTH, sd3.D3_EMISSAO)
    AND SUBSTRING(sd3_sub1.D3_CC,1,5) = SUBSTRING(sd3.D3_CC,1,5)
    AND sd3_sub1.D3_TM = '010'
    AND sd3_sub1.D3_LOCAL BETWEEN '01' AND '02'
    AND sd3_sub1.D_E_L_E_T_ <> '*'
  GROUP BY sd3.D3_FILIAL

There were also fields without alias .

I also added WITH(NOLOCK) also, which indicates to SQL Server that it is not necessary to lock the table while reading the data is done.