The Stifle

From CloudScale
Jump to: navigation, search

Contents

The Stifle

Example

A nightly batch process accessing account transfer tables to backup the transactions executed while the working day. For every single transaction copied the backup date has to be stored in a dedicated column in the transaction table.

Context

Systems adding and editing a lot of data in a relational database. Often in batch processing activities.

Problem

"The Stifle anti-pattern results from an improper use of the database interface. For instance, performing single update or insert statements into the same database table in a loop instead of doing it in a batch results in an unnecessarily high amount of database requests and lower potential for the database to optimize query execution. Not making proper use of the power of WHERE clauses in database queries may result in an unnecessarily high amount of database requests, too."[1]

The following code snippet is executed at the end of each successful backup run. If this is the case, every transaction, which was added during the last working day, is copied to a separate table. It adds the backupdate to every data element.

 newTransactions = getNewTransactions()
 FOREACH(id IN newTransactions) {
      newDate = CURRENTDATE
      EXECUTE STATEMENT("UPDATE transaction SET backupdate=<newDate> WHERE id=<id>")
 }

In this case a single request is sent to the database for every single update in the transaction table which was backuped.

Solution

Change the logic of the application in a way there is only one database request necessary. This gives the database the chance to execute the updates efficiently and it avoids sending numerous requests to the database. Subsequently a better code example is present

 newDate = CURRENTDATE
 EXECUTE STATEMENT ("UPDATE transaction SET backupdate=DATE WHERE createDate=<newDate>") 

Detection

High amount of database requests with very similar statments while only one users is on the system. Often only the values from the WHERE clause are different.

Variants

Unkown

Consequences

The anti-pattern potentially causes excessive message traffic and a high load at the database.

References

  1. Alexander Wert, Marius Oehler, Christoph Heger, and Roozbeh Farahbod. Automatic Detection of Performance Anti-patterns in Inter-component Communications. In Proceedings of the 10th International Conference on Quality of Software Architecture, Lille, France, 2014, QoSA '14.