Forgot the view!
First task is rewrite the sql only for clientes table.
Try it first and you will not a much better performance.
Next step join the other tables but not using a view.
Finally if it continue slow try changuing your sql to:
Where (clientes.name like 'test%' or clients.name like '%test%')
Create an indice for name column.
In this case Fb will use the index to search records matching the criteria.
If a litle ammount of records is found it stop the execution and then make
it by demand when you roll down
Em 12/07/2012 10:17, "peixinhosdalua" <peixinhosdalua@...> escreveu:

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

[Non-text portions of this message have been removed]