[firebase-br] Melhorar consulta

Sex Ago 16 14:16:48 -03 2013

Bom dia/tarde/noite Carlos.

Meu nobre amigo, posso estar redondamente errado, mas acredito que os 
operadores "CONTAINING" é que vão impedir qualquer melhora de 
performance, pois na prática, esses trechos nunca utilizarão qualquer 
índice.

Mesmo que as outras condições de relacionamento entre as tabelas 
envolvidas utilizem índices, sempre haverá um processamento de texto 
para cada registro/linha selecionado pelas condições.

Talvez precisasse reformular essa consulta, de forma a evitar o uso do 
operador CONTAINING.

É uma tarefa bem complicada.

Aqui vai apenas uma sugestão que pode não ser viável para o seu caso, 
mas que poderá dar idéias novas para chegar à solução ideal para o seu caso:

1 - Criar uma tabela de palavras compartilhadas, contendo apenas um 
código (numérico) e a palavra compartilhada (um varchar).

2 - Criar uma tabela filha/detalhe para cada uma das tabelas que possuem 
um campo/coluna de nome a ser pesquisado, apenas para relacionar as 
palavras que constam no(s) nome(s) contidos naquele registro/linha.

3 - Quando inserir um novo registro em uma dessas tabelas que contém um 
nome a ser pesquisado, dividir o nome em palavras e cadastrá-las na 
tabela de palavras compartilhadas, caso ainda não constem lá, incluindo 
também, na respectiva tabela filha/detalhe, a relação entre o registro 
atual e as respectivas palavras.

4 - Quando alterar um ou mais campos/colunas de nome, fazer os 
respectivos ajustes (nas tabelas filhas) para manter tudo sincronizado.

5 - Quando for efetuar a consulta, dividir o texto informado em 
palavras, e gerar as condições que pesquisem essas palavras na tabela de 
palavras compartilhadas e também relacionem com as respectivas tabelas 
filhas. Dessa forma, não necessitaria utilizar o operador CONTAINING, e 
utilizariam apenas os respectivos índices.

Exemplo:

1 - Criação da tabela de palavras compartilhadas.

CREATE TABLE PALAVRA(
   CODIGO INTEGER DEFAULT 0 NOT NULL,
   TEXTO VARCHAR(100) CHARACTER SET ISO8859_1 NOT NULL COLLATE PT_BR,
   REFERENCIAS INTEGER DEFAULT 1 NOT NULL,
   CONSTRAINT PK_PALAVRA PRIMARY KEY(CODIGO),
   CONSTRAINT UK_PALAVRA_TEXTO UNIQUE(PALAVRA));

COMMENT ON TABLE PALAVRA IS 'Palavras compartilhadas que formam os nomes';
COMMENT ON COLUMN PALAVRA.CODIGO IS 'Codigo da palavra compartilhada';
COMMENT ON COLUMN PALAVRA.TEXTO IS 'A palavra compartilhada propriamente 
dita';
COMMENT ON COLUMN PALAVRA.REFERENCIAS IS 'A quantidade de vezes que essa 
palavra esta sendo utilizada';

SET TERM ^ ;

CREATE TRIGGER TG_PALAVRA_BI FOR PALAVRA BEFORE INSERT AS
BEGIN
   -- Nao informou o codigo da nova palavra compartilhada?
   IF (NEW.CODIGO = 0) THEN
     -- Calcula o codigo da nova palavra compartilhada (use um 
gerador/sequence se desejar).
     SELECT
       COALESCE(MAX(CODIGO), 0) + 1
     FROM
       PALAVRAS
     INTO
       NEW.CODIGO;
END^

CREATE PROCEDURE INCLUA_PALAVRA(
   NOVA_PALAVRA VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE PT_BR
)RETURNS(
   CODIGO INTEGER
)AS
BEGIN
   -- Verifica se ja consta a palavra informada.
   SELECT CODIGO FROM PALAVRA WHERE TEXTO = :NOVA_PALAVRA INTO :CODIGO;
   -- Ainda nao consta?
   IF (ROW_COUNT = 0) THEN
     -- Insere a nova palavra compartilhada e obtem o codigo dela.
     INSERT INTO PALAVRA(TEXTO)VALUES(:NOVA_PALAVRA)RETURNING CODIGO 
