Performance when comparing Varchar fields in SQL Server

Asked

Viewed 453 times

0

To SQL below is causing much slowness by the fact that I use the

Right(L.nrDiscado, Len(D.Descricao))

(believe me) to compare with the field D.Descricao:

create table #TempNrDiscado (NrDiscado varchar(150))

Insert into #TempNrDiscado
Select distinct L.nrDiscado from #TempLigacoes L with(nolock) 
   inner join GI_Dispositivo D with(nolock) on  
      Right(L.nrDiscado, Len(D.Descricao)) = D.Descricao

Does anyone know any way I could make that comparison?

Note: if I put a number 10 in place of the Len(D.Descricao) (Right(L.nrDiscado, Len(10)) he goes fast.

sql performance

  • Text comparison (string) are longer, and in your case have two complicated functions (RIGHT and LEN) makes the process more costly, has also a problem perhaps, because do not compare this INNER JOIN by the key that would be correct, you yourself proved that by putting the number 10 the process flows better. I ask why join with texts what is the scenario?

  • 2

    If the field will only be searched this way, it may be the case to create a column/index more suitable for search and (although it seems strange) store inverted. Thus, searches will use index, and a LIKE with % on the right solves. Now, if you want something more traditional, it would be cool to [Dit] the question and give more details of data structures and application requirements.

  • If I put the 10 in place of Len(), the result is on time!

  • 2

    Just to complement, if the data is UTF-8 both Len and right are required to scan all strings from start to finish, and it costs a lot of money. At these times the people who speak ill of ISO disappear :)

1 answer

0

I ended up solving this by having to work with some temporary tables in order to get a single value to do JOIN instead of Right(L.nrDiscado, Len(D.Descricao)) as said here that causes a lot of slowness.

The issue of working with the key as stated here too, is not possible in this case, because the form of linking the information is only in this way.

Thank you all for your help!

Browser other questions tagged sql performance

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