Tentando localizar o erro

[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;  

[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;  

[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.

[JoséQuintas]

Alguma idéia sobre qual pode ser o problema?

[JoséQuintas]

Cada parte isolada leva 12 segundos.

[JoséQuintas]

E as duas juntas, até cancelo porque demora muito.

[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.

[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.

[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....

[asimoes]

Corrigindo...

select alias.col1, alias.col2 from
(
select....

)

[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....

[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  
             )  

[bencz]

o EXISTS e NOT EXISTS também é uma boa alternativa...
Uma alternativa para fazer o "IN" mais rápido é:

Código: Selecionar todos

Where <campo> NOT IN ( SELECT '603', '903', .. )

[JoséQuintas]

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.

[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....