quicker performace wise - SQL or PHP

[arturski]

Hi guys,

I am having a performance issue with a site and am trying to find the best approach to optimize it.

There is a large table of data that loads on the page the size of it depends on the day. When the data size is large the page can take up to 5 - 10 seconds to load which is not great however this really only happens during peak times and i really want to improve this.

At the moment i have a large JOIN query pulling all the data into a huge rectangular array and then I process it all via PHP loops.

I am wondering if it would be quicker to have smaller loops but more SQL Queries that return smaller sections of data.

For Example:

at the moment an array gets returned that has 3 layers and many many categories which ends up returning a lot of data

Category
--|_Items
-----|_Detals

Php then loops through it all breaking it down into a visible table.

What I want to know is:

Will it be a lot faster if I run a query for each category and have more queries but smaller loops in PHP?

 

[visibleman]

In most situations, joins tend to be quicker over 'round-trips' but obviously this is not always the case, especially if you're dealing with large rows, blobs etc.

First make sure you're indexing correctly, then secondly have a look at the slow queries log and see if that highlights anything you can do to optimise your current query.

 

[AJK]

When the data size is large the page can take up to 5 - 10 seconds to load which is not great however this really only happens during peak times and i really want to improve this.

Have you done any debugging to isolate exactly where the bottleneck is? I mean, do you know if your problem is the SQL query, the IO between the database and the web server, or the PHP code?

At the moment i have a large JOIN query pulling all the data into a huge rectangular array and then I process it all via PHP loops.

Danger Will Robinson. Just how big is "huge" and how many "PHP loops" are we talking about?

I am wondering if it would be quicker to have smaller loops but more SQL Queries that return smaller sections of data.

Without further information on your circumstances, I'm going to say yes it will. Make the database do as much work as possible. Obviously there's a sensible limit to this (you don't want to issue a separate query to retrieve each row of a table, for example), but if you are just issuing one massive query with many joins and getting back a huge volume of data to iterate through, that's not going to have great performance.

 

[kkelly]

Are there index in you're tables ?

Can you get details of the execution plan to see where the highest cost is ?

 

[fez]

Do what the others have said and if you can, post the db table structure up.

10s is a huge amount of time for a page load but that can be because of bottlenecks in a number of places. Even a single inefficient php loop can kill the efficiency of a page load.

How many queries pull the data back over the course of the run. If its only a few then you can just run them on the command line to see how slow they are.

With regards to php and mysql I think it is almost always quicker to do as much as you can in mysql before coming back to php. Obviously the few queries you can make the better but an average web page that has 20 smallish queries will take 0.4 of a second to execute the whole script and return the page. (the rest is obviously resources, css, js etc)

 

[Haircut]

This question can't be answered without knowing a bit more about your architecture, but as others have said it will generally be quicker to do the query in one go rather than have multiple round trips.

Even if your application server and db are on the same box it will normally be quicker to get the data in one go from the database.

Make sure you're not pulling any more data from the database than you need to. If your doing a query to get a big chunk of data and then discarding lots of it in the PHP then you probably want to change that.

At the end of the day you need to find out whether you're CPU bound (sounds unlikely) or IO bound, and if IO bound is it disk or network?