Database Performance Anti-Patterns

Databases are 'slow' for a number of reasons, most of which are predictable.  The most common one being the database design itself.  Next is the data access methodologies used by the developers.  And the SQL queries.  Followed by many, many more ...  

 

Please be sure to always test your applications using large sets of data.  And keep in mind ... a bad SQL statement can bring the most powerful hardware to it's knees.

Sometimes the design pattern being followed ... actually has a negative impact

Why is our database slow?

There can be many reasons, but usually it's as simple as it wasn't designed and tested to keep running fast.  

 

Nearly any design or coding technique works fast with a small quantity of data. But larger quantities of data cause the database engine to behave differently. Often quite slowly.

This is easy to prevent at the beginning of the design cycle, but much, much harder once the solution is placed into production.

Common performance anti-patterns

Entity-Attribute-Value design.  This web and ERP-centric design saves time at the expense of performance and data-integrity.

Code-first database design.  

 

Lack of understanding of how queries are executed by developers.  This is far too common these days.

Solutions ported from mainframes.  Without being re-designed for modern database technologies.

Quick

Quiz

How complex is the SQL statement to return a 'complete' record of your most common business transaction?