Performance of sql server with concatenated joins

Asked

Viewed 66 times

1

Make a INNER JOIN with the keys concatenated, as below, breaks and/or impairs the performance of the bank?

    INNER JOIN Totvs12.dbo.SE1010 receber 
    ON receber.E1_FILIAL + receber.E1_NUM + receber.E1_PREFIXO = pedido.C5_FILIAL + pedido.C5_NOTA + pedido.C5_SERIE 


   INNER JOIN Totvs12.dbo.SF2010 nota 
   ON pedido.C5_FILIAL + pedido.C5_NOTA  = nota.F2_FILIAL + nota.F2_DOC
sql sql-server

1 answer

5


In the code snippet 

INNER JOIN Totvs12.dbo.SE1010 receber 
ON receber.E1_FILIAL + receber.E1_NUM + receber.E1_PREFIXO = pedido.C5_FILIAL + pedido.C5_NOTA + pedido.C5_SERIE

this concatenation of columns is nothing more than an expression, which makes the predicate non sargable. If the columns receber.E1_FILIAL, receber.E1_NUM and receber.E1_PREFIXO correspond to the columns pedido.C5_FILIAL, pedido.C5_NOTA and pedido.C5_SERIE, here is suggested amendment:

INNER JOIN Totvs12.dbo.SE1010 receber 
ON receber.E1_FILIAL = pedido.C5_FILIAL
   and receber.E1_NUM = pedido.C5_NOTA
   and receber.E1_PREFIXO =  pedido.C5_SERIE

I suggest reading the article "Building Efficient T-SQL Code: Sargability”.

  • 1

    Nice article, too bad I could not yet read it with the zeal it deserves. I also liked the stylistic marks of the author of the linked text ;-)

  • @Jeffersonquesado Grateful for the comments. The article also has a file version, in PDF format; it is possible to print it and then read the text calmly. And, in addition, we need to publicize the concept sargability, because it is the starting point for optimization of SQL queries.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.