Re: [firebird-support] Re: Slow query with like '%xxx%' clause - PROBLEM SOLVED!
-- Original Message --- From: "peixinhosdalua" > CREATE VIEW LISTA_CLIENTES_PESQUISA (IDCLIENTES, PESQUISA) > AS > > select c.IDCLIENTES, > cp.DESCRICAO||p.NOME||c.IDCLIENTES||c.CLIENTE||c.MORADA|| c.CP||c.LOCALIDADE|| c.PAIS||c.TELEFONE||c.FAX|| > c.EMAIL|| > c.PER_DESCONTO|| > c.CONTRIBUINTE||c.COD_CLIENTE_FAT > FROM CLIENTES c > inner join PAISES p on p.CODIGO_ISO_3166_1=c.PAIS > inner join COND_PAGAMENTO cp on cp.IDCOND_PAGAMENTO=c.CONDICOES_PAGAMENTO > order by c.CLIENTE; --- End of Original Message --- Might I suggest, also: coalesce(cp.descricao, '') || coalesce(p.nome, '') || ... ? (any single null in a concatenation will cause the result to be null) I realize there's a join involved here, but you could still make this a bit more reusable by, on each table to be searched, adding a computed-by column that does this work for you: alter table clientes add search_field computed by (coalesce(cp.descricao, '') || ... ) You could retain the view, but the view would now only need to do: clientes.search_field || paises.search_field || ... And you'd be able to do your searches on single tables anytime you like, without repeating the full query, or using the view that does more than you need. -Philip
Re: [firebird-support] Re: Slow query with like '%xxx%' clause
Olá está em www.op3racional.eu/x/TESTE.FDB. Obrigado pela ajuda. From: "fabianoas...@gmail.com" To: firebird-support@yahoogroups.com Sent: Friday, July 13, 2012 2:52 PM Subject: Re: [firebird-support] Re: Slow query with like '%xxx%' clause I agree but: I have an system with 50.000+ clients with "%some%" too and it works vary 'fast'. In my system the sql executes in less than a second! It does not use indexes as well but 50.000 records is too little to take 20s, 30s even 60s to run! Yes Firebird dont will use the indexes but the key is how make the SQL statement as I posted. If you want send me this database and I will rewrite your SQL code to you. Fabiano. 2012/7/13 Tupy... nambá > ** > > > Doesn´t matter if just using Clientes or having a join or a view, a search > with ' like "%word%" ' will not bring the results you want, this will ever > use table scan and will be relatively slow for your needs. > > Direct in the database, the only approach were you will can speed up your > searches will be using a solution like suggested by mr.Frank > Schlottmann-Gödde. > > Using only sql commands and clauses, no way to get what you want. Ms.Ann > Harrison already said "Firebird cannot use an index on partial word matches > unless it knows the initial characters, so your queries are all going to > require full table scans."(On Thu, Jul 12, 2012 at 12:08 PM). To this way, > final words. > > > From: peixinhosdalua > To: firebird-support@yahoogroups.com > Sent: Friday, July 13, 2012 9:57 AM > Subject: [firebird-support] Re: Slow query with like '%xxx%' clause > > > What you suggest is not a solution because just using the tables CLIENTES, > without any join or inside a view, the problem remains. I tried it. > > Also, i do not want to make 'word%' search. What i need is '%word%'. > > > > ++ > > 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] ++ 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] Re: Slow query with like '%xxx%' clause
I agree but: I have an system with 50.000+ clients with "%some%" too and it works vary 'fast'. In my system the sql executes in less than a second! It does not use indexes as well but 50.000 records is too little to take 20s, 30s even 60s to run! Yes Firebird dont will use the indexes but the key is how make the SQL statement as I posted. If you want send me this database and I will rewrite your SQL code to you. Fabiano. 2012/7/13 Tupy... nambá > ** > > > Doesn´t matter if just using Clientes or having a join or a view, a search > with ' like "%word%" ' will not bring the results you want, this will ever > use table scan and will be relatively slow for your needs. > > Direct in the database, the only approach were you will can speed up your > searches will be using a solution like suggested by mr.Frank > Schlottmann-Gödde. > > Using only sql commands and clauses, no way to get what you want. Ms.Ann > Harrison already said "Firebird cannot use an index on partial word matches > unless it knows the initial characters, so your queries are all going to > require full table scans."(On Thu, Jul 12, 2012 at 12:08 PM). To this way, > final words. > > > From: peixinhosdalua > To: firebird-support@yahoogroups.com > Sent: Friday, July 13, 2012 9:57 AM > Subject: [firebird-support] Re: Slow query with like '%xxx%' clause > > > What you suggest is not a solution because just using the tables CLIENTES, > without any join or inside a view, the problem remains. I tried it. > > Also, i do not want to make 'word%' search. What i need is '%word%'. > > > > ++ > > 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] ++ 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] Re: Slow query with like '%xxx%' clause
Doesn´t matter if just using Clientes or having a join or a view, a search with ' like "%word%" ' will not bring the results you want, this will ever use table scan and will be relatively slow for your needs. Direct in the database, the only approach were you will can speed up your searches will be using a solution like suggested by mr.Frank Schlottmann-Gödde. Using only sql commands and clauses, no way to get what you want. Ms.Ann Harrison already said "Firebird cannot use an index on partial word matches unless it knows the initial characters, so your queries are all going to require full table scans."(On Thu, Jul 12, 2012 at 12:08 PM). To this way, final words. From: peixinhosdalua To: firebird-support@yahoogroups.com Sent: Friday, July 13, 2012 9:57 AM Subject: [firebird-support] Re: Slow query with like '%xxx%' clause What you suggest is not a solution because just using the tables CLIENTES, without any join or inside a view, the problem remains. I tried it. Also, i do not want to make 'word%' search. What i need is '%word%'. ++ 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] Re: Slow query with like '%xxx%' clause
If the set that word belongs to is small and fixed, you could index on a function. If it is unbounded, you're pretty much going to have to look into something that does full text indexing (e.g., Lucene). On 7/13/2012 8:57 AM, peixinhosdalua wrote: > What you suggest is not a solution because just using the tables CLIENTES, > without any join or inside a view, the problem remains. I tried it. > > Also, i do not want to make 'word%' search. What i need is '%word%'. > > > > > > ++ > > 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 > > >
