>I'm still far from a expert on firebird and sql. If someone can help me to >improve this code, i will be grateful. > >1) GOAL: I need to make a search for a partial word in several fields of the >customers table. > >2) STRUCTURE: I am using a view because i join the customers table with the >country names table and payment conditions table. > ... > >CREATE VIEW LISTA_CLIENTES (COND_PAGAMENTO_DESCRICAO, PAIS_NOME, IDCLIENTES, >CLIENTE, MORADA, CP, LOCALIDADE, PAIS, TELEFONE, >FAX, EMAIL, CONDICOES_PAGAMENTO, REGIME_IVA, PER_DESCONTO, NUNCA_BLOQUEAR, >CONTRIBUINTE, COD_CLIENTE_FAT, ATIVO, VALIDADO) >AS >select cp.DESCRICAO COND_PAGAMENTO_DESCRICAO,p.NOME PAIS_NOME,c.* >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;
Interesting, why do you have ORDER BY in the view definition? I view views as 'virtual tables', and tables have no inherent ordering. >3) THE SLOW QUERY: From Flamerobin i run the following query and is take 60 >seconds to finished: >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 Unlike STARTING, neither LIKE nor CONTAINING can use any index (exception, LIKE that starts with a constant). How many records do each table contain and what is the result of select count(*) 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 >If i change the query to: > >select first 20 * from LISTA_CLIENTES where (CLIENTE like '%crist%') order by >CLIENTE > >it runs much faster. But as more fields i had in the WHERE clause, more slow >it gets. I try using the CONTAINING function >instead of LIKE but i got no improvement. I also tried to create a PROCEDURE >to speed up but again not speed up on getting >the result. What are the plans of these two different queries? Is the difference in execution time equally visible if you query something that have less than 20 matches in total (e.g. 'where CLIENTE like '%wxtwaq%')? Generally, databases like Firebird are good when you allow them to use indexes. Typically, indexes will be useful when they can be used in WHERE or JOIN clauses, less so when used only for ordering. Your queries cannot use indexes for the WHERE clause, for the join to COND_PAGAMENTO the primary key will be used, whereas an index for PAISES may be used if you have an index for CODIGO_ISO_3166_1 (whether it will be useful or not depends on its selectivity, how many different values are there for PAISES.CODIGO_ISO_3166_1?). Also, when you say that as you add more fields to the WHERE clause the slower it gets, are there any fields in particular that slows things down? Is it the same if you add a field from CLIENTES or one of the other tables and does it matter what size the field is? Set
