marcioe Posted October 6, 2016 Report Share Posted October 6, 2016 Amigos tenho uma tabela de vendas e que as vezes esta demorando muito para abrir a query que esta sendo usada é essa cQuery:= ' SELECT * FROM ( ' cQuery:= cQuery + ' SELECT cli.CLICOD, cli.CLINOM, cli.CLICPF, ' cQuery:= cQuery + ' vda.CODVEN,vda.DATVEN, vda.NUMVEN, vda.CLIVEN, vda.QTDVEN, vda.VALVEN, vda.ESPVEN, vda.TOTVEN, ' cQuery:= cQuery + ' vda.MARCADO, vda.CUPOM, ' cQuery:= cQuery + " IF(vda.CUPOM!='S' AND vda.MARCADO!='S',vda.QTDVEN,0) AS VENDAS_OUTRAS, " cQuery:= cQuery + " IF(vda.CUPOM='S', vda.QTDVEN,0) AS VENDAS_CUPOM, " cQuery:= cQuery + " IF(vda.MARCADO='S',vda.QTDVEN,0) AS VENDAS_NF, " cQuery:= cQuery + ' tv.CODVENDA, tv.ENTRADA_SAIDA ' cQuery:= cQuery + ' FROM vendas AS vda ' cQuery:= cQuery + ' LEFT JOIN clientes AS cli ON cli.CLICOD = vda.CLIVEN ' cQuery:= cQuery + ' LEFT JOIN tvenda AS tv ON vda.NUMVEN = tv.CODVENDA ' cQuery:= cQuery + ' WHERE vda.CODVEN = ' + TRANSFORMA_SQL(T_CODI_PRODUTOS,'N',07,0) cQuery:= cQuery + ' AND vda.CODVEN != ' + TRANSFORMA_SQL(0,'N',07,0) cQuery:= cQuery + ' order by vda.DATVEN DESC ) AS t_venda_itens ' cQuery:= cQuery + ' order by DATVEN DESC ' use sql cQuery alias "vendas_array" new via 'MYSQL' vendas_array->( DbGoTop()) mas demora muito Quote Link to comment Share on other sites More sharing options...
joaosolution Posted October 6, 2016 Report Share Posted October 6, 2016 Marcio Boa tarde Sugiro que no Select * from vendas as vda (select interno) você execute apenas o Where sem o left join. Coloque o left join no select AS t_venda_itens (select de fora, após executar o filtro) Att João Bosco Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 6, 2016 Author Report Share Posted October 6, 2016 Desculpa eu, não entendi Quote Link to comment Share on other sites More sharing options...
joaosolution Posted October 6, 2016 Report Share Posted October 6, 2016 Marcio Seria isso: cQuery:= ' SELECT cli.CLICOD, cli.CLINOM, cli.CLICPF,tv.CODVENDA, tv.ENTRADA_SAIDA,* FROM ( ' cQuery:= cQuery + ' SELECT vda.CODVEN,vda.DATVEN, vda.NUMVEN, vda.CLIVEN, vda.QTDVEN, vda.VALVEN, vda.ESPVEN, vda.TOTVEN, ' cQuery:= cQuery + ' vda.MARCADO, vda.CUPOM, ' cQuery:= cQuery + " IF(vda.CUPOM!='S' AND vda.MARCADO!='S',vda.QTDVEN,0) AS VENDAS_OUTRAS, " cQuery:= cQuery + " IF(vda.CUPOM='S', vda.QTDVEN,0) AS VENDAS_CUPOM, " cQuery:= cQuery + " IF(vda.MARCADO='S',vda.QTDVEN,0) AS VENDAS_NF " cQuery:= cQuery + ' FROM vendas AS vda ' cQuery:= cQuery + ' WHERE vda.CODVEN = ' + TRANSFORMA_SQL(T_CODI_PRODUTOS,'N',07,0) cQuery:= cQuery + ' AND vda.CODVEN != ' + TRANSFORMA_SQL(0,'N',07,0) cQuery:= cQuery + ' order by vda.DATVEN DESC ) AS t_venda_itens ' cQuery:= cQuery + ' LEFT JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN ' cQuery:= cQuery + ' LEFT JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA ' cQuery:= cQuery + ' order by DATVEN DESC ' use sql cQuery alias "vendas_array" new via 'MYSQL' vendas_array->( DbGoTop()) Mas não tenho como estar aqui. Att João Bosco Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 7, 2016 Author Report Share Posted October 7, 2016 Obrigado amigo, por tentar me ajudar, mas continua lento Na verdade ficou até mais lento fiz assim SELECT cli.CLICOD, cli.CLINOM, cli.CLICPF,tv.CODVENDA, tv.ENTRADA_SAIDA, tv.DATVENDA FROM ( SELECT vda.CODVEN,vda.DATVEN, vda.NUMVEN, vda.CLIVEN, vda.QTDVEN, vda.VALVEN, vda.ESPVEN, vda.TOTVEN, vda.MARCADO, vda.CUPOM, IF(vda.CUPOM!='S' AND vda.MARCADO!='S',vda.QTDVEN,0) AS VENDAS_OUTRAS, IF(vda.CUPOM='S', vda.QTDVEN,0) AS VENDAS_CUPOM, IF(vda.MARCADO='S',vda.QTDVEN,0) AS VENDAS_NF FROM vendas AS vda WHERE vda.CODVEN = 1 AND vda.CODVEN != 0 order by vda.DATVEN DESC ) AS t_venda_itens LEFT JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN LEFT JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA order by DATVEN DESC /* Affected rows: 0 Registros encontrados: 51.847 Avisos: 0 Duração de 1 query: 6,879 sec. (+ 9,797 sec. network) */ Quote Link to comment Share on other sites More sharing options...
kleyber Posted October 7, 2016 Report Share Posted October 7, 2016 Marcio, você precisa mesmo usar o ORDER BY? Dependendo da situação, o ORDER BY deixa a consulta extremamente lenta. Outra coisa que pode ser feita é gerar índices baseados no WHERE que estás fazendo. Isso vai agilizar a consulta. Faça o teste: retire o ORDER BY e veja se fica mais rápida a consulta. Outra coisa: estás usando 2 ORDER BY. Aí mesmo é que a consulta fica lenta... Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 7, 2016 Author Report Share Posted October 7, 2016 Sem o order by não resolveu SELECT cli.CLICOD, cli.CLINOM, cli.CLICPF,tv.CODVENDA, tv.ENTRADA_SAIDA, tv.DATVENDA FROM ( SELECT vda.CODVEN,vda.DATVEN, vda.NUMVEN, vda.CLIVEN, vda.QTDVEN, vda.VALVEN, vda.ESPVEN, vda.TOTVEN, vda.MARCADO, vda.CUPOM, IF(vda.CUPOM!='S' AND vda.MARCADO!='S',vda.QTDVEN,0) AS VENDAS_OUTRAS, IF(vda.CUPOM='S', vda.QTDVEN,0) AS VENDAS_CUPOM, IF(vda.MARCADO='S',vda.QTDVEN,0) AS VENDAS_NF FROM vendas AS vda WHERE vda.CODVEN = 1 AND vda.CODVEN != 0 order by vda.DATVEN DESC ) AS t_venda_itens LEFT JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN LEFT JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA /* Affected rows: 0 Registros encontrados: 51.847 Avisos: 0 Duração de 1 query: 7,129 sec. (+ 9,813 sec. network) */ *======================================================================================================== Assim SELECT * FROM vendas WHERE CODVEN = 1 /* Affected rows: 0 Registros encontrados: 51.847 Avisos: 0 Duração de 1 query: 0,171 sec. (+ 12,995 sec. network) */ *=============================================================================================================== o Resultado seria isso dentro do Sistema Quote Link to comment Share on other sites More sharing options...
aferra Posted October 7, 2016 Report Share Posted October 7, 2016 Toque LEFT JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN LEFT JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA por INNER JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN INNER JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA Quote Link to comment Share on other sites More sharing options...
kleyber Posted October 7, 2016 Report Share Posted October 7, 2016 Bom, como falei, dependendo da situação a query fica lenta. Para fins de depuração, monte o SELECT por partes, aí você vai encontrar a razão da lentidão. Tipo: Faça o Select somente com uma tabela, depois vá fazendo os IFs (SELECT interno) e assim por diante. Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 7, 2016 Author Report Share Posted October 7, 2016 Ok, obrigado a todos Quote Link to comment Share on other sites More sharing options...
emotta Posted October 7, 2016 Report Share Posted October 7, 2016 Se você está passando no WHERER que o CODVEN = 1 por qual motivo você passa que ele deve ser != 0 ???? Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 7, 2016 Author Report Share Posted October 7, 2016 mesmo retirando CODVEN != 0 Não faz diferença Quote Link to comment Share on other sites More sharing options...
kleyber Posted October 7, 2016 Report Share Posted October 7, 2016 Outra coisa... você não retirou TODOS os ORDER BY para testar... experimente e veja se muda alguma coisa. Quote Link to comment Share on other sites More sharing options...
Luiz Fernando Posted October 8, 2016 Report Share Posted October 8, 2016 Marcio tem um outro tópico seu aqui, que criei para vc um subselect usando o order by, lembra que ficou rapido, a lógica é a mesma, divide em qdos subselect for preciso, estuda isto com calma que vc faz qq select ficar rapido. Abraços Quote Link to comment Share on other sites More sharing options...
kleyber Posted October 12, 2016 Report Share Posted October 12, 2016 Marcio bom dia, Deu certo? Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 12, 2016 Author Report Share Posted October 12, 2016 nada ainda amigo, mas sou brasileiro e nao desisto... Com a combinação de dicas melhorou, mas nao fiquei ainda satisfeito Quote Link to comment Share on other sites More sharing options...
kleyber Posted October 12, 2016 Report Share Posted October 12, 2016 Como ficou teu select agora? Quote Link to comment Share on other sites More sharing options...
evandro Posted October 12, 2016 Report Share Posted October 12, 2016 Olá, Minha pergunta é: para que um select dentro do outro? E os índices das tabelas clientes e tvenda? Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 13, 2016 Author Report Share Posted October 13, 2016 assim até que roda, mas fica lento ao meu ver, porem acho que tem como já jogar o resultado em ARRAY, pois o que percebi que está demorando quando vou dando add NO ARRAY SELECT cli.CLICOD, cli.CLINOM, cli.CLICPF,tv.CODVENDA, tv.ENTRADA_SAIDA, tv.DATVENDA FROM ( SELECT vda.CODVEN,vda.DATVEN, vda.NUMVEN, vda.CLIVEN, vda.QTDVEN, vda.VALVEN, vda.ESPVEN, vda.TOTVEN, vda.MARCADO, vda.CUPOM, IF(vda.CUPOM!='S' AND vda.MARCADO!='S',vda.QTDVEN,0) AS VENDAS_OUTRAS, IF(vda.CUPOM='S', vda.QTDVEN,0) AS VENDAS_CUPOM, IF(vda.MARCADO='S',vda.QTDVEN,0) AS VENDAS_NF FROM vendas AS vda WHERE vda.CODVEN = 891 AND vda.CODVEN != 0 order by vda.DATVEN DESC ) AS t_venda_itens LEFT JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN LEFT JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA ; /* Affected rows: 0 Registros encontrados: 156.514 Avisos: 0 Duração de 1 query: 8,050 sec. (+ 12,465 sec. network) */ Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 13, 2016 Author Report Share Posted October 13, 2016 O resultado de EXPLAIN SELECT cli.CLICOD, cli.CLINOM, cli.CLICPF, vda.CODVEN,vda.DATVEN, vda.NUMVEN, vda.CLIVEN, vda.QTDVEN, vda.VALVEN, vda.ESPVEN, vda.TOTVEN, vda.MARCADO, vda.CUPOM, IF(vda.CUPOM!='S' AND vda.MARCADO!='S',vda.QTDVEN,0) AS VENDAS_OUTRAS, IF(vda.CUPOM='S', vda.QTDVEN,0) AS VENDAS_CUPOM, IF(vda.MARCADO='S',vda.QTDVEN,0) AS VENDAS_NF, tv.CODVENDA, tv.ENTRADA_SAIDA FROM vendas AS vda LEFT JOIN clientes AS cli ON cli.CLICOD = vda.CLIVEN LEFT JOIN tvenda AS tv ON vda.NUMVEN = tv.CODVENDA WHERE vda.CODVEN = 891 AND vda.CODVEN !=0 order by vda.DATVEN DESC já o EXPLAIN SELECT cli.CLICOD, cli.CLINOM, cli.CLICPF,tv.CODVENDA, tv.ENTRADA_SAIDA, tv.DATVENDA FROM ( SELECT vda.CODVEN,vda.DATVEN, vda.NUMVEN, vda.CLIVEN, vda.QTDVEN, vda.VALVEN, vda.ESPVEN, vda.TOTVEN, vda.MARCADO, vda.CUPOM, IF(vda.CUPOM!='S' AND vda.MARCADO!='S',vda.QTDVEN,0) AS VENDAS_OUTRAS, IF(vda.CUPOM='S', vda.QTDVEN,0) AS VENDAS_CUPOM, IF(vda.MARCADO='S',vda.QTDVEN,0) AS VENDAS_NF FROM vendas AS vda WHERE vda.CODVEN = 891 AND vda.CODVEN != 0 order by vda.DATVEN DESC ) AS t_venda_itens LEFT JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN LEFT JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA Quote Link to comment Share on other sites More sharing options...
aferra Posted October 13, 2016 Report Share Posted October 13, 2016 já tentou Troque LEFT JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN LEFT JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA por INNER JOIN clientes AS cli ON cli.CLICOD = t_venda_itens.CLIVEN INNER JOIN tvenda AS tv ON t_venda_itens.NUMVEN = tv.CODVENDA Quote Link to comment Share on other sites More sharing options...
marcioe Posted October 13, 2016 Author Report Share Posted October 13, 2016 Olá a todos os amigos Desta forma que o Ferando (EMPRESOFT) me passou ficou funcional. cQuery:= ' SELECT * FROM ( ' cQuery:= cQuery + ' SELECT cli.CLICOD, cli.CLINOM, cli.CLICPF, ' cQuery:= cQuery + ' vda.CODVEN, vda.DATVEN, vda.NUMVEN, vda.CLIVEN, vda.QTDVEN, vda.VALVEN, vda.ESPVEN, vda.TOTVEN, ' cQuery:= cQuery + ' vda.MARCADO, vda.CUPOM, ' cQuery:= cQuery + " IF(vda.CUPOM!='S' AND vda.MARCADO!='S',vda.QTDVEN,0) AS VENDAS_OUTRAS, " cQuery:= cQuery + " IF(vda.CUPOM='S', vda.QTDVEN,0) AS VENDAS_CUPOM, " cQuery:= cQuery + " IF(vda.MARCADO='S',vda.QTDVEN,0) AS VENDAS_NF, " cQuery:= cQuery + ' tv.CODVENDA, tv.ENTRADA_SAIDA ' cQuery:= cQuery + ' FROM vendas AS vda ' cQuery:= cQuery + ' LEFT JOIN clientes AS cli ON cli.CLICOD = vda.CLIVEN ' cQuery:= cQuery + ' LEFT JOIN tvenda AS tv ON vda.NUMVEN = tv.CODVENDA ' cQuery:= cQuery + ' WHERE vda.CODVEN = ' + TRANSFORMA_SQL(oCodigo_Produto,'N',07,0) cQuery:= cQuery + ' order by vda.DATVEN DESC ) AS t_venda_itens ' cQuery:= cQuery + ' order by DATVEN DESC ' SqlExecute("commit") aItens_saida:=SqlArray(cQuery) if len(aItens_saida)!=0 T_QTDE_VENDIDA_NUMERO_VEZES:=len(aItens_saida) aEval( aItens_saida , {|_1, _2| T_QTDE_VENDIDA += val(aItens_saida[_2,08] ) }) aEval( aItens_saida , {|_1, _2| T_QTDE_VENDIDA_OU += val(aItens_saida[_2,14] ) }) aEval( aItens_saida , {|_1, _2| T_QTDE_VENDIDA_CF += val(aItens_saida[_2,15] ) }) aEval( aItens_saida , {|_1, _2| T_QTDE_VENDIDA_NF += val(aItens_saida[_2,16] ) }) endif Então Caso Resolvido, obrigado a Todos Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.