php - SQL statement takes 26 seconds in laravel to execute, at phpmyadmin takes .0075 seconds
Get the solution ↓↓↓I have a sql statement that involve 6 tables, in laravel takes 26 seconds to execute it, but the same sql sentence takes around 0.0075 to .0089 seconds in phpmyadmin.
This is my code in laravel:
use DB;
$partidas = DB::select("SELECT A.Patente, A.Pedimento, A.SeccionAduanera, A.Fraccion, A.SecuenciaFraccion, A.ValorComercial, A.PrecioUnitario, A.CantidadUMComercial, A.UnidadMedidaComercial, A.CantidadUMTarifa, A.UnidadMedidaTarifa, A.MetodoValorizacion, A.PaisOrigenDestino, A.PaisCompradorVendedor,
B.ClavePermiso, B.NumeroPermiso,
C.ClaveCaso, C.IdentificadorCaso, C.ComplementoCaso,
D.ClaveContribucion, D.FormaPago, D.ImportePago,
E.TasaContribucion, E.TipoTasa,
F.Observaciones
FROM `551` A
INNER JOIN `553` B ON B.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = B.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND B.auditoria_id = 4)
AND (A.Patente = '3452' AND B.Patente = '3452')
AND (A.Pedimento = '0000180' AND B.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND B.SeccionAduanera = '430')
INNER JOIN `554` C ON C.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = C.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND C.auditoria_id = 4)
AND (A.Patente = '3452' AND C.Patente = '3452')
AND (A.Pedimento = '0000180' AND C.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND C.SeccionAduanera = '430')
INNER JOIN `557` D ON D.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = D.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND D.auditoria_id = 4)
AND (A.Patente = '3452' AND D.Patente = '3452')
AND (A.Pedimento = '0000180' AND D.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND D.SeccionAduanera = '430')
INNER JOIN `556` E ON E.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = E.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND E.auditoria_id = 4)
AND (A.Patente = '3452' AND E.Patente = '3452')
AND (A.Pedimento = '0000180' AND E.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND E.SeccionAduanera = '430')
INNER JOIN `558` F ON F.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = F.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND F.auditoria_id = 4)
AND (A.Patente = '3452' AND F.Patente = '3452')
AND (A.Pedimento = '0000180' AND F.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND F.SeccionAduanera = '430')
");
dd($partidas);
I need to know if i can make the statement with eloquent or another way to have a better performance in laravel.
Thank you!
PD: English isn't my first language, so please excuse any mistakes!
Answer
Solution:
Try this it maybe has the time like in your phpmyadmin
<?php
use DB;
$pdo = DB::connection()->getPdo();
$SQL = "SELECT A.Patente, A.Pedimento, A.SeccionAduanera, A.Fraccion, A.SecuenciaFraccion, A.ValorComercial, A.PrecioUnitario, A.CantidadUMComercial, A.UnidadMedidaComercial, A.CantidadUMTarifa, A.UnidadMedidaTarifa, A.MetodoValorizacion, A.PaisOrigenDestino, A.PaisCompradorVendedor,
B.ClavePermiso, B.NumeroPermiso,
C.ClaveCaso, C.IdentificadorCaso, C.ComplementoCaso,
D.ClaveContribucion, D.FormaPago, D.ImportePago,
E.TasaContribucion, E.TipoTasa,
F.Observaciones
FROM `551` A
INNER JOIN `553` B ON B.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = B.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND B.auditoria_id = 4)
AND (A.Patente = '3452' AND B.Patente = '3452')
AND (A.Pedimento = '0000180' AND B.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND B.SeccionAduanera = '430')
INNER JOIN `554` C ON C.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = C.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND C.auditoria_id = 4)
AND (A.Patente = '3452' AND C.Patente = '3452')
AND (A.Pedimento = '0000180' AND C.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND C.SeccionAduanera = '430')
INNER JOIN `557` D ON D.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = D.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND D.auditoria_id = 4)
AND (A.Patente = '3452' AND D.Patente = '3452')
AND (A.Pedimento = '0000180' AND D.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND D.SeccionAduanera = '430')
INNER JOIN `556` E ON E.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = E.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND E.auditoria_id = 4)
AND (A.Patente = '3452' AND E.Patente = '3452')
AND (A.Pedimento = '0000180' AND E.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND E.SeccionAduanera = '430')
INNER JOIN `558` F ON F.Fraccion = A.Fraccion
AND (A.SecuenciaFraccion = F.SecuenciaFraccion)
AND (A.auditoria_id = 4 AND F.auditoria_id = 4)
AND (A.Patente = '3452' AND F.Patente = '3452')
AND (A.Pedimento = '0000180' AND F.Pedimento = '0000180')
AND (A.SeccionAduanera = '430' AND F.SeccionAduanera = '430')
";
$stmt = $pdo->prepare($SQL);
$stmt->execute();
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
Answer
Solution:
Yes, there is always a room for improvement.
- Start by using Eloquent Relationship instead of raw mysql. If you are gonna use Laravel, then please go through the documentation, it doesn't get any easier than that.
- Second, use select() to minimize unnecessary use/consumption of data. Optimizing code is a good skill to acquire. Check out this answer on optimization on laravel.
Using data type the right way.
A.Patente = '3452'
If your Patente is of type int then comparing with string will take more processing time. Instead doA.Patente = 3452
. Compare int with int.
I am confident that when you follow these steps, your query should not take more than 1 sec. Keep me posted in the comments below. Cheers!
Answer
Solution:
Thanks to discord's user pablov or pablete for helping me solve my question!
It should be noted that I did tests removing tables, for example I started with A and B, it worked well, ABC etc. Until with F the problem of slowness began, I suppose it was because of so many tables and records that I had to review, until Pablete gave me this option and it worked.
The sentence was as follows:
$partidas = DB::select("SELECT A.Patente, A.Pedimento, A.SeccionAduanera, A.Fraccion, A.SecuenciaFraccion, A.ValorComercial, A.PrecioUnitario,
A.CantidadUMComercial, A.UnidadMedidaComercial, A.CantidadUMTarifa, A.UnidadMedidaTarifa, A.MetodoValorizacion, A.PaisOrigenDestino,
A.PaisCompradorVendedor,
B.ClavePermiso, B.NumeroPermiso,
C.ClaveCaso, C.IdentificadorCaso, C.ComplementoCaso,
D.ClaveContribucion, D.FormaPago, D.ImportePago,
E.TasaContribucion, E.TipoTasa,
F.Observaciones
FROM `551` A
LEFT JOIN `553` B ON B.Fraccion = A.Fraccion AND (A.SecuenciaFraccion = B.SecuenciaFraccion) AND (A.auditoria_id = B.auditoria_id) AND (A.Patente = B.Patente) AND (A.Pedimento = B.Pedimento) AND (A.SeccionAduanera = B.SeccionAduanera)
LEFT JOIN `554` C ON C.Fraccion = B.Fraccion AND (B.SecuenciaFraccion = C.SecuenciaFraccion) AND (B.auditoria_id = C.auditoria_id) AND (B.Patente = C.Patente) AND (B.Pedimento = C.Pedimento) AND (B.SeccionAduanera = C.SeccionAduanera)
LEFT JOIN `557` D ON D.Fraccion = A.Fraccion AND (A.SecuenciaFraccion = D.SecuenciaFraccion) AND (A.auditoria_id = D.auditoria_id) AND (A.Patente = D.Patente) AND (A.Pedimento = D.Pedimento) AND (A.SeccionAduanera = D.SeccionAduanera)
LEFT JOIN `556` E ON E.Fraccion = A.Fraccion AND (A.SecuenciaFraccion = E.SecuenciaFraccion) AND (A.auditoria_id = E.auditoria_id) AND (A.Patente = E.Patente) AND (A.Pedimento = E.Pedimento) AND (A.SeccionAduanera = E.SeccionAduanera)
LEFT JOIN `558` F ON F.Fraccion = A.Fraccion AND (A.SecuenciaFraccion = F.SecuenciaFraccion) AND (A.auditoria_id = F.auditoria_id) AND (A.Patente = F.Patente) AND (A.Pedimento = F.Pedimento) AND (A.SeccionAduanera = F.SeccionAduanera)
Where A.auditoria_id = 4
And A.Patente = '3452'
And A.Pedimento = '0000180'
And A.SeccionAduanera = '430'
");
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: your lock file does not contain a compatible set of packages. please run composer update.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.