Array
(
)

sql demorada

Livia
   - 10 set 2004

Olá a todos!
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


Motta
   - 10 set 2004

Pelo select creio que o bd seja oracle ou sqlserver, mas subselect como coluna realmente é pesado.

qts linhas tem a tabela?
o bd tem estatisticas recentes?
tem indices ?
vc fez o plano de excecucao (explain plain) da query ?


Livia
   - 10 set 2004

Estou usando Interbase... A tabela de lancamento tem em torno de 50 linhas, isso pq é um banco teste, pois se pegar uma tabela de um cliente, deve ter em torno 3000 linhas (isso de 1 mês), consequentemente a demora seria bem maior...Teria uma outra maneira?


Vinicius2k
   - 10 set 2004

Livia,

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+


Livia
   - 10 set 2004

Vinicius,
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


Vinicius2k
   - 10 set 2004

Livia,

Crie os índices conforme este script :
#Código

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+


Vinicius2k
   - 10 set 2004

Esquecí-me de mencionar... Este é realmente seu Script ? Neste trecho :

Citação:
...ALTER TABLE ´LANCAMENTO´ ADD ALTER TABLE ´LANCAMENTO´ ADD CONSTRAINT ´FK_LANCAMENTO_CONTA_CREDITO´ FOREIGN KEY (´COD_EMP´, ´CONTA_CREDITO´) REFERENCES PLANO (´COD_EMP´, ´CODIGO´);...

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 :
#Código

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+


Vinicius2k
   - 10 set 2004

Esquecí-me de mencionar... Este é realmente seu Script ? Neste trecho :

Citação:
...ALTER TABLE ´LANCAMENTO´ ADD ALTER TABLE ´LANCAMENTO´ ADD CONSTRAINT ´FK_LANCAMENTO_CONTA_CREDITO´ FOREIGN KEY (´COD_EMP´, ´CONTA_CREDITO´) REFERENCES PLANO (´COD_EMP´, ´CODIGO´);...

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 :
#Código

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"));

Motta
   - 10 set 2004

Outra solução pode ser criar uma tabela agregadora alimentada por trigger ou por um processo que rode automaticamente (não sei se o IB tem isto, o Job do Oracle)


Livia
   - 13 set 2004

Vinicius,
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


Vinicius2k
   - 13 set 2004


Citação:
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???

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+


Livia
   - 14 set 2004

Achei q fosse um loop q estaria o problema mas eu executei a sql no IBExpert e demorou +-5 s p/ fazer a consulta, e ao debugar percebi q quando eu faço uma query receber essa sql e dou um open demora em torno de 12 segundos.


Livia


Vinicius2k
   - 14 set 2004

Livia,

Vamos tentar melhorar mais um pouco :
1. Altere os índices :
#Código

/* 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...
#Código
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...


Citação:
e ao debugar percebi q quando eu faço uma query receber essa sql e dou um open demora em torno de 12 segundos.

Qual a camada de acesso que vc está utilizando?

T+
Vinicius


Motta
   - 14 set 2004

Livia, o que sugerir foi manter uma tabela totalizadora que fosse um resumo dos dados que vc quer ober por sum, ela pode ser alimentada por trigger, teria que ter uma analise mais profunda para ver se vale a pena ou se uma azeitada na query (indices etc) resolve o problema, em geral se usa totalizadora para muitos dados onde mesmo com indices a query fica lenta, no seu caso acho que uma arrumada nas tabelas e query resolve, ajuda muito conhecer o banco (IB no caso) que eu não domino.


Livia
   - 15 set 2004

A sql q o Vinicius me passou funcionou legal na minha maquina...conseguiu executar em 5 s, porém ao executar em outra máquina demorou em torno de 15 s.
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


Vinicius2k
   - 15 set 2004

Livia,

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 cliente 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+