Controller response very slow on query that runs fast in phpmyadmin

Can you paste the laravel app code for that query? And also the basic profiling dump?

Cheers

Yeah.

I had this on a repository and stuff, but it has become a little messy just trying to find out why this is happening.

Here it is.

public function getRange($node, $category, $fromDate, $toDate) {

        $fromString = Carbon::createFromFormat('Y-m-d',$fromDate)->toDateString();
        $toString   = Carbon::createFromFormat('Y-m-d',$toDate)->toDateString();

        $sql = "SELECT
                i.id, i.description AS ingredient, effective_date, uom.symbol as purchase_uom,
                (sum(ri.regular_qty * t.regular) + sum(ri.large_qty * t.large)) * uc.factor AS qty

                FROM

                (
                    SELECT m.id AS mealID, effective_date,
                    SUM(CASE
                        WHEN c.portion = -1 THEN 1
                        WHEN c.portion =  0 THEN 1
                        WHEN c.portion =  2 THEN 2
                    END) AS regular,
                    SUM(CASE
                        WHEN c.portion = 1 THEN 1
                    END) AS large
                    FROM client c
                    JOIN client_menu cm ON c.idUser=cm.idClient
                    JOIN meal m ON cm.id_meal=m.id
                    WHERE effective_date BETWEEN '$fromString' AND '$toString'
                    AND c.node_id = '$node'
                    AND id_meal >0
                    GROUP BY effective_date, m.id
                ) AS t

                JOIN recipes r ON t.mealID = r.meal_id
                JOIN recipe_ingredients ri ON r.id = ri.recipe_id
                JOIN ingredients i ON ri.ingredient_id = i.id
                JOIN uom_conversions uc ON uc.uom_from = i.recipe_uom AND uc.uom_to = i.purchase_uom
                JOIN uom on uom.id = i.purchase_uom 
                GROUP BY effective_date, i.id ";

        $queryStart = Carbon::now();

        $data = DB::select($sql);
        
        $queryEnd = Carbon::now();

        $response['data'] = $data;
        $response['queryStart'] = $queryStart;
        $response['queryEnd'] = $queryEnd;
        $response['sql'] = $sql;

        return response()->json($response);
    }

Query profile I don't have a screenshot now...

Also, I did an old school mysqli connection and got a 1 second response....

Did u try running this query like in a route so that u're sure there's not other interference going on?

@cristian9509 yes, I did..

@marcoacm I keep on looking at this line and it feels like it's not the right approach:

$data = DB::select($sql);

I believe that you should be using DB::raw along withDB::select. You already include the select in your query. Try

$data = DB::select(DB::raw($sql));

and hopefully you should go back to normal.

Note: keep in mind that DB::raw is completely unsanitized and therefore open to attack!

@cristian9509, thanks.

I had DB::raw there before. It makes no difference...

Just to debug - if you run a somewhat simpler query via DB::select() does it go slow relative to 'plain' mysql too? Just to narrow down if it's this specific query that's the problem or if it's the DB::select() call itself?

@ohffs, I did very simple select and it responded in 1 sec. However, like I said before, when I run that query on my client it runs in 22 milliseconds.

I am desperate here.... Somebody help please!

You need to profile the calls within the Db::select method, which one is taking most of the time ?

Anybody?

Sorry @premsaurav I didn't understand your point...

get the time each statement within the Db::select takes to execute ?

there is only 2 select statements in the query. The inner one is taking most the time (39 secs out of 40). @premsaurav

Really out of ideas :-/ So just trying to debug.

1. Is your database local or remote?
2. Is your phpmyadmin client local or remote?
3. How many rows are being returned? phpmyadmin tends to limit the rows unless specified ?

Also, I did an old school mysqli connection and got a 1 second response....

4. Forget laravel for the time being, does a simple php page using PDO improve the result ?

1. remote (local works great)
2. remote (local works great)
3. 100
4. yes, quick results. Excellent performance. @premsaurav

So here is what I did. I put my query on a store procedure and called it. Sure enough it runs in 1 second!

I do not get it! can someone help understand this?

Have you tried using something like xdebug and webgrind to find out where the bottleneck might be?

So many degrees of freedom here :( Remote vs local , PDO vs mysqli, SP vs SQL query , head's blown? As @thomaskim mentioned, have something and more to try determine the bottleneck, sorry mate :(

@thomaskim, not those, but we used other profilers that show us that the time is being spent on the query itself. There are no errors or anything else running before or after the query. Like I mentioned above the store procedure works perfectly, and it is still just one statement being called from PHP.

Hi,

Are you sure that you are using DB::raw?

$data = DB::select( DB::raw($query) ); 

I would try debug the queries with the Debug Bar, to see how much time it takes, and which is taking longer,... It's very easy to use and install: https://github.com/barryvdh/laravel-debugbar

Cheers

@diugo, I am positive. I used the debug bar as well...

@marcoacm You use some variables in your raw query. Can you dump the actual query executed by Laravel and post it here?

Another thing, do you actually get the expected result even if the query runs for 40 seconds compared to the one you run straight in mysql and that runs in 1sec.

@cristian9509 my very first post in this discussion shows the values

WHERE effective_date BETWEEN '2016-01-20' AND '2016-01-20'
AND c.node_id = '3'

Another thing, do you actually get the expected result even if the query runs for 40 seconds compared to the one you run straight in mysql and that runs in 1sec.

I do get the extepected result back

@marcoacm That is just crazy. There is one more thing you can do. Just make another fresh Laravel installation and run the query from there. If that does not work that means that something, somewhere is very wrong with your query.

@cristian9509, like I said, my query is fine. I run it all the time in phpmyadmin or sequel or any other client and runs in 0.22 seconds.

If you run the query without the variables (that is - hardcode the expected values) does it still run slow? If so you might be able to raise an issue on github and see if they have any suggestions?

@ohffs,

Again! The query runs well everywhere else but Laravel via DB::raw.

Yep - I know, but if you try it with the hardcoded variables you can rule out one last possibility and then report it as a bug/issue on github so the folk who write the framework can look :-)

Just wanted to say I've experienced the same issue with Laravel 5.0. Have not tried newer versions. But essentially running a query in Laravel takes 28ms while running the EXACT same query in phpmyadmin takes only 0.7ms. Such a drastic difference in performance. :(

@marcoacm, did you ever figure out why this happened?