On 7/11/2012 9:48 PM, Alexandre Benson Smith wrote: > > Em 11/7/2012 14:23, K Z escreveu: > > > > Hello, > > > > 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 TABLE CLIENTES > > ( > > IDCLIENTES INTEGER NOT NULL, > > CLIENTE VARCHAR(100) NOT NULL COLLATE UNICODE_CI_AI, > > MORADA VARCHAR(100) NOT NULL COLLATE UNICODE_CI_AI, > > CP VARCHAR(10) DEFAULT NULL COLLATE UNICODE_CI_AI, > > LOCALIDADE VARCHAR(50) NOT NULL COLLATE UNICODE_CI_AI, > > PAIS VARCHAR(2) DEFAULT 'PT' NOT NULL COLLATE UNICODE_CI_AI, > > TELEFONE VARCHAR(15) DEFAULT NULL COLLATE UNICODE_CI_AI, > > FAX VARCHAR(15) DEFAULT NULL COLLATE > > UNICODE_CI_AI, > > EMAIL VARCHAR(100) DEFAULT NULL COLLATE UNICODE_CI_AI, > > CONDICOES_PAGAMENTO SMALLINT DEFAULT 1 NOT NULL, > > REGIME_IVA VARCHAR(1) DEFAULT 'G' NOT NULL COLLATE UNICODE_CI_AI, > > PER_DESCONTO DECIMAL(6,2) DEFAULT 0 NOT NULL, > > NUNCA_BLOQUEAR VARCHAR(1) DEFAULT 'F' NOT NULL COLLATE UNICODE_CI_AI, > > CONTRIBUINTE VARCHAR(25) DEFAULT NULL COLLATE UNICODE_CI_AI, > > COD_CLIENTE_FAT VARCHAR(15) DEFAULT NULL COLLATE UNICODE_CI_AI, > > ATIVO VARCHAR(1) DEFAULT 'T' NOT NULL COLLATE UNICODE_CI_AI, > > VALIDADO VARCHAR(1) DEFAULT 'F' NOT NULL COLLATE UNICODE_CI_AI, > > CONSTRAINT INTEG_26 PRIMARY KEY (IDCLIENTES) > > ); > > > > CREATE INDEX IDX_CLIENTES1 ON CLIENTES (CLIENTE); > > CREATE INDEX IDX_CLIENTES2 ON CLIENTES (PAIS); > > CREATE INDEX IDX_CLIENTES3 ON CLIENTES (CONDICOES_PAGAMENTO); > > > > CREATE TABLE COND_PAGAMENTO > > ( > > IDCOND_PAGAMENTO INTEGER NOT NULL, > > DESCRICAO VARCHAR(45) NOT NULL COLLATE UNICODE_CI_AI, > > QTD_DIAS > > SMALLINT DEFAULT 0, > > ENTREGA_PAGAMENTO VARCHAR(1) DEFAULT 'T' NOT NULL COLLATE UNICODE_CI_AI, > > CONSTRAINT INTEG_14 PRIMARY KEY (IDCOND_PAGAMENTO) > > ); > > > > CREATE TABLE PAISES > > ( > > IDPAISES INTEGER NOT NULL, > > NOME VARCHAR(60) NOT NULL COLLATE UNICODE_CI_AI, > > CODIGO_ISO_3166_1 VARCHAR(2) NOT NULL COLLATE UNICODE_CI_AI, > > VALOR_PORTES DECIMAL(10,2) DEFAULT NULL, > > ISENCAO_PORTES DECIMAL(10,2) DEFAULT NULL, > > CONSTRAINT INTEG_79 PRIMARY KEY (IDPAISES) > > ); > > > > 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; > > > > > > 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 > > > > 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. > > > > I use this kind of code with other tables and > > it runs very fast. I also tried using MySQL for comparing and this > exact query runs in milliseconds. I tried to read docs in the Firebird > site and still no improvements. > > Can anyone help me with this problem? > > > > Thank you. > > > > > > This kind of search criteria (like '%crist%') will not use an index, so > the size of the tables would impact on the time to get the records. I > think that if you just filter using CLIENTE like '%crist%' is faster > because it starts by this table, but when you put the fields from the > other tables the optimizer thinks that will be faster to start from > table PAIS for example, and thus the final result is slow. There is no > way to make this kind of query to be fast, it simply cannot use any > index to speed it up and worse sicne you are searching for the same > information on fields from a bunch of tables the optimizer will choose > very bad plans because it will start from the table with fewer rows even > when the data is in fact on the larger table. CONTAINING will no help as > you already experienced and the stored procedure will not help either... > > You could "fool" the optimizer forcing the table CLIENTES to be the > first one scanned using a query like: > > 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 > left 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; > > *But* it will be slower if the real searched data resides on table PAIS... > > I use a diferent approach to help the user to search for records, first > the user point wich field it wants to search (for example the Name) then > I do a search in 3 steps: > 1.) search for the exact name: > Select * from Lista_Clientes where Cliente = 'something' > > if I find just one record, I will return this value, if there is more > than one I would present a grid, so the user could choose from the > available records, if there is no record I will go to step 2 > > 2.) search for the beginning > > Select * from Lista_Clientes where Cliente like 'something%' > > if I find just one record, I will return this value, if there is more > than one I would present a grid, so the user could choose from the > available records, if there is no record I will go to step 3 > > 3.) sarch for the string in anywhere in the specified field > Select * from Lista_Clientes where Cliente like '%something%' > > if I find just one record, I will return this value, if there is more > than one I would present a grid, so the user could choose from the > available records, if there is no record the user can try another search > > The step 1 and 2 are really fast no matter what the size of the table > because it will use an index on the field, step 3 will be slow since no > index could be used, but in the vast majority of the cases step 1 and 2 > will return the desired records, step 3 will not be executed very often. > As an aditional feature user could put wildcards on the searched string, > if the search string contains any wildcard the step 1 is bypassed and if > the first character of the string is an wildcard the routine would > bypass step 1 and 2 and go directly for step 3. > > I know that what you are trying to achieve is friendly to the user, he > just throw some data and the system tries to find it anywhere, but it > will not be fast. IMHO you should think about the general case, how > often the user want to find a costumer by a string but don't know if > that string is a name or a country or a phone number ??? how much bogus > information will it return ? (ex. costumers who has that piece of the > string on the street address and the user are searching for the name of > the company), I think it will return a lot of uninteresting information. > > see you >
If you have indexes on any or all of the search columns, you can change your query to this: 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 like 'search%' allows firebird to use an index, and most people will type the first letters of a name or telephone number to search, not random letters in the middle of the name Regards Gary [Non-text portions of this message have been removed]
