select start

”john doe“I was asked to look into an application that was running slowly. It didn’t take me long to discover that the application was slow because the database was slow, but to figure out why the database was slow, I had to do a few things. I had to look deep into the logs.

The database is a central system; many Applications connected to. Every 10 minutes the performance degraded significantly and coincidentally the batch update process was running every 10 minutes.

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() ) 
                    
                    query = connection.prepareStatement( "UPDATE XXX", …);
                    query.executeUpdate();
            else
                    
                    query = connection.prepareStatement( "INSERT INTO XXX VALUES (?,?,?,?, …)", …);
                    query.executeUpdate();
                    
            
    
    catch(Exception e) 
            throw e;
    
    finally 
            
    

This method and query is highly anonymous and does not reflect all details, but the logical flow is important.

The flow here is to run a select statement and check if the record exists. If there is a match, the record will be updated. Otherwise, a new record is inserted.

Even if you don’t know anything about database or application structure, it’s actually not such a terrible option.it’s not wonderfulHowever, it is fairly safe to approach it without knowing anything about how the database is accessed.

There’s just one problem with this. Another function executes before this function executes. DELETE FROM XXX, completely empty the data table.There are no duplicates in the input data (or at least there shouldn’t be any), so the insert is everytime success.

Now, the batch updater is only Removes irrelevant data while stressing the database every 10 minutes SELECT and just go straight INSERT This was a sufficient performance increase to allow all running applications to access the database with reasonable performance.

And the next time a performance problem occurs, John has a pretty solid idea of ​​what to look for.