Single statements better for performance? 2

[LaundroMat]

Hi,

I'm using MySQL with PHP, and I was wondering what would be the better way of doing things performance-wise.

Sometimes I need data (some of it grouped, such as SUMs) from 5 columns in one time. Would it be better to execute one complex SQL query (with a lot of JOINs, SUMs and what not) or would performance be less hampered by using single statements?

 

[TonyGroves]

It's hard to tell. The best way to find out is by trying both methods several times.

However, when using more than one statement, you run the risk of inconsistency; the database could get updated in the meantime. This can be avoided by using table-locking, but that's not foolproof, as you could forget to unlock the tables.

 

[LaundroMat]

Thanks for that. I think I'll stick to more complex queries then.

 

[MichaelRed]

In a general sense, the dbengine(s) are optimized to deal with the complexities of the dbstructures. The better engines return ONLY the desired data, doing any filtering necesary based on the Joins, Where, Having, In, etc clauses. This -generally- improves the overall performance of the entire system (particularly over networks). Exceptions can (an do) occur when the burden on the dbengine causes other operations to wait or to only get small slices of the processor.




MichaelRed