Hello,

I made a few query to count the time. I tried with the CLIENTES having 3553 records and with 1000 records. The changes in time performance is significant! Note that now i am not testing with the VIEW (so not having any joined tabled).

30 SEG (3553 records) 9 SEG (1000 records) (with the VIEW)

select first 20 * from LISTA_CLIENTES where (CLIENTE like '%crist%' or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like '%crist%' or PAIS like '%crist%' or PAIS_NOME like '%crist%' or TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like '%crist%' or COND_PAGAMENTO_DESCRICAO like '%crist%') order by CLIENTE


20 SEG (3553 records) 6 SEG (1000 records) (without the VIEW)

select first 20 * from CLIENTES where CLIENTE like '%crist%'
or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like
'%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like
'%crist%' order by CLIENTE

total records in CLIENTES = 3553

25 SEG (3553 records) 8 SEG (1000 records) (without the VIEW)
select first 20 * from CLIENTES where CLIENTE like '%crist%'
or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like
'%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like
'%crist%'


15 SEG (3553 records) 8 SEG (1000 records) (without the VIEW)
select first 20 * from CLIENTES where CLIENTE like '%crist%'
or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like
'%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%'


4 SEG (3553 records) 2 SEG (1000 records) (without the VIEW)
select first 20 * from CLIENTES where CLIENTE like '%crist%'
or MORADA like '%crist%'

Regarding your suggestions, i need to make a search by '%word%' not 'word%'. For example, most companies have a commercial name or brand that they use and these are different from the juridic name. It is the juridic name that is inserted in the table because this table is using to make invoices. So it is standard to have companies names like: Something Brand LTD and people search for Brand and if they do it like 'Brand%' never appears.

Same can happen for phones if you search with out without prefixes. Also the VAT-ID. For example VAT's in Europe have letters at beginning but most people omitted the letters when searching for VAT-ID from their own country.

This is even more true when searching for products.

So, how can i improve the usage of the like or containing with the %word%?
Thanks.