INTO :CODIGO;
   ELSE
     -- Incrementa o contador de referencias dessa palavra.
     UPDATE PALAVRA SET REFERENCIAS = REFERENCIAS + 1 WHERE CODIGO = 
:CODIGO;
   -- Retorna o codigo da palavra informada.
   SUSPEND;
END^

COMMENT ON PROCEDURE INCLUA_PALAVRA IS 'Inclui uma nova palavra 
compartilhada'^
COMMENT ON PARAMETER INCLUA_PALAVRA.NOVA_PALAVRA IS 'A nova palavra a 
ser compartilhada'^
COMMENT ON PARAMETER INCLUA_PALAVRA.CODIGO IS 'O codigo da nova palavra 
compartilhada'^

CREATE PROCEDURE EXCLUA_PALAVRA(
   CODIGO INTEGER
)AS
DECLARE VARIABLE REFERENCIAS INTEGER;
BEGIN
   -- Obtem a quantidade de referencias da palavra informada.
   SELECT REFERENCIAS FROM PALAVRA WHERE CODIGO = :CODIGO INTO :REFERENCIAS;
   -- Encontrou alguma palavra com o codigo informado?
   IF (REFERENCIAS IS NOT NULL) THEN
     -- So consta essa unica referencia de uso?
     IF (REFERENCIAS = 1) THEN
       -- Exclui essa palavra, ja que ela nao esta sendo mais utilizada.
       DELETE FROM PALAVRA WHERE CODIGO = :CODIGO;
     ELSE
       -- Decrementa o contador de referencias dessa palavra.
       UPDATE PALAVRA SET REFERENCIAS = REFERENCIAS - 1 WHERE CODIGO = 
:CODIGO;
END^

COMMENT ON PROCEDURE EXCLUA_PALAVRA IS 'Exclui uma palavra compartilhada'^
COMMENT ON PARAMETER EXCLUA_PALAVRA.CODIGO IS 'O codigo da palavra 
compartilhada a ser excluida'^

SET TERM ; ^

Dessa forma, use as stored procedures INCLUA_PALAVRA e EXCLUA_PALAVRA 
para gerenciar a tabela de palavras compartilhadas.

Para a tabela PESSOAFISICA, poderíamos criar uma tabela detalhe como a 
seguinte:

CREATE TABLE PESSOAFISICAPALAVRA(
   IDENTIFICADOR INTEGER NOT NULL,
   CODIGO_PALAVRA INTEGER NOT NULL,
   CONSTRAINT PK_PESSOAFISICAPALAVRA PRIMARY 
KEY(IDENTIFICADOR,CODIGO_PALAVRA),
   CONSTRAINT FK_PESSOAFISICAPALAVRA_PF FOREIGN KEY(IDENTIFICADOR) 
REFERENCES PESSOAFISICA ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT FK_PESSOAFISICAPALAVRA_PL FOREIGN KEY(CODIGO_PALAVRA) 
REFERENCES PALAVRAS ON UPDATE CASCADE ON DELETE CASCADE);

COMMENT ON TABLE PESSOAFISICAPALAVRA IS 'Palavras que formam os nomes 
das pessoas fisicas';
COMMENT ON COLUMN PESSOAFISICAPALAVRA.IDENTIFICADOR IS 'Identificador da 
pessoa fisica';
COMMENT ON COLUMN PESSOAFISICAPALAVRA.CODIGO_PALAVRA IS 'Codigo da 
palavra que forma o nome dessa pessoa fisica';

Vamos criar uma stored procedure para podermos facilmente gerenciar 
essas tabelas detalhes:

SET TERM ^ ;

