Página 1 de 2
Tentando localizar o erro
Enviado: 20 Mar 2020 11:50
por JoséQuintas
À primeira vista tudo certo, mas não vai.
Código: Selecionar todos
Erro executando comando:-2147217900 [ma-3.1.6][5.7.12-log]Unknown column 'ULTIMOPRECO.ESVALOR' in 'field list'
SELECT LANCAMENTOS.*, ULTIMOPRECO.ESVALOR
FROM
( SELECT ESDATLAN, ESTIPLAN, ESQTDE, ESVALOR, ESCFOP, JPTRANSA.TRREACAO AS REACAO,
JPITEM.IEQTDCOM AS QTDCOM, JPESTOQUE.ESPRODUTO AS PRODUTO, JPITEM.IEPRODEP AS DEPTO,
JPESTOQUE.ESPRODUTO, ULTIMOPRECO.ESVALOR AS ULTIMOPRECO
FROM JPESTOQUE
LEFT JOIN JPTRANSA ON JPESTOQUE.ESTRANSA=JPTRANSA.IDTRANSA
LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = JPESTOQUE.ESPRODUTO
WHERE NOT SUBSTR( ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
) AS LANCAMENTOS
LEFT JOIN
( SELECT ESPRODUTO, ESVALOR FROM JPESTOQUE
WHERE IDESTOQUE IN (
SELECT ID
FROM ( SELECT MAX( IDESTOQUE ), ESPRODUTO FROM JPESTOQUE
LEFT JOIN JPTRANSA ON JPESTOQUE.ESTRANSA=JPTRANSA.IDTRANSA
LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = JPESTOQUE.ESPRODUTO
WHERE NOT SUBSTR( ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
AND ESTIPLAN = '2' AND NOT TRREACAO LIKE '%DEV%'
GROUP BY ESPRODUTO
) AS IDLIST
)
) AS ULTIMOPRECO
ON LANCAMENTOS.ESPRODUTO = ULTIMOPRECO.ESPRODUTO
ORDER BY ESPRODUTO, ESDATLAN, ESTIPLAN;
Tentando localizar o erro
Enviado: 20 Mar 2020 12:09
por JoséQuintas
ALIAS não resolveu
Código: Selecionar todos
Erro executando comando:-2147217900 [ma-3.1.6][5.7.12-log]Unknown column 'ULTIMOPRECO.ESVALOR' in 'field list'
SELECT LANCAMENTOS.*, ULTIMOPRECO.ESVALOR
FROM ( SELECT ESDATLAN, ESTIPLAN, ESQTDE, ESVALOR, ESCFOP, JPTRANSA.TRREACAO AS REACAO,
JPITEM.IEQTDCOM AS QTDCOM, A.ESPRODUTO AS PRODUTO, JPITEM.IEPRODEP AS DEPTO,
A.ESPRODUTO, ULTIMOPRECO.ESVALOR AS ULTIMOVALOR
FROM JPESTOQUE AS A
LEFT JOIN JPTRANSA ON JPTRANSA.IDTRANSA = A.ESTRANSA
LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = A.ESPRODUTO
WHERE NOT SUBSTR( ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
) AS LANCAMENTOS
LEFT JOIN ( SELECT ESPRODUTO, ESVALOR
FROM JPESTOQUE AS B
WHERE IDESTOQUE IN ( SELECT ID
FROM ( SELECT MAX( IDESTOQUE ) AS ID, ESPRODUTO
FROM JPESTOQUE AS C
LEFT JOIN JPTRANSA AS T ON T.IDTRANSA = C.ESTRANSA
LEFT JOIN JPITEM AS I ON I.IDPRODUTO = C.ESPRODUTO
WHERE NOT SUBSTR( C.ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
AND C.ESTIPLAN = '2' AND NOT T.TRREACAO LIKE '%DEV%'
GROUP BY ESPRODUTO
) AS IDLIST
)
) AS ULTIMOPRECO ON LANCAMENTOS.ESPRODUTO = ULTIMOPRECO.ESPRODUTO
ORDER BY ESPRODUTO, ESDATLAN, ESTIPLAN;
Tentando localizar o erro
Enviado: 20 Mar 2020 12:38
por JoséQuintas
Simplifiquei, mas ficou demorada, cancelo antes de terminar.
Código: Selecionar todos
SELECT ESDATLAN, JPESTOQUE.ESPRODUTO, ESTIPLAN, ESQTDE, ESCFOP, JPTRANSA.TRREACAO AS REACAO,
JPITEM.IEQTDCOM AS QTDCOM, JPITEM.IEPRODEP AS DEPTO, ULTIMOPRECO.ESVALOR
FROM JPESTOQUE
LEFT JOIN JPTRANSA ON JPTRANSA.IDTRANSA = JPESTOQUE.ESTRANSA
LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = JPESTOQUE.ESPRODUTO
LEFT JOIN ( SELECT B.ESPRODUTO, B.ESVALOR
FROM JPESTOQUE AS B
WHERE B.IDESTOQUE IN ( SELECT ID
FROM ( SELECT MAX( C.IDESTOQUE ) AS ID, C.ESPRODUTO
FROM JPESTOQUE AS C
LEFT JOIN JPTRANSA AS T ON T.IDTRANSA = C.ESTRANSA
WHERE NOT SUBSTR( C.ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
AND C.ESTIPLAN = '2' AND NOT T.TRREACAO LIKE '%DEV%'
GROUP BY C.ESPRODUTO
) AS IDLIST
)
) AS ULTIMOPRECO
ON JPESTOQUE.ESPRODUTO = ULTIMOPRECO.ESPRODUTO
WHERE NOT SUBSTR( JPESTOQUE.ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
ORDER BY ESPRODUTO, ESDATLAN, ESTIPLAN;
Basicamente é:
pego os lançamentos de estoque (JPESTOQUE) com os últimos preços de compra (ULTIMOPRECO)
Para o último preço, pego a lista com os maiores IDs de cada produto (IDLIST), e os respectivos lançamentos (ULTIMOPRECO)
Cada um individual é rápido, relacionar códigos entre os dois deveria ser rápido mas não está sendo.
Tentando localizar o erro
Enviado: 20 Mar 2020 12:43
por JoséQuintas
Alguma idéia sobre qual pode ser o problema?
Tentando localizar o erro
Enviado: 20 Mar 2020 12:55
por JoséQuintas
Cada parte isolada leva 12 segundos.
Tentando localizar o erro
Enviado: 20 Mar 2020 12:58
por JoséQuintas
E as duas juntas, até cancelo porque demora muito.
Tentando localizar o erro
Enviado: 20 Mar 2020 13:11
por JoséQuintas
Antes que comentem do left join.... já que o inner join é o mais comum:
There are different types of joins available in SQL: INNER JOIN: returns rows when there is a match in both tables. LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
Como as informações vieram de DBF, e não uso relacionamento pré-existente, o que entendo é:
INNER JOIN - só vai mostrar lançamento de estoque com produto cadastrado
LEFT JOIN - vai mostrar lançamento mesmo que não exista produto cadastrado
RIGHT JOIN - vai mostrar até produtos sem lançamento (que não se aplica neste caso)
É por isso que tenho preferido usar o LEFT JOIN, pra virem todos os lançamentos, mesmo se nào existir cadastro.
Então... LEFT JOIN NÃO é o problema.
Tentando localizar o erro
Enviado: 20 Mar 2020 14:12
por asimoes
Faz um select acima englobando o select e chama as colunas que você quer.
select alias.col1, alias.col2
(
select....
)
Essa coluna que você quer não é vista no select principal porque ela é da subquery, mas se você fizer um select por cima de tudo deve funcionar.
Tentando localizar o erro
Enviado: 20 Mar 2020 14:17
por bencz
José, boa tarde!
Primeiramente, veja isso:
https://pctoledo.org/forum/viewto ... 15#p139372
Depois:
[*]Crie o relacionamento entre as tabelas
[*]Crie os indices
[*]Utilize um profiler para mostrar o que está consumindo mais tempo... isso varia de banco para banco...
Você já sabe que uma query em especial consome 12segudos+ para ser executada..., ao analisar a query, que é bastante simples por sinal, vejo o seguinte problema...
Falta de índice...
ALguns ajustes na query:
Código: Selecionar todos
SELECT
<APELIDO TABELA>.ESDATLAN,
<APELIDO TABELA>.ESPORDUTO,
<APELIDO TABELA>.ESTIPLAN,
<APELIDO TABELA>.ESQTDE,
<APELIDO TABELA>.ESCFPOP,
JPTRANSA.TRREACAO AS REACAO,
JPITEM.IEQTDCOM AS QTDCOM,
JPITEM.IEPRODEP AS DEPTO
FROM JPESTOQUE <INSIRA AQUI APELIDO JPESTOQUE>
LEFT JOIN JPTRANSA <INSIRA AQUI UM APELIDO PARA JPTRANSA> ON(<APELIDO JPTRANSA>.IDTRANSA = <APELIDO JPESTOQUE>.ESTRANSA
LEFT JOIN JPITEM <INSIRA AQUI UM APELIDO PARA JPITEM> ON(<APELIDO JPITEM>.IDPRODUTO = <APELIDO JPESTOQUE>.ESPRODUTO
WHERE LEFT(<APELIDO JPESTOQUE>.ESCFOP, 3) NOT IN( .... )
ORDER BY <APELIDO TABELA>.ESPRODUTO, <APELIDO TABELA>.ESDATLAN, <APELIDO TABELA>.ESTIPLAN;
Verifique se os campos "<APELIDO JPTRANSA>.IDTRANSA" e "<APELIDO JPESTOQUE>.ESTRANSA" são do mesmo tipo e verifique a mesma coisa para o outro join
Eu criaria os seguintes indices para teste:
Código: Selecionar todos
CREATE INDEX JPESTOQUE_IDX ON JPESTOQUE (ESTRANSA, ESPORDUTO, ESDATLAN, ESTIPLAN, ESQTDE, ESCFPOP, ESCFOP);
CREATE INDEX JPTRANSA_IDX ON JPTRANSA (IDTRANSA, TRREACAO);
CREATE INDEX JPITEM_IDX ON JPITEM (IDPRODUTO, IEPRODEP);
Obs: da proxima vez colque a Query aqui, ao invés de postar foto da tela do gerenciador do banco de dados....
Tentando localizar o erro
Enviado: 20 Mar 2020 14:19
por asimoes
Corrigindo...
select alias.col1, alias.col2 from
(
select....
)
Tentando localizar o erro
Enviado: 20 Mar 2020 14:26
por bencz
Por experiencia própria... uma vez estava montando um sistema e tinha que fazer um select bem simples... erá entre uma tabela com 10 registros e outra com 1000 registros..., eu tinha que trazer varias informações nesse select... e as vezes, a query demorava até 5 minutos para ser executada... bastou criar os índices e pronto... retorno instantâneo...
Não adianta nada estar utilizando o banco de dados mais porreta do mundo, nos melhores servidores do mundo, se a estrutura do banco de dadas está mal projetada e construída e querys não otimizadas com a correta criação dos índices....
Bom, um é consequência do outro... se o banco tá mal projetado, consequentemente, as querys também estarão... falo isso por experiencia dos mais bizarros projetos nos quais já trabalhei e migrei....
>-------
Um outro detalhe importante, as tabelas TEM QUE TER CHAVE PRIMARIA!!!!!!!!!!!, tabela sem chave primaria, dificulta a busca para os SGDB deixando o processo mais lento....
Tentando localizar o erro
Enviado: 20 Mar 2020 14:29
por asimoes
Eu tentaria o uso do
where exists (select alias.col from alias where alias.col = principal.col )
Código: Selecionar todos
WHERE
APC.D_DESLIG IS NULL AND
APC.D_FALECI IS NULL AND
EXISTS
( SELECT
ADM.MATRICULA
FROM
ADMSAUDE ADM
WHERE
ADM.MATRICULA = APC.CODIGO
ORDER BY
ADM.MATRICULA
LIMIT 1
)
Tentando localizar o erro
Enviado: 20 Mar 2020 14:33
por bencz
o EXISTS e NOT EXISTS também é uma boa alternativa...
Uma alternativa para fazer o "IN" mais rápido é:
Tentando localizar o erro
Enviado: 20 Mar 2020 15:20
por JoséQuintas
bencz escreveu:Obs: da proxima vez colque a Query aqui, ao invés de postar foto da tela do gerenciador do banco de dados....
Na verdade a primeira mensagem é a query, é que já tentei tantas modificações, que chega uma hora que deixa de ser o teste original.
O tempo de 12 segundos, acho que não é da query, porque no log mostra milésimos de segundo.
O detalhe é: o relacionamento é feito em memória, e não diretamente com a tabela do MySQL, então nesse left join final nem entra índice.
No outro ajustado pode ser.
TODAS as minhas tabelas tem chave primária.
E o estoque com certeza tem índice por produto.
Basicamente as subqueries são rápidas, mas quando junta as duas, uma relacionada com a outra, é onde vém o problema.
A segunda query retorna apenas um registro por produto, com código de produto e valor.
a primeira são os lançamentos.
Tentando localizar o erro
Enviado: 20 Mar 2020 17:55
por bencz
A diferença de criar um simples indice...
Eu instalei o MariaDB no meu desktop e rodei um programa que fiz que migra o bando de dados, do esse meu programa migra entra MS-SQL server, PostgreSQL, MYSQL, MariaDB, DB2/400 e DB/Z... só escolher a origem e o destino... o programa se vira em portar a estrutura, índices e o conteúdo das tabelas...
Rodei o profiler e vi a falta de um índice, em uma tabela com 4 registros, que é a tabela "FYSCO_SituacaoProcuracoes"... ao criar o índice o tempo de execução da query caiu em 4ms....