Looped select queries or php processing? 1

[Dweezel]

I'm creating a web interface that includes a feature to view data in graphical format. I'll draw the graphs using data from a mysql database.

The data is a measurement of temperature over time, so the graph will have temperature on the y-axis and time on the x. It will have buttons below it that enable the user to move forward or backward through time (no Doctor Who jokes please , with a timestamp being passed forward on a url when either button is clicked.

It's this timestamp that will set what data is needed from the table. I could either do one 'BETWEEN' query fetching all data required between 2 timestamps and then loop through the results in PHP, or alternitavely put the query itself in a loop and pull the data out one record at a time.

Now the code for the second of the above options (putting the query in a loop) is the easiest to do and requires the least amout of code to create. It would mean however making upto 600 queries to the database each time the page loads and the graph is drawn.

I'm wondering if this is excessive? From a server resources standpoint do you think it would be better to pull all of the data out in one BETWEEN query and then process it with a larger and more complex piece of PHP code? Or is the query within the loop the better way to go?

Any opinions on this appreciated.

And happy holidays to all .

 

[TonyGroves]

600 queries at a time is definitely a bad idea, for several reasons.

1. There is a more-or-less fixed overhead for each query processed by the server, regardless of the size of the result set. Multiply that by 600, and you have 600 times the processing overhead.

2. A query using a BETWEEN value range is usually far more efficient than even one query based on several discrete key values.

3. How do you know which individual records are to be retrieved? If you are issuing individual queries, you need to know the key values of each record concerned. With one BETWEEN query, you are guaranteed to get them all.

4. I'm sure there are many more reasons, but hey, it's Christmas Eve, and I shouldn't be here!

 

[Dweezel]

Thanks Tony. Retrieving the individual records wouldn't be a problem as they're at fixed intervals of 15 minutes. But as you say, 600 times the overhead = bad idea.

Thanks for clearing that up mate. Have a good Christmas.