CodeSOD: Select Start

"John Doe" was asked to take a look at a slow-running application. It didn't take too long to discover that the application was slow because the database was slow, but figuring out why the database was slow involved digging deeply through some logs.

The database was a central system, which many applications connected to. Every ten minutes, performance dropped significantly, and it just so happened that every ten minutes a batch update process ran.

public void doTheUpdate(int currentItemId, ...) throws Exception { Connection connection = null; PreparedStatement query = null; ResultSet rs = null; try { connection = getConnection( ConnectionFactory.DB_POOL_KEY ); query = connection.prepareStatement( "SELECT * FROM XXX WHERE id=?", currentItemId); rs = query.executeQuery(); if( rs.next() ) { // update query = connection.prepareStatement( "UPDATE XXX", ...); query.executeUpdate(); }else{ // create query = connection.prepareStatement( "INSERT INTO XXX VALUES (?,?,?,?, ...)", ...); query.executeUpdate(); } } catch(Exception e) { throw e; } finally { //close the connection }}

This method and the queries have been highly anonymized, and don't reflect all the details, but the logical flow is what matters.

The flow here is that it runs a select statement to see if a record exists. If it does, it updates the record. If it doesn't, it inserts a new record.

If you know nothing about the database or the application structure, that's not actually a terrible set of choices. It's not great, but approaching it with no knowledge of how the database gets accessed, it's pretty safe.

There's just one problem about this. Before this function runs, another function does DELETE FROM XXX, emptying the table of data completely. There are not (or at least shouldn't be) any duplicates in the input data, so inserts will always succeed.

Now, the batch updater wasn't the only thing hammering the database every ten minutes, but removing the extraneous SELECT and just going straight to an INSERT was enough of a performance boost to make sure that every running application was able to access the database with reasonable performance.

And the next time there's a performance problem, John has a pretty solid idea of what to look for.

[Advertisement] Keep the plebs out of prod. Restrict NuGet feed privileges with ProGet. Learn more.