What is the most performant method and best practices method to run many UPDATE statements efficiently? Currently I'm just creating a text block of many UPDATE statements separated with semicolons and passing that into a single db_query() call.
Using this method I'm currently getting this error, so I'm guessing it's not a smart way:
PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.:
Is there a better way that is as fast? I will need to run possibly hundreds of UPDATE statements.
Why must I do so many queries? For every node, I track three metrics of that node. These metrics are determined form other sources programmatically (like traffic data). For speed, I created a table that just tracks the nid, and the values of the three metrics. On a regular basis, I have to update the metrics for every node in this table. Every node in the system will have a row in this table.
I determine a forth metric from the values of the three, so each day I have to:
- Update the values of metrics 1-3
- Update metric 4 based on values of metrics 1-3