Opening Eyes on Database Query Performance

Today’s blog post is from a guest blogger, Curt Triplett. Curt is one of the founding partners at DBG Software and an IBM Champion. He has over 20 years of experience specializing in performance optimization across all major databases including DB2,  Oracle, SQL Server, and Teradata.  

Take it away Curt…

After working in over 40 companies now, there is either some amazing coincidence or the discipline of query performance is not truly “owned” by anyone. Yes, every company says they “have it covered” to some extent. Usually, this means the DBAs tune queries. But the reality is that the typical DBA simply does not have the time to tune queries every day, and therefore cannot possibly build the skill set required for such a discipline.

What do they say… repetition breeds proficiency? The point is you can’t possibly be as good at something you do 20 percent of the time that someone else does 100 percent of the time.

A day in the life of a DBA is immense. First and foremost (way before performance) is the availability and integrity of the database. Oh, and then there is the constant cycle of applying patches, migrating database objects between databases, creating new databases, answering dozens of questions from various sources, etc. etc. etc.

What ends up happening is performance takes a back seat. And when “performance” is looked at, it’s usually database performance that is the focus. Not query performance. Too often, a shotgun approach is taken to turn a big knob on the database to make all the queries run faster. Or maybe hardware is added. Did that fix anything? Of course not, but you can bet the queries will run faster. For now.

Because there is a mentality to want to kill many birds with one stone by tuning the database, we never fix the true problem. The queries. So, the common cycle of making changes at the database level, partitioning, buying hardware, etc. is prescribed instead of doing what’s right… tuning the queries.

I propose that we quit thinking of ways to take broad strokes at “fixing” query performance and start making broad strokes at “diagnosing” what queries to fix! Whether it’s fixing the anomalies that bubble to the top of the averages or the individual elapse times, the idea is to determine which queries bring the greatest return.

So, let’s trade in the habit of taking the shotgun approach of tuning with tactics like partitioning and disk array tactics intended to evenly distribute I/O. Why distribute I/O? Eliminate it. That sounds far more efficient to me. Eliminate the I/O by tuning at the level that’s needed, the query level.

Having said all that, it takes a unique process and discipline to help identify what queries will bring the greatest return. This discipline will need two things. The right product and the right skill set.

So, share with me any thoughts and think about the following:

  1. How is query performance addressed in your shop?
  2. Is it effective?
  3. How do you know it’s effective?
  4. Could there be a possibility of a different, maybe even better, methodology that exists to make your query performance discipline a world class service?

I have assumptions about the answers to the first three questions, but I know the answer to the last one for sure. The answer is “Yes” and it’s called DPOP. DPOP is the methodology I’ve created for addressing query performance and you’ll quickly see how it applies to any database (DB2, Oracle, SQL Server) and for any application (developed or purchased).

Investigate how DPOP can help your organization achieve improved query performance at http://www.softwareonz.com/page31.html

 

Advertisements

About craig@craigsmullins.com

I'm a strategist, researcher, and consultant with nearly three decades of experience in all facets of database systems development.
This entry was posted in DBA. Bookmark the permalink.

3 Responses to Opening Eyes on Database Query Performance

  1. Luis Chavayda says:

    Attractive section of content. I just stumbled upon your web site and in accession capital to assert that I get in fact enjoyed account your blog posts. Anyway I’ll be subscribing to your feeds and even I achievement you access consistently fast.

  2. Mapa Tu says:

    Whoa, this blog is fantastic, i love reading your articles. Keep up the good work! You know, many people are hunting around for this info, you can aid them greatly.

  3. Madelyn says:

    Hi, I read your new stuff daily. Your writing style is witty,
    keep up the good work!

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