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.

[Non-text portions of this message have been removed]