SQL statement takes 26 seconds in laravel to execute, at phpmyadmin takes .0075 seconds

Question

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.

phpmyadmin

laravel

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!

Something went fundamentally awry when you constructed a table called 551 — Strawberry, May 23 '20 at 23:32
could you explain why do you have identoical dataabases to search for the same data. — nbk, May 23 '20 at 23:38
It is a program to audit customs. Customs sends files (hence the name 551, 553, etc.). And in each file comes certain information, in 551 the products, 553 insurance, 554 seller, etc. To audit it, the client needs to join certain data that are in different tables. The fields that I put in the statement are the ones that join the tables. — Mundoco, May 23 '20 at 23:52
Is your PHP code remote or in the same server as the database? I say that because phpMyAdmin is probably in the same server, if the query is exactly the same, maybe your connection is too slow. — lbrandao, May 24 '20 at 01:11
Can you measure the exact execution time of the `DB::select`? here is how to do that https://stackoverflow.com/a/18336968 — Vinay, May 24 '20 at 07:04

score 0 · Answer 1 · answered May 23 '20 at 23:38

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

Thanks for your answer! But it still takes the same time. I changed the last line $stmt->fetchAll($pdo::FETCH_ASSOC); — Mundoco, May 24 '20 at 00:17

score 0 · Answer 2 · answered May 24 '20 at 05:17

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 do A.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!

score 0 · Accepted Answer · answered May 27 '20 at 21:50

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'
                                ");

SQL statement takes 26 seconds in laravel to execute, at phpmyadmin takes .0075 seconds

3 Answers3