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" <peixinhosda...@yahoo.com> 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] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
