Performance matters when writing, too: more SQL tips
SQL is a great domain for attention to performance, because it so often happens “in the wild” that a modestly altered coding boosts performance by an eye-popping factor.
It’s exciting to accelerate a query that takes a day to execute, into one that finishes in five seconds. Almost all the written examples of these speed-ups, though, are about queries, in the narrow sense of read-only reports. They have to do with retrieval of data. As it happens, writes to a database also often have great potential for improvement.
To understand SQL write performance, start with a little context. “Real User Monitor” only dabbles in SQL; database management is a huge subject, and this blog occasionally touches on little corners of it. Performance in typical modern applications comes from teamwork between at least a half-dozen different technologies, and one of the great lessons of modern monitoring and management is that the different technologies need to be kept in balance.
Still, SQL is a rewarding area to attack, because so many characteristic opportunities continue to turn up after nearly forty years of use. It’s easy, for instance, to look for ways to shift report computations from a host language on a client back to the database server.
The same trick often applies with SQL updates as well as retrievals. Although textbooks continue to emphasize optimization on the read side, at least two reasons make it timely to look at writes:
- There’s now widespread appreciation that indexes can improve performance of retrievals. The costs of indexes when writing are less well-understood by database beginners.
- In an era of Big Data and The Internet of Things, database content is exploding in unfamiliar and not-always-well-understood ways.
Is there a remedy for slow writes? Sure; consider, for example, a simple case where an application
inserts a record. As simple as that sounds, its performance is likely to be variable in many practical situations. The table to which the application is writing probably has one or more indexes, and perhaps a few triggers and other constraints as well. It can easily happen that some
inserts take a hundred or a thousand times as long as other apparently identical ones, as indexes overflow page boundaries or logs have to be flushed.
While a complete performance optimization is a hard problem, it’s often quick and safe to do a complementary re-factoring on the write side that so often helps with retrievals: batch writes in an application language to minimize the number of distinct
Insert isn’t limited to just a single record at a time; rather than iterate over entries on the application side, for instance, just send all the data to the database server in a single
insert, and give the database optimizer a proper chance to do its job.
Another cheap tranformation in a MySQL environment is to use
After these easy steps, there’s often still great potential for optimization of write operations, but at the cost of more careful analysis: removal of redundant indexes, minimization of transaction scopes, relocation of logfiles to separate spindles, adjustment of fill factor, and so on. Most important: keep in mind that modern applications are about as likely to deserve care with writing, as reading.