Page 1 of 1

SQL SUM question

Posted: Tue Oct 31, 2006 9:12 am
by tbrown1
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?

Posted: Tue Oct 31, 2006 9:15 am
by feyd
It puts a bit of load on the database, but that is offset by it only needing to return a smaller result set to you. It's more work to sum it yourself.

Posted: Tue Oct 31, 2006 9:15 am
by tbrown1
Ok thankyou that's what my opinion was but I needed someone to confirm it for me

Posted: Tue Oct 31, 2006 1:51 pm
by RobertGonzalez
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?

Posted: Tue Oct 31, 2006 2:28 pm
by tbrown1
Good point

Posted: Wed Nov 01, 2006 11:30 am
by AKA Panama Jack
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.

Code: Select all

SELECT SUM(votes) FROM 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.

Code: Select all

SELECT * FROM table
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...

Code: Select all

SELECT votes FROM table
...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.