CODEWITHSUNDEEP
mysqloptimization
willthiswork89
willthiswork89

Reputation: 617

What is the proper way to do multiple UPDATE statements?

I have a server which sends up to 20 UPDATE statements to a separate MySQL server every 3-5 seconds for a game. My question is, is it faster to concat them together(UPDATE;UPDATE;UPDATE). Is it faster to do them in a transaction then commit the transaction? Is it faster to just do each UPDATE individually?

Any insight would be appreciated!

Upvotes: 1

Views: 113

Answers (2)

mvp
mvp

Reputation: 116447

It is always faster to wrap these UPDATEs into single transaction block. Price for this is that if anything fails inside that block it would be that nothing happened at all - you will have to repeat your work again.

Aslo, keep in mind that transactions in MySQL only work when using InnoDB engine.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270775

It sort of depends on how the server connects. If the connection between the servers is persistent, you probably won't see a great deal of difference between concatenated statements or multiple separate statements.

However, if the execution involves establishing the connection, executing the SQL statement, then tearing down the connection, you will save a lot of resources on the database server by executing multiple statements at a time. The process of establishing the connection tends to be an expensive and time-consuming one, and has the added overhead of DNS resolution since the machines are separate.

It makes the most logical sense to me to establish the connection, begin a transaction, execute the statements individually, commit the transaction and disconnect from the database server. Whether you send all the UPDATE statements as a single concatenation or multiple individual statements is probably not going to make a big difference in this scenario, especially if this just involves regular communication between these two servers and you need not expect it to scale up with user load, for example.

The use of the transaction assumes that your 3-5 second periodic bursts of UPDATE statements are logically related somehow. If they are not interdependent, then you could skip the transaction saving some resources.

As with any question regarding performance, the best answer is if your current system is meeting your performance and scaling needs, you ought not pay too much attention to micro-optimizing it just yet.

Upvotes: 1

Related Questions