CREATE PROCEDURE RELACIONA_PALAVRAS(
   NOME VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE PT_BR,
   TABELADETALHE VARCHAR(30),
   CAMPOSCHAVESINCLUSAO VARCHAR(100),
   VALORESCHAVESINCLUSAO VARCHAR(200),
   CONDICOESPESQUISA VARCHAR(200)
)AS
DECLARE VARIABLE CODIGO INTEGER;
DECLARE VARIABLE PALAVRA VARCHAR(100);
DECLARE VARIABLE POSICAO SMALLINT;
BEGIN
   -- Laco de exclusao dos relacionamentos atuais.
   FOR EXECUTE STATEMENT ('SELECT CODIGO FROM ' || :TABELADETALHE || ' 
WHERE ' || :CONDICOESPESQUISA) INTO :CODIGO DO
     -- Exclui a palavra atual.
     EXECUTE PROCEDURE EXCLUA_PALAVRA(:CODIGO);
   -- Exclui os relacionamentos atuais.
  EXECUTE STATEMENT ('DELETE FROM ' || :TABELADETALHE || ' WHERE ' || 
:CONDICOESPESQUISA);
   -- Remove os espacos em branco excedentes do nome informado.
   NOME = TRIM(NOME);
   -- Laco de processamento do nome.
   WHILE (NOME > '') DO
   BEGIN
     -- Obtem a posicao do espaco em branco dentro do nome.
     POSICAO = POSITION(' ' IN NOME);
     -- Nao consta?
     IF (CODIGO = 0) THEN
     BEGIN
       -- Trata-se da ultima palavra do nome.
       PALAVRA = TRIM(NOME);
       -- Sem mais palavras a processar.
       NOME = '';
     END -- IF
     ELSE
     BEGIN
       -- Obtem a proxima palavra do nome.
       PALAVRA = SUBSTRING(NOME FROM 1 FOR (POSICAO - 1));
       -- Remove essa palavra do nome.
       NOME = TRIM(SUBSTRING(NOME FROM POSICAO FOR 200));
     END -- ELSE
     -- Insere ou reutiliza a palavra compartilhada e obtem o seu 
respectivo codigo.
     SELECT CODIGO FROM INCLUA_PALAVRA(:PALAVRA) INTO :CODIGO;
     -- Insere o registro da nova palavra relacionada a essa pessoa fisica.
     EXECUTE STATEMENT ('INSERT INTO ' || :TABELADETALHE || '(' || 
:CAMPOSCHAVES || ',CODIGO)VALUES(' || :VALORESCHAVES || ', ' || :CODIGO 
|| ')');
   END -- WHILE
END^

COMMENT ON PROCEDURE RELACIONA_PALAVRAS IS 'Relaciona as palavras do 
nome com um registro';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.NOME IS 'Nome a ser processado';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.TABELADETALHE IS 'Nome da tabela 
detalhe onde ficara o relacionamento';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.CAMPOSCHAVESINCLUSAO IS 'Nomes 
dos campos chaves, separados por virgula, para inclusao';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.VALORESCHAVESINCLUSAO IS 
'Valores dos campos chaves, separados por virgula, para inclusao';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.CONDICOESPESQUISA IS 'Valores 
dos campos chaves, separados por virgula, para consulta e exclusao';

SET TERM ; ^

Agora vamos criar um gatilho para a tabela PESSOAFISICA, para que esses 
relacionamentos entre as palavras e o nome dos registros sejam feitas 
automaticamente:

SET TERM ^ ;

CREATE PROCEDURE RELACIONA_PALAVRAS(
   NOME VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE PT_BR,
   TABELADETALHE VARCHAR(30),
   CAMPOSCHAVESINCLUSAO VARCHAR(100),
   VALORESCHAVESINCLUSAO VARCHAR(200),
   CONDICOESPESQUISA VARCHAR(200)
)AS
DECLARE VARIABLE CODIGO INTEGER;
DECLARE VARIABLE PALAVRA VARCHAR(100);
DECLARE VARIABLE POSICAO SMALLINT;
BEGIN
   -- Laco de exclusao dos relacionamentos atuais.
   FOR EXECUTE STATEMENT ('SELECT CODIGO FROM ' || :TABELADETALHE || ' 
WHERE ' || :CONDICOESPESQUISA) INTO :CODIGO DO
     -- Exclui a palavra atual.
     EXECUTE PROCEDURE EXCLUA_PALAVRA(:CODIGO);
   -- Exclui os relacionamentos atuais.
  EXECUTE STATEMENT ('DELETE FROM ' || :TABELADETALHE || ' WHERE ' || 
:CONDICOESPESQUISA);
   -- Remove os espacos em branco excedentes do nome informado.
   NOME = TRIM(NOME);
   -- Laco de processamento do nome.
   WHILE (NOME > '') DO
   BEGIN
     -- Obtem a posicao do espaco em branco dentro do nome.
     POSICAO = POSITION(' ' IN NOME);
     -- Nao consta?
     IF (CODIGO = 0) THEN
     BEGIN
       -- Trata-se da ultima palavra do nome.
       PALAVRA = TRIM(NOME);
       -- Sem mais palavras a processar.
       NOME = '';
     END -- IF
     ELSE
     BEGIN
       -- Obtem a proxima palavra do nome.
       PALAVRA = SUBSTRING(NOME FROM 1 FOR (POSICAO - 1));
       -- Remove essa palavra do nome.
       NOME = TRIM(SUBSTRING(NOME FROM POSICAO FOR 200));
     END -- ELSE
     -- Insere ou reutiliza a palavra compartilhada e obtem o seu 
respectivo codigo.
     SELECT CODIGO FROM INCLUA_PALAVRA(:PALAVRA) INTO :CODIGO;
     -- Insere o registro da nova palavra relacionada a essa pessoa fisica.
     EXECUTE STATEMENT ('INSERT INTO ' || :TABELADETALHE || '(' || 
:CAMPOSCHAVES || ',CODIGO)VALUES(' || :VALORESCHAVES || ', ' || :CODIGO 
|| ')');
   END -- WHILE
END^

COMMENT ON PROCEDURE RELACIONA_PALAVRAS IS 'Relaciona as palavras do 
nome com um registro';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.NOME IS 'Nome a ser processado';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.TABELADETALHE IS 'Nome da tabela 
detalhe onde ficara o relacionamento';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.CAMPOSCHAVESINCLUSAO IS 'Nomes 
dos campos chaves, separados por virgula, para inclusao';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.VALORESCHAVESINCLUSAO IS 
'Valores dos campos chaves, separados por virgula, para inclusao';
COMMENT ON PARAMETER RELACIONA_PALAVRAS.CONDICOESPESQUISA IS 'Valores 
dos campos chaves, separados por virgula, para consulta e exclusao';

CREATE TRIGGER TG_PESSOAFISICA_AIU FOR PESSOAFISICA AFTER INSERT OR 
UPDATE POSITION 100 AS
BEGIN
   -- Processa o nome dessa nova pessoa fisica e cria a relacao de 
palavras com esse novo registro/linha.
   EXECUTE PROCEDURE RELACIONA_PALAVRAS(
     -- NOME
     NEW.NOME,
     -- TABELADETALHE
     'PESSOAFISICAPALAVRA',
     -- CAMPOSCHAVESINCLUSAO
     'IDENTIFICADOR',
     -- VALORESCHAVESINCLUSAO
     NEW.IDENTIFICADOR,
     -- CONDICOESPESQUISA
     'IDENTIFCADOR = ' || NEW.IDENTIFICADOR);
END^

SET TERM ; ^

Agora, quando você incluir uma nova pessoa física, ou alterar o seu 
nome, os relacionamentos serão criados e atualizados automaticamente.

Vamos supor que estamos cadastrando uma pessoa física com identificador 
100 e nome "Fulano da Silva".

Separando o nome "Fulano da Silva" em palavras, temos "Fulano", "da" e 
"Silva".

Seriam incluídas 3 palavras na tabela de palavras compartilhadas 
("Fulano", "da" e "Silva") e seriam incluídos 3 registros/linhas na 
tabela PESSOAFISICAPALAVRA.

Tudo ficaria dessa forma:

Tabela PALAVRA:

CODIGO = 1, TEXTO = 'Fulano', REFERENCIAS = 1
CODIGO = 2, TEXTO = 'da', REFERENCIAS = 1
CODIGO = 3, TEXTO = 'Silva', REFERENCIAS = 1

Tabela PESSOAFISICAPALAVRA:

IDENTIFICADOR = 100, CODIGO_PALAVRA = 1
IDENTIFICADOR = 100, CODIGO_PALAVRA = 2
IDENTIFICADOR = 100, CODIGO_PALAVRA = 3

Nesse cenário, podemos fazer uma consulta de todas as pessoas físicas 
que tem a palavra "Fulano" (caso insensitivo pela colação PT_BR) da 
seguinte forma:

SELECT
   PF.IDENTIFICADOR,
   PF.NOME
FROM
   PALAVRA P
   PESSOAFISICAPALAVRA PFP,
   PESSOAFISICA PF
WHERE
   (P.TEXTO = 'Fulano')AND
   (PFP.CODIGO_PALAVRA = P.CODIGO)AND
   (PF.IDENTIFICADOR = PFP.IDENTIFICADOR);

Usando o operador EXISTS, você poderia refazer as condições de sua 
pesquisa, substituindo o CONTAINING por subselects dentro de EXISTS.

Dessa forma, você poderia realmente usar índices, e não mas ser obrigado 
a processar nome a nome.

É só uma sugestão.

Espero ter ajudado mais que atrapalhado. :D

Em 15-08-2013 21:30, Carlos Phelippe escreveu:
> Olá pessoal,
> preciso de ajuda para otimizar a consulta abaixo:
>
> BEGIN
> FOR SELECT C.IDENTIFICADOR, C.DATA, C.HORA, C.SONORO, C.AVISADO, C.OPCAO, C.TEXTO, C.CONTATO, C.LOGIN, C.PUBLICO, C.QUEM, C.SINCRONIZAR, C.DATAGRAVACAO, C.TIPO, TC.COR
>
> FROM COMPROMISSO C LEFT JOIN PESS0A_COMPROMISSO PC ON PC.ID_COMPROMISSO = C.IDENTIFICADOR
> LEFT JOIN TIPOCOMPROMISSO TC ON TC.NOME = C.TIPO
> LEFT JOIN PESSOAFISICA PF ON PF.IDENTIFICADOR = PC.ID_PESSOA AND PC.TIPO_PESSOA = :TIPO_PESSOA_PF
> LEFT JOIN PESSOAJURIDICA PJ ON PJ.IDENTIFICADOR = PC.ID_PESSOA AND PC.TIPO_PESSOA = :TIPO_PESSOA_PJ
> LEFT JOIN CONTATO_PJ C_PJ ON C_PJ.IDENTIFICADOR = PC.ID_PESSOA AND PC.TIPO_PESSOA = :TIPO_PESSOA_C_PJ
>
> WHERE C.DATA BETWEEN :DATA_01 AND :DATA_02 AND COALESCE(C.TEXTO,'') CONTAINING :TEXTO_ AND COALESCE(PJ.NOME,'')||COALESCE(PF.NOME,'')||COALESCE(C_PJ.NOME,'') CONTAINING :NOME AND C.OPCAO IN (:OPCAO_01, :OPCAO_02, :OPCAO_03) INTO :IDENTIFICADOR, :DATA, :HORA, :SONORO, :AVISADO, :OPCAO, :TEXTO, :CONTATO, :LOGIN, :PUBLICO, :QUEM, :SINCRONIZAR, :DATAGRAVACAO, :TIPO, :COR DO SUSPEND;
>
> END
>
> Como poderia otimizar a consulta acima?
>
> [http://www.digilab.com.br/wp-content/themes/Digilab/images/logo_digilab.jpg]   Carlos Phelippe
> Analista de Sistemas
> +55 48 3234 4041
> digilab.com.br<http://www.digilab.com.br>
>
> ______________________________________________
> FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
> Para saber como gerenciar/excluir seu cadastro na lista, use: http://www.firebase.com.br/fb/artigo.php?id=1107
> Para consultar mensagens antigas: http://firebase.com.br/pesquisa