MySQL Query SQL para procedimento Otimizar

[jeffersoncg]

Bom dia, sou novo aqui no Clube do Hardware, mas sempre acompanhei as dicas que o pessoal passa, dessa vez resolvi me inscrever pra pedir ajuda também.

Tenho esse código, mas ele tá muito mal otimizado, eu jogo ele em uma query no Delphi, mas como as vezes tem muitos resultados, fica muito lento.

Eu não entendo de SQL, alguém poderia me ajudar a converter ele para uma função/procedimento ou otimiza-lo?

Eu realmente agradeço, preciso muito desse código.

As condições de busca, eu jogarei depois, ainda estou desenvolvendo essa parte.

 

A função CnvDt apenas junta dois campos separados Date e Time em um DateTime pra poder fazer comparação de data e hora.

 

SELECT Codigo, EstabCodigo, EstabNome, VendaCodigo, CartaoCodigo, ConvCodigo, ClienteCodigo, ClienteNome, ValorBruto, ValorLiquido, Taxa, DescJur, VeiculoCodigo, VeiculoPlaca, VeiculoModelo, MotoristaCodigo, MotoristaNome,
CnvDt(DATAVENDA,HORAVENDA) as DataEHora, VeiculoKM as KMAtual,
ifnull((SELECT max(VeiculoKM) from vendascartao as vendascartaoa where vendascartaoa.VeiculoCodigo= vendascartao.VeiculoCodigo and CnvDt(vendascartaoa.DATAVENDA,vendascartaoa.HORAVENDA) < CnvDt(vendascartao.DATAVENDA,vendascartao.HORAVENDA)), (select veiculos.KMInicial from veiculos where veiculos.codigo = vendascartao.veiculocodigo)) AS KMAnterior,
(VeiculoKM-ifnull((SELECT max(VeiculoKM) from vendascartao as vendascartaoa where vendascartaoa.VeiculoCodigo= vendascartao.VeiculoCodigo and CnvDt(vendascartaoa.DATAVENDA,vendascartaoa.HORAVENDA) < CnvDt(vendascartao.DATAVENDA,vendascartao.HORAVENDA)), (select veiculos.KMInicial from veiculos where veiculos.codigo = vendascartao.veiculocodigo))) AS KMPercorrido,
ifnull((select vendasdetalhamento.quantidade from vendasdetalhamento where vendasdetalhamento.VendaCodigo = vendascartao.VendaCodigo and vendasdetalhamento.ProdutoCod = (select veiculos.CombustivelCodigo from veiculos where veiculos.Codigo = vendascartao.VeiculoCodigo)), 0) AS LitrosAbst,
ifnull(((VeiculoKM-ifnull((SELECT max(VeiculoKM) from vendascartao as vendascartaoa where vendascartaoa.VeiculoCodigo= vendascartao.VeiculoCodigo and CnvDt(vendascartaoa.DATAVENDA,vendascartaoa.HORAVENDA) < CnvDt(vendascartao.DATAVENDA,vendascartao.HORAVENDA)), (select veiculos.KMInicial from veiculos where veiculos.codigo = vendascartao.veiculocodigo))))/ifnull((select vendasdetalhamento.quantidade from vendasdetalhamento where vendasdetalhamento.VendaCodigo = vendascartao.VendaCodigo and vendasdetalhamento.ProdutoCod = (select veiculos.CombustivelCodigo from veiculos where veiculos.Codigo = vendascartao.VeiculoCodigo)), 0), 0) as MediaAtual
FROM `vendascartao`

 

Muito obrigado mesmo, de verdade.

[joseph_dev]

Cara, sem o modelo do seu banco para ver os relacionamentos fica difícil. Mas já te adianto que o motivo dessa lentidão é a grande quantidade de subselects.

Tem que ver quais dados você precisa realmente nessa consulta, pois me parece ter algumas subconsultas sem relação. Talvez até uma VIEW seja melhor nessa situação.

[jeffersoncg]

Eu estava pensando se não tem um jeito de alterar esses SubSelects repetidos, pra uma variavel, deixar apenas um Select, que passa o valor pra variavel e a variavel nos outros, pois varios dos selects são o mesmo, mas é que como são informações diferentes que são puxadas, eu preciso confirmar toda vez.

Estava pensando em algo tipo:
 

