Ok I think I know the answer to this but I need a little reassurance. Is it truly faster to use "SELECT SUM(votes) FROM table" instead of
"SELECT * FROM table" and then adding the results using a while loop. I can see that it should be but I am not sure if using the sum command puts extra load on the database that would maybe be better handled by the webserver
Any thoughts?
SQL SUM question
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Think of it this way. Your web server is working to serve pages. The code engine (PHP, ASP, CF, whatever) is working to handle the instructions presented by the code. The Database Server is also working to server data, and it does it pretty well. The more work your database server can reasonably do, the less work your web server has to do with data. So even if there is a performance loss on the database server by handling an instruction that is more complex than 'SELECT this', it is still, overall, going to be less of a performance hit that having your code engine, which is already working a ton, handle the data that is returned by the database server.
Besides, the database servers have those functions built in so they can be used. Why not use them?
Besides, the database servers have those functions built in so they can be used. Why not use them?
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
Actually using the SUM in your example puts far LESS strain on the database server than grabbing every single record and field from the table.
Will cause the database server to add all of the vote fields together and send one record to PHP. This will use far less processor time on the database side and transfer only a very tiny amount of data from the database server.
The above will cause the database server to collect every single record with ALL fields to send to PHP. This means the database server has to use far more processor time collecting the full record sets for the ENTIRE table as well as allocating memory for every recordset. If the table has many fields and many records this can be very slow and memory intensive.
If all you need are the votes you can use...
...and then add up each votes record element through PHP. But even then the database server will be doing far more work than if you just use SUM.
Code: Select all
SELECT SUM(votes) FROM tableCode: Select all
SELECT * FROM tableIf all you need are the votes you can use...
Code: Select all
SELECT votes FROM table