SQL Database Tuning for Developers

Visual Studio

A good Database Administrator (DBA) can create and maintain a great database on an efficient server. The problem is other people use the database, like developers that are making changes so that they can write programs to access that perfect database. As those changes are made, the database can become less efficient if the proper techniques aren’t used by the development team. In this article by Rodrigo Koch, he helps us understand the techniques developers should use.

Indexes

If you’re a complete newcomer to databases, you should know that indexing is an effective way to tune your database that is often neglected during development. In basic terms, an index is a data structure that improves the speed of data retrieval operations on a database table by providing rapid random lookups and efficient access of ordered records. This means that once you’ve created an index, you can select or sort your rows faster than before.

Indexes are also used to define a primary-key or unique index which will guarantee that no other columns have the same values. Of course, indexing is a vast an interesting topic to which I can’t do justice with this brief description.

If you’re new to indexes, I recommend using this diagram when structuring your queries: 

Basically, the goal is to index the major searching and ordering columns.

Note that if your tables are constantly hammered by INSERTUPDATE, and DELETE, you should be careful when indexing—you could end up decreasing performance as all indexes need to be modified after these operations.

Further, DBAs often drop their indexes before performing million+ row batch inserts to speed up the insertion process. After the batch is inserted, they then recreate the indexes. Remember, however, that dropping indexes will affect every query running in that table; so this approach is only recommended when working with a single, large insertion.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s