declare @DtLanc = CnvDt(DATAVENDA,HORAVENDA);
declare @KmInicial = (select veiculos.KMInicial from veiculos where veiculos.codigo = vendascartao.veiculocodigo);
declare @KmAnt = ifnull((SELECT max(VeiculoKM) from vendascartao as vendascartaoa where vendascartaoa.VeiculoCodigo = vendascartao.VeiculoCodigo and CnvDt(vendascartaoa.DATAVENDA,vendascartaoa.HORAVENDA) < @DtLanc), @KmInicial);
declare @KmPerc = (VeiculoKM-@KmAnt, @KmInicial)))
declare @CombCodigo = (select veiculos.CombustivelCodigo from veiculos where veiculos.Codigo = vendascartao.VeiculoCodigo);
declare @LtsAbst = ifnull((select vendasdetalhamento.quantidade from vendasdetalhamento where vendasdetalhamento.VendaCodigo = vendascartao.VendaCodigo and vendasdetalhamento.ProdutoCod = @CombCodigo), 0);
declare @MdAtual = ifnull(KmPerc/LtsAbst, 0);

SELECT Codigo, EstabCodigo, EstabNome, VendaCodigo, CartaoCodigo, ConvCodigo, ClienteCodigo, ClienteNome, ValorBruto, ValorLiquido, Taxa, DescJur, VeiculoCodigo, VeiculoPlaca, VeiculoModelo, MotoristaCodigo, MotoristaNome,
@DtLanc as DataEHora, VeiculoKM as KMAtual,  @KmAnt AS KMAnterior, KmPerc AS KMPercorrido, @LtsAbst AS LitrosAbst, @MdAtual as MediaAtual FROM `vendascartao`

 

O que esta declarado como @Variavel são as definidas, as que não estão, são campos da tabela que vão contem informação.

 

Seria possível fazer algo assim?
Isso daria uma ótima reduzida no código, mas eu não entendo de SQL, então não sei a estruturação que se usa na hora de escrever um código.

[joseph_dev]

Não tem muito sentido você criar uma função para retornar apenas selects sem nenhuma operação. O certo seria fazer os relacionamentos corretos e reduzir o número de subSelects. Aí sim você vai otimizar seu código. Ou você pode fazer cada consulta separada no código de seu sistema (cada uma com sua respectiva clausula where). Eu acredito que ainda sairá mais rápido do que criar pencas de subSelects aleatórios. Aí você vai atribuindo às variáveis na sua regra de negócio. Acho esse armengue o menos ruim.

[jeffersoncg]

Eu estou tentando seguir sua dica, e otimizar ele usando inner Join, mas me surgiu uma dúvida.

 

Como eu converteria a função seguinte, sem que ela fizesse minha função inteira chamar apenas 1 Record?

 

ifnull((select max(VeiculoKM) from vendascartao where vendascartao.VeiculoCodigo = Cartoes.VeiculoId), KmInicial) as UltimoKM

 

Eu chamo assim, pois quando tento chama apenas o max(veiculoKm), ele faz toda a função retornar apenas um record, além eu não poder fazer verificações nele.

 

Estou tentando chamar ele assim:

Como aquele código acima esta muito bagunçado, estou tentando simplificar um mais simples, pra tentar entender melhor a "simplificação", ai tento simplificar ele também.

 

O código que utilizo esse acima é o seguinte:

select cartoes.Codigo, Uid, Serial, ConvenioId, ConvenioNome, UtilizadorId, UtilizadorNome, CartaoNumero, Ativado, DtCadastro, DtUltPassagem, Historico, Senha, Bloqueado, VeiculoId, 
Placa, MarcaModelo, Concat(veiculos.AnoFab, "/",veiculos.AnoMod) as Ano, ifnull((select max(VeiculoKM) from vendascartao where vendascartao.VeiculoCodigo = Cartoes.VeiculoId), KmInicial) as UltimoKM,
funcionarios.Codigo as MotoristaCodigo, funcionarios.Nome as MotoristaNome, funcionarios.CPF as MotoristaCPF
from cartoes 
inner join veiculos on veiculos.Codigo = cartoes.VeiculoId
inner join funcionarios on funcionarios.Codigo = veiculos.MotoristaCodigo

[joseph_dev]

1 hora atrás, jeffersoncg disse:

Eu chamo assim, pois quando tento chama apenas o max(veiculoKm), ele faz toda a função retornar apenas um record, além eu não poder fazer verificações nele.

 

Quando você usa o MAX() sem agrupar por algum campo (GROUP BY) automaticamente o mysql entende que você está agrupando no geral. Por isso só retorna o maior campo desse geral e agrupa todos os resultados em um.

 

Pelos campos dessa consulta eu acredito que você precisa fazer um LEFT com vendascartao (já que ele pode retornar NULL):

