What is the most performant method to run many UPDATE statements?

Question

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

Answer 1

UPDATE statements alter data and are therefore by definition not fast. They might be relatively fast if you a small data set but the bigger you table gets, the slower an UPDATE will become, especially if it involves updating indexes and stuff.

So I doubt it would matter much if you execute them separately or in a single call. Instead, I would look into ways to "optimize" them in a more general way:

• Maybe you can rewrite it so that you need to execute less queries, e.g. use a subquery to get the value. Make sure to actually profile it to be sure that it is faster. A complex query could also be slower than a number of simple ones.

• Use db_transaction() to execute all queries in a single transaction. Not only will this enforce consistency (all or nothing, if you have an error in the 500. query the others changes will be reverted as well) it will also be considerably faster on many systems because (in the default configuration) MYSQL enforces a write to the file system after each transaction (and if you don't use transactions explicitly, every UPDATE/INSERT/DELETE query is considered a separate transaction).

• Maybe your table structure can be optimized so that you need less queries.

• If there is no way around executing hundreds of UPDATE queries, I would go for reliable instead of performant/fast. Have a look at how node access table rebuilds are done for example ( which can result in hundreds of thousands of INSERTS if you have many nodes). When one is necessary (e.g. a node access module is installed), it usually just sets a flag to inform the admin and then the actual rebuild is executed in the backend with a batch.

If you provide more details on what you are actually trying to do, I might be able to give you some actual advice as well.

EDIT:

Some more hints now that you posted what you are doing exactly.

• If you don't need to keep old data, you could try if you can first calculate the metrics for a number of nodes, then delete them and then do a multi-insert. Maybe that's faster, maybe not.

• Generally, I would implement what you're doing in a hook_cron(). You could process N nodes per cron run, store the highest node id and on the next run, process the next N. If a query returns less than the requested N, you reached the end of your data set and are done for the day, set a flag to today and can start by zero at the next day. If you need to process all nodes at the same time, I'd create a simple drush script that you can start through system cron, but still group the processing into groups of nodes, to avoid memory issues.

• You could look into using a different backend for storing your metrics if you're on your own server and can install something. For example, there is http://oss.oetiker.ch/rrdtool/, which is specifically built for storing "time series data" (quote from the website). I have never used it though. And if you're in for big, then there are systems like Hadoop which is among others used by Facebook to process their enormous amount of daily metrics.