Re: Fewer large queries vs lots of small. performance?
Quote
UPDATE SET column_1=1 , column_2=1, column_n=1 WHERE id=1;
Setting multiple fields in one statement is
far quicker than setting them one at a time.
Remember; "
You must think in Russian .. er ..
Sets"; find a set of rows to work with, then process each of those rows.
For every update
statement you issue, MySQL has to find the row(s) you want to update and then update them. If it can perform lots of operations on each row once it's found it? Quicker.
Quote
When and how often do I open and close a connection
Opening connections
can be expensive, although connection cacheing helps a lot. For "heavy traffic" applications like this, I'd suggest keeping the connection open as long as the program is running.
Quote
How many (SQL) commands do I put in a string ...
"
There can be only one".
If your client supports them, Parameterised Queries can improve performance by simplifying the query parsing at the database end.
Regards, Phill W.
(I seem to be having a bit of a Movie Quote-fest today).