Re: [firebird-support] Slow query with like '%xxx%' clause
Wenn man die Vorschlage aus Herrn Gödde folgen wird, man kann ein Googler bauen. d.h., wenn man insert oder update macht, dann eine Tabelle mit Suchwörte und Index wird aktualiziert.. stimmt das ? This will need a complete change in the conception - this is really the only way to get speed in this kind of search -, but must be considered how frequently this kind of search will be needed and the words need to be dismounted at the insert/update time. And each gotten word will need to be converted in a record in this search table. This can mean that the accessories operations will increase the time to insert/update operations. And what will be gained as result, may mean a general loss. So, this solutions need to be clearly considered, because the positive results may not be great enough to justify its implementation, may be better stay with the current search results. Think about. Roberto Camargo, Rio de Janeiro/BR From: Frank Schlottmann-Gödde fr...@schlottmann-goedde.de To: firebird-support@yahoogroups.com Sent: Thursday, July 12, 2012 12:43 PM Subject: Re: [firebird-support] Slow query with like '%xxx%' clause On 07/12/2012 03:17 PM, peixinhosdalua wrote: 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%? You may try to create an extra search-table sth. like (searchword,tablename,fieldname,ID) and fill this on insert,update or delete using triggers on your original tables, Create one or more stored procedures that do a for select tablename,id from searchtable where searchword like '%whatever%' into .. (maybe trying a starting with first) and returns the values you want (via execute statement) . This will reduce the table scan to one table. hth fsg -- Fascinating creatures, phoenixes, they can carry immensely heavy loads, their tears have healing powers and they make highly faithful pets. - J.K. Rowling ++ 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 [Non-text portions of this message have been removed]
Re: [firebird-support] Slow query with like '%xxx%' clause
I have a tendency to not agree with any solution that implies a duplication or a increase in the Kb used. There must be another solution to solve this problem. From: Tupy... nambá anhangu...@yahoo.com To: firebird-support@yahoogroups.com firebird-support@yahoogroups.com Sent: Friday, July 13, 2012 1:22 PM Subject: Re: [firebird-support] Slow query with like '%xxx%' clause Wenn man die Vorschlage aus Herrn Gödde folgen wird, man kann ein Googler bauen. d.h., wenn man insert oder update macht, dann eine Tabelle mit Suchwörte und Index wird aktualiziert.. stimmt das ? This will need a complete change in the conception - this is really the only way to get speed in this kind of search -, but must be considered how frequently this kind of search will be needed and the words need to be dismounted at the insert/update time. And each gotten word will need to be converted in a record in this search table. This can mean that the accessories operations will increase the time to insert/update operations. And what will be gained as result, may mean a general loss. So, this solutions need to be clearly considered, because the positive results may not be great enough to justify its implementation, may be better stay with the current search results. Think about. Roberto Camargo, Rio de Janeiro/BR From: Frank Schlottmann-Gödde fr...@schlottmann-goedde.de To: firebird-support@yahoogroups.com Sent: Thursday, July 12, 2012 12:43 PM Subject: Re: [firebird-support] Slow query with like '%xxx%' clause On 07/12/2012 03:17 PM, peixinhosdalua wrote: 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%? You may try to create an extra search-table sth. like (searchword,tablename,fieldname,ID) and fill this on insert,update or delete using triggers on your original tables, Create one or more stored procedures that do a for select tablename,id from searchtable where searchword like '%whatever%' into .. (maybe trying a starting with first) and returns the values you want (via execute statement) . This will reduce the table scan to one table. hth fsg -- Fascinating creatures, phoenixes, they can carry immensely heavy loads, their tears have healing powers and they make highly faithful pets. - J.K. Rowling ++ 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 [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
Re: [firebird-support] Slow query with like '%xxx%' clause
On 13.07.2012 14:22, Tupy... nambá wrote: Wenn man die Vorschlage aus Herrn Gödde folgen wird, man kann ein Googler bauen. d.h., wenn man insert oder update macht, dann eine Tabelle mit Suchwörte und Index wird aktualiziert.. stimmt das ? Genau, but don't forget the delete :-) If this kind of search is needed quite often and search speed is an issue, it is the easiest solution I can think of. Another approach could be to add a somehow normalized representation of the data (removing stop-words from customer names, remove letters from VAT-Codes etc.) This can mean that the accessories operations will increase the time to insert/update operations. The impact is not that big from my experience. We use a similar approach to allow a global search over lot's of different tables. fsg -- Fascinating creatures, phoenixes, they can carry immensely heavy loads, their tears have healing powers and they make highly faithful pets. - J.K. Rowling
Re: [firebird-support] Slow query with like '%xxx%' clause
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/
Re: [firebird-support] Slow query with like '%xxx%' clause
On 12-7-2012 16:24, Tupy... nambá wrote: Caro peixinhosdalua, There is no way to increase such kind of searches in a database, because the searches are made line per line, searching inside each line by the occurrence of your needed search by displacement of the searching word, char by char. snip For this kind of search, there is no tip and no trick to increase the speed. Agreed with Roberto's (and others') remarks about performance in SQL/Firebird. Perhaps you could use a full text search engine like Lucene or Sphinx for these searches in your database See e.g. http://www.firebirdsql.org/en/sphinx-full-text-search/ (don't know whether it's up to date or not) Good luck, Reinier
