sql demorada
10/09/2004
0
Gostaria de saber se tem alguma coisa errada no sql abaixo, pois quando executo, demora em torno de 20 segundos p/ mostrar o resultado, e acho q é isso q está fazendo com q o relatório demore p/ ser gerado
select g.cod_emp,p.codigo,p.conta,p.nome, p.grau,p.SALDO_INICIALIZACAO,
(select sum(v.valor) from lancamento v where v.situacao=´1´and (v.mes_ano_base between ´2004-08-01´ and ´2004-08-31´)
and v.conta_credito=p.codigo
) as credito,
(select sum(v.valor) from lancamento v where v.situacao=´1´ and (v.mes_ano_base between ´2004-08-01´ and ´2004-08-31´)
and v.conta_debito=p.codigo ) as debito
from lancamento g, plano p
where g.cod_emp=1 and (p.conta between ´1. . . . ´ and ´2. . . . ´)
group by g.cod_emp,p.codigo,p.conta,p.nome, p.grau,p.SALDO_INICIALIZACAO
order by p.conta
Obrigada
Livia
Livia
Posts
10/09/2004
Motta
qts linhas tem a tabela?
o bd tem estatisticas recentes?
tem indices ?
vc fez o plano de excecucao (explain plain) da query ?
10/09/2004
Livia
10/09/2004
Vinicius2k
Vc vai precisar planejar alguns índices para melhorar a performance desta consulta...
Eu começaria com :
Lancamento:
1. COD_EMP + SITUACAO + MES_ANO_BASE + CONTA_CREDITO
2. COD_EMP + SITUACAO + MES_ANO_BASE + CONTA_DEBITO
Plano:
1. CONTA
Então vc deve executar esta instrução diretamente num front-end (IBConsole ou IBExpert, por exemplo) e analisar se no PLAN adotado pelo servidor seus índices foram usados.
A partir daí os pontos são os seguintes :
1. Algum dos índices foi usado ?
Se sim, bom. Se não, os índices não estão adequados e vc precisará re-projetá-los. Basicamente, o projeto consiste em já criar no índice uma ´imagem´ na ordem que seria a ideal para o servidor utilizar.
2. Todos os índices foram usados?
Se sim, ótimo. Se não, vc poderá remover os que não foram usados (logicamente, só estou considerando esta consulta).
3. Observe no PLAN se alguma vez foi utilizada a opção NATURAL.
Se sim isto é ruim. Vc deve procurar criar um índice para que o NATURAL deixe de ser usado. Se não, está pronto. Isto é o máximo que vc poderia fazer para melhorar a performance em relação à indices...
Se precisar de mais ajuda, vc poderia postar as instruções de ´create table´ das duas tabelas (lancamento e plano) para que possamos simular a execução.
Espero ter ajudado...
T+
10/09/2004
Livia
Segue abaixo o script das tabelas Lancamento e Plano. Executei a sql e realmente a opção NATURAL é utilizada...
CREATE TABLE ´LANCAMENTO´
(
´LOTE´´CHAVE´ NOT NULL,
´LINHA´´CHAVE´ NOT NULL,
´COD_EMP´SMALLINT NOT NULL,
´MES_ANO_BASE´TIMESTAMP NOT NULL,
´TIPO´CHAR(1),
´CONTA_CREDITO´INTEGER,
´CONTA_DEBITO´INTEGER,
´DATALANC´TIMESTAMP,
´SITUACAO´CHAR(1),
´CODIGO_HISTORICO´INTEGER,
´NUM_DOC´DOUBLE PRECISION,
´DATA_DOC´TIMESTAMP,
´COMPLEMENTO1´VARCHAR(400),
´DESCRICAO´VARCHAR(30),
´VALOR´DOUBLE PRECISION DEFAULT 0 NOT NULL,
´SLD_ANT_CONTA_CREDITO´DOUBLE PRECISION,
´SLD_ANT_CONTA_DEBITO´DOUBLE PRECISION,
´DIVERSOS´VARCHAR(50),
CONSTRAINT ´PK_LANCAMENTO´ PRIMARY KEY (´LOTE´, ´LINHA´, ´COD_EMP´, ´MES_ANO_BASE´)
);
ALTER TABLE ´LANCAMENTO´ ADD ALTER TABLE ´LANCAMENTO´ ADD CONSTRAINT ´FK_LANCAMENTO_CONTA_CREDITO´ FOREIGN KEY (´COD_EMP´, ´CONTA_CREDITO´) REFERENCES PLANO (´COD_EMP´, ´CODIGO´);
ALTER TABLE ´LANCAMENTO´ ADD CONSTRAINT ´FK_LANCAMENTO_CONTA_DEBITO´ FOREIGN KEY (´COD_EMP´, ´CONTA_DEBITO´) REFERENCES PLANO (´COD_EMP´, ´CODIGO´);
ALTER TABLE ´LANCAMENTO´ ADD CONSTRAINT ´FK_LANCAMENTO_HISTORICO´ FOREIGN KEY (´CODIGO_HISTORICO´) REFERENCES HISTORICO (´CODIGO´);
CREATE TABLE ´PLANO´
(
´CODIGO´INTEGER NOT NULL,
´CONTA´VARCHAR(40) NOT NULL,
´NOME´VARCHAR(40) NOT NULL,
´GRAU´INTEGER,
´TIPOCON´VARCHAR(1),
´TIPOSLD´VARCHAR(1),
´ORDIMP´INTEGER,
´COD_EMP´SMALLINT NOT NULL,
´SALDO_INICIALIZACAO´DOUBLE PRECISION,
´DATA_INICIALIZACAO´TIMESTAMP,
´CONTAPAI´VARCHAR(40),
CONSTRAINT ´PK_PLANO´ PRIMARY KEY (´COD_EMP´, ´CODIGO´),
CONSTRAINT ´UNQ_PLANO´ UNIQUE (´COD_EMP´, ´CONTA´)
);
ALTER TABLE ´PLANO´ ADD CONSTRAINT ´FK_PLANOCONTAPAI´ FOREIGN KEY (´COD_EMP´, ´CONTAPAI´) REFERENCES PLANO (´COD_EMP´, ´CONTA´) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ´PLANO´ ADD CONSTRAINT ´FK_PLANO_COD_EMP´ FOREIGN KEY (´COD_EMP´) REFERENCES TABEMP (´COD_EMP´) ON UPDATE CASCADE ON DELETE CASCADE;
Obrigada
Livia
10/09/2004
Vinicius2k
Crie os índices conforme este script :
CREATE INDEX IX_LANCAMENTO_A ON LANCAMENTO (COD_EMP); CREATE INDEX IX_LANCAMENTO_B ON LANCAMENTO (SITUACAO, MES_ANO_BASE); CREATE INDEX IX_PLANO_A ON PLANO (CONTA);
Na minha análise de PLAN o NATURAL não mais está sendo usado e sim os índices criados...
Creio que é o máximo que se pode fazer em relação aos índices dentro do contexto da sua consulta, se não resolver totalmente o problema, deve ajudar bastante...
Faça o teste e retorne. :wink:
Espero ter ajudado.
T+
10/09/2004
Vinicius2k
Outra coisa, como vc não passou (eu tbm não pedi) o script para as tabelas HISTORICO E TABEMP eu criei desta forma, apenas para que pudessem ser feitos os relacionamentos :
CREATE TABLE "TABEMP"( "COD_EMP" INTEGER NOT NULL, "NOME_EMP" VARCHAR(50), CONSTRAINT "PK_TABEMP" PRIMARY KEY ("COD_EMP")); CREATE TABLE "HISTORICO"( "CODIGO" INTEGER NOT NULL, "DESCRICAO" VARCHAR(50), CONSTRAINT "PK_HISTORICO" PRIMARY KEY ("CODIGO"));
Mas creio não ter influencia sobre a execução da consulta... já o erro que indiquei acima pode estar lhe atrapalhando... já que se esta contraint não existir, consequentemente o índice dela também não...
T+
10/09/2004
Vinicius2k
Outra coisa, como vc não passou (eu tbm não pedi) o script para as tabelas HISTORICO E TABEMP eu criei desta forma, apenas para que pudessem ser feitos os relacionamentos :
CREATE TABLE "TABEMP"( "COD_EMP" INTEGER NOT NULL, "NOME_EMP" VARCHAR(50), CONSTRAINT "PK_TABEMP" PRIMARY KEY ("COD_EMP")); CREATE TABLE "HISTORICO"( "CODIGO" INTEGER NOT NULL, "DESCRICAO" VARCHAR(50), CONSTRAINT "PK_HISTORICO" PRIMARY KEY ("CODIGO"));
10/09/2004
Motta
13/09/2004
Livia
aquela parte do script realmente não existe. Eu criei os indices q vc me passou e a performance melhorou, mas acho q o problema maior é alguma função, pois analiso as contas e subcontas através de um loop, os débitos e créditos, será q pode ser isso???
Motta,
essa tabela armazena os dados toda vez q gero uma consulta sql??
Obrigada
Livia
13/09/2004
Vinicius2k
Bem, é provável q sim... se neste loop vc executa esta query várias vezes, vc tem um ´gargalo´ de processamento q vc precisa tentar evitar...
Se quiser, dê-nos uma idéia de como funciona seu laço que talvez seja possível convertê-lo para ser executado 100¬ no servidor com uma SQL diferente da atual, talvez usando Joins...
T+
14/09/2004
Livia
Livia
14/09/2004
Vinicius2k
Vamos tentar melhorar mais um pouco :
1. Altere os índices :
/* Dropando indices */ DROP INDEX IX_LANCAMENTO_A; DROP INDEX IX_LANCAMENTO_B; DROP INDEX IX_LANCAMENTO_C; DROP INDEX IX_PLANO_A; /* Criando novos indices */ CREATE INDEX IX_LANCAMENTO_A ON LANCAMENTO (COD_EMP); CREATE INDEX IX_LANCAMENTO_B ON LANCAMENTO (CONTA_CREDITO, MES_ANO_BASE, SITUACAO); CREATE INDEX IX_LANCAMENTO_C ON LANCAMENTO (CONTA_DEBITO, MES_ANO_BASE, SITUACAO); CREATE INDEX IX_PLANO_A ON PLANO (CONTA);
2. Altere sua query. Sempre que possível, deve-se utilizar a seguinte ordem de seleção em Joins : Smallint -> Integer -> Date -> String... Fiz pequenas mudanças na ordem do where dos sub-selects...
select g.cod_emp, p.codigo, p.conta, p.nome, p.grau, p.saldo_inicializacao, (select sum (v.valor) from lancamento v where (v.conta_credito = p.codigo) and (v.mes_ano_base between ´2004-08-01´ and ´2004-08-31´) and (v.situacao = ´1´)) as credito, (select sum (v.valor) from lancamento v where (v.conta_debito = p.codigo) and (v.mes_ano_base between ´2004-08-01´ and ´2004-08-31´) and (v.situacao = ´1´)) as debito from lancamento g, plano p where g.cod_emp=1 and (p.conta between ´1. . . . ´ and ´2. . . . ´) group by g.cod_emp, p.codigo, p.conta, p.nome, p.grau, p.saldo_inicializacao order by p.conta
Isto deve melhorar mais um pouco a performace... teste-a no IBExpert...
Qual a camada de acesso que vc está utilizando?
T+
Vinicius
14/09/2004
Motta
15/09/2004
Livia
A minha máquina eh um XP 2.2 e a outra máq. é um Pentium II.
O q o Motta me sugeriu já tinha sido sugerido aki na empresa, porém pensei q poderia ser algum problema na minha sql. O q farei agora é criar uma outra tabela q armazenará os totalizadores das contas p/ ver se poderia ser isso a causadora da demora da consulta.
Obrigada
Livia
15/09/2004
Vinicius2k
Fica a seu critério, então, qual solução adotar... a vantagem é que conseguimos baixar um processo com tempo inicial de 20s para 5s...
Sobre a máquina q vc executou, é totalmente justificável, pois o processamento é feito todo por ela, mas lembre-se que é um processamento de servidor... Seus clientes terão um PII como servidor?
A velocidade de processamento e memória de uma estação não farão nenhuma diferença na performance dessa query... tudo vai depender do servidor. Porque vc não faz o teste da sua aplicação no seu PII, mas acessando como [b:b84986d534]cliente[/b:b84986d534] o seu XP 2.2... vc notará uma pequena diferença devido ao transito na rede, mas quem estará executando a instrução é o XP 2.2
T+
Clique aqui para fazer login e interagir na Comunidade :)