Speed and efficiency

Using code vs SQL statements

[Sandi_W]

Which is faster, totalling items by using SUM( ) and GROUP BY in the SQL statement or "manual totalling" using coding logic?

[jatar_k]

I would take a shot by saying the more you can get SQL to do the better. If you have to load all of the results from a DB and then run through them all again to get totals you are, in essence, doing the work twice.

[Mikael]

I'd agree that it's much better practice to get as much possible specified in the SQL. You might want to look at this article which has more on doing things in PHP vs SQL [zend.com ].

[Woz]

>totalling items by using SUM( ) and GROUP BY in the SQL

I tend to put complex calculation like this into the database itself and save it as a query, I am talking Access here, as it runs faster within the database rather than outside and is less sever intensive. Then I simply grab the totals via a simpler SQL statement. It may only be a small difference per se but as the number of page hits grows it can mean a considerable saving overall.

Onya
Woz

[ggrot]

Whenever you make a call to SQL, you have a large overhead regarding the amount of data that has to pass between your coding language and the database. If you can reduce this by only returning the fields you need or performing sums on the database side, that speeds things up alot.

All newer implementations of SQL use variations of B+ trees which have basically been proven to be the fastest algorithms for organization of large amounts of data. So basically, if SQL can do it with a query, you can't write PHP code to do it any faster(speaking in terms of big O, you might possibly be able to save a few clock cycles if you are really good, but nothing significant). In the best case, you'll waste alot of time studying algorithms to do it 'as good' as SQL does.