SELECT
car.Codigo,
MAX(Uid),
MAX(Serial),
MAX(ConvenioId),
MAX(ConvenioNome),
MAX(UtilizadorId),
MAX(UtilizadorNome),
MAX(car.CartaoNumero),
MAX(Ativado),
MAX(DtCadastro),
MAX(DtUltPassagem),
MAX(Historico),
MAX(Senha),
MAX(Bloqueado),
MAX(VeiculoId), 
MAX(car.Placa),
MAX(car.MarcaModelo),
CONCAT(MAX(vei.AnoFab), "/",MAX(vei.AnoMod)) AS Ano,
MAX(IFNULL(vca.VeiculoKM, KmInicial)) AS UltimoKM,
/*se der erro tente assim: IFNULL(MAX(vca.VeiculoKM), MAX(KmInicial)) AS UltimoKM --não tive como testar*/
MAX(fun.Codigo) AS MotoristaCodigo,
MAX(fun.Nome) AS MotoristaNome,
MAX(fun.CPF) AS MotoristaCPF
FROM cartoes car
JOIN veiculos vei ON vei.Codigo = car.VeiculoId
JOIN funcionarios fun ON fun.Codigo = vei.MotoristaCodigo
LEFT JOIN vendascartao vca ON vca.VeiculoCodigo = car.VeiculoId
GROUP BY car.Codigo

Só tem que ver os campos do agrupador pois não sei a definição de sua consulta e nem o seu modelo... Estou sugerindo pelo que pude analisar no seu código. Nesse caso aí ele vai agrupar por cada cartão e vai trazer os campos do cartão, o carro utilizado, o funcionário cadastrado e vai trazer o maior registro VeiculoKm da vendascartao caso tenha algum.

 

*Quando você usa o GROUP BY, para todos os campos que não estão no agrupador (na linha GROUP BY) você precisa usar o MAX() ou MIN() ou até AVG(), SUM(), etc, a depender de sua necessidade, é claro. Na maioria das vezes o MAX() é usado pois o campo vai ter o mesmo valor no agrupamento.

 

*Eu não consegui identificar a tabela de cada campo no select. Depois coloque o alias de cada tabela em cada campo. É uma boa prática.

Ex:

Para campos da tabela cartoes o alias está car (eu defini aí mas você escolhe).

Para campos da tabela funcionarios o alias está fun (eu defini aí mas você escolhe).

Logo, no select, eu faço a seguinte referência:

SELECT
car.codigo,
car.descricao,
fun.codigo,
fun.nome
...
FROM cartoes car
JOIN funcionarios fun ON fun.codigo = car.codfuncionario
WHERE car.codigo = 10

 

*Não sei qual a versão do seu MYSQL, mas dê preferência em usar caixa alta (maiúsculas) pelo menos nas palavras reservadas do SQL. Nos campos das tabelas, use como está definido no banco (maiúscula ou minúscula).

 

Se não funcionar de primeira eu acredito que com um pequeno ajuste ao seu modelo já funcione.

[jeffersoncg]

21 minutos atrás, joseph_dev disse:

Quando você usa o MAX() sem agrupar por algum campo (GROUP BY) automaticamente o mysql entende que você está agrupando no geral. Por isso só retorna o maior campo desse geral e agrupa todos os resultados em um.

 

Pelos campos dessa consulta eu acredito que você precisa fazer um LEFT com vendascartao (já que ele pode retornar NULL):

SELECT
car.Codigo,
MAX(Uid),
MAX(Serial),
MAX(ConvenioId),
MAX(ConvenioNome),
MAX(UtilizadorId),
MAX(UtilizadorNome),
MAX(car.CartaoNumero),
MAX(Ativado),
MAX(DtCadastro),
MAX(DtUltPassagem),
MAX(Historico),
MAX(Senha),
MAX(Bloqueado),
MAX(VeiculoId), 
MAX(car.Placa),
MAX(car.MarcaModelo),
CONCAT(MAX(vei.AnoFab), "/",MAX(vei.AnoMod)) AS Ano,
MAX(IFNULL(vca.VeiculoKM, KmInicial)) AS UltimoKM,
/*se der erro tente assim: IFNULL(MAX(vca.VeiculoKM), MAX(KmInicial)) AS UltimoKM --não tive como testar*/
MAX(fun.Codigo) AS MotoristaCodigo,
MAX(fun.Nome) AS MotoristaNome,
MAX(fun.CPF) AS MotoristaCPF
FROM cartoes car
JOIN veiculos vei ON vei.Codigo = car.VeiculoId
JOIN funcionarios fun ON fun.Codigo = vei.MotoristaCodigo
LEFT JOIN vendascartao vca ON vca.VeiculoCodigo = car.VeiculoId
GROUP BY car.Codigo

Só tem que ver os campos do agrupador pois não sei a definição de sua consulta e nem o seu modelo... Estou sugerindo pelo que pude analisar no seu código. Nesse caso aí ele vai agrupar por cada cartão e vai trazer os campos do cartão, o carro utilizado, o funcionário cadastrado e vai trazer o maior registro VeiculoKm da vendascartao caso tenha algum.

 

*Quando você usa o GROUP BY, para todos os campos que não estão no agrupador (na linha GROUP BY) você precisa usar o MAX() ou MIN() ou até AVG(), SUM(), etc, a depender de sua necessidade, é claro. Na maioria das vezes o MAX() é usado pois o campo vai ter o mesmo valor no agrupamento.

 

*Eu não consegui identificar a tabela de cada campo no select. Depois coloque o alias de cada tabela em cada campo. É uma boa prática.

Ex:

Para campos da tabela cartoes o alias está car (eu defini aí mas você escolhe).

Para campos da tabela funcionarios o alias está fun (eu defini aí mas você escolhe).

Logo, no select, eu faço a seguinte referência:

SELECT
car.codigo,
car.descricao,
fun.codigo,
fun.nome
...
FROM cartoes car
JOIN funcionarios fun ON fun.codigo = car.codfuncionario
WHERE car.codigo = 10

 

*Não sei qual a versão do seu MYSQL, mas dê preferência em usar caixa alta (maiúsculas) pelo menos nas palavras reservadas do SQL. Nos campos das tabelas, use como está definido no banco (maiúscula ou minúscula).

 

Se não funcionar de primeira eu acredito que com um pequeno ajuste ao seu modelo já funcione.

 

Muito obrigado Joseph, era exatamente isso que eu precisava, e obrigado também pela explicação. Consegui entender bem melhor agora como trabalhar com SQL.

Estou utilizando a versão 5.7 do MYSQL, testei seu código aqui e funcionou perfeitamente. 

 

Vou tentar adaptar o outro também.

Mais uma vez, muito obrigado, e um forte abraço.

 

[joseph_dev]

Valeu, meu velho!

Ainda bem que deu certo.

Depois que você mandou o último código com os joins deu pra entender melhor os relacionamentos.

[jeffersoncg]

Eu ia tentar lhe enviar por MP pra não fazer post desnecessário, mas como sou novo, não tenho permissão pra enviar MP.

Aqui o código como consegui corrigir, não sei se essa é a maneira mais correta, mas é o máximo que consegui.

 

SELECT
vca.Codigo,
MAX(vca.EstabCodigo) AS EstabCodigo,
MAX(vca.EstabNome) AS EstabNome,
MAX(vca.VendaCodigo) AS VendaCodigo,
MAX(vca.CartaoCodigo) AS CartaoCodigo,
MAX(vca.ConvCodigo) AS ConvCodigo,
MAX(vca.ClienteCodigo) AS ClienteCodigo,
MAX(vca.ClienteNome) AS ClienteNome,
MAX(vca.ValorBruto) AS ValorBruto,
MAX(vca.ValorLiquido) AS ValorLiquido,
MAX(vca.Taxa) AS Taxa,
MAX(vca.DescJur) AS DescJur,
MAX(vca.VeiculoCodigo) AS VeiculoCodigo,
MAX(vca.VeiculoPlaca) AS VeiculoPlaca,
MAX(vca.VeiculoModelo) AS VeiculoModelo,
MAX(vca.MotoristaCodigo) AS MotoristaCodigo,
MAX(vca.MotoristaNome) AS MotoristaNome,
MAX(vca.DataVenda) AS DataVenda,
MAX(vca.HoraVenda) AS HoraVenda,
MAX(vca.VeiculoKM) AS KMAtual,
MAX(IFNULL(vcant.VeiculoKM, vei.KMInicial)) AS KMAnterior,
MIN(IFNULL(vca.VeiculoKM-IFNULL(vcant.VeiculoKM, vei.KMInicial), 0)) AS KMPercorrido,
SUM(IFNULL(vdet.quantidade, 0)) as LitrosAbastQuant,
SUM(IFNULL(vdet.valorliquido, 0)) as LitrosAbastValor,
MAX((IFNULL(vca.VeiculoKM-IFNULL(vcant.VeiculoKM, vei.KMInicial), 0)/(IFNULL(vdet.quantidade, 0)))) AS MediaAtual FROM vendascartao vca
LEFT JOIN vendascartao vcant ON vcant.VeiculoCodigo = vca.VeiculoCodigo AND vca.DataHora > vcant.DataHora
JOIN veiculos vei ON vei.codigo = vca.VeiculoCodigo
JOIN vendasdetalhamento vdet ON vdet.VendaCodigo = vca.VendaCodigo AND vdet.ProdutoCod = vei.CombustivelCodigo
GROUP BY vca.codigo

 

Mais uma vez Joseph, obrigado, tu salvou meu sistema, já que ele é totalmente baseado em controle de frota, e precisava funcionar sem muita lentidão, já que vai estar em computadores fora da rede, via internet.

[joseph_dev]

Massa, Jefferson.

Você já pegou o embalo.

Só dá uma olhada nesse tanto de IFNULL. Será que são todos necessários mesmo?

Valeu!