Category Archives: Database Tuning

A physical filing cabinet

SQL Is More than Just Store and Retrieve

picture by shinichi via Flickr

I often work with programmers to build applications. It never ceases to amaze me that many programmers look at SQL as a store and retrieve mechanism and see nothing more. Their approach is to write programming code for all their logic. It’s not just a matter of programmers being unable to write complex SQL. It’s also a matter of them not imagining what is possible.

Continue reading SQL Is More than Just Store and Retrieve

Please Index Foreign Keys

CeramicHierarchy

picture by Travis via Flickr

In the database literature, the lore has been to first design a schema and then predict the query mix to decide on a tuning strategy. This approach can work, but it is risky and error prone. A small misjudgment of the query mix can lead to vastly inferior performance. For many applications a better strategy is to index every foreign key. That is often sufficient and further tuning is unnecessary.

Continue reading Please Index Foreign Keys

Ten Guidelines For Database Performance

DTM Zandvoort

picture by Rob Blank via Flickr

That’s what we often tell our clients. If you build a database properly, performance will be fast for 80% of applications. The remaining 20% are outside the scope of this article – they have complex issues such as large schema, massive data, multi-user contention, and intricate logic.

Here are ten guidelines that developers can use to obtain good database performance. The guidelines apply to the day-to-day operational systems that run on a relational database.

Continue reading Ten Guidelines For Database Performance