Markus Winand's Blog

Archive for the ‘Performance’ Category

Choosing NoSQL For The Right Reason

In Performance, Reliability, Scalability on 2011-05-13 at 09:42

Observing the NoSQL hype through the eyes of an SQL performance consultant is an interesting experience. It is, however, very hard to write about NoSQL because there are so many forms of it. After all, NoSQL is nothing more than a marketing term. A marketing term that works pretty well because it goes to the heart of many developers that struggle with SQL every day.

Read the rest of this entry »

Finding the Best Match With a Top-N Query

In Performance on 2010-09-29 at 11:16

There was an interesting index related performance problem on Stack Overflow recently. The problem was to check an input string against a table that holds about 2000 prefix patterns (e.g., LIKE 'xyz%'). A fast select is needed that returns one row if any pattern matches the input string, or no row otherwise.

I believe my solution is worth a few extra words to explain it in more detail. Even though it’s a perfect fit for Use The Index, Luke it’s a little early to put it as an exercise there. It is, however, a very good complement to my previous article Analytic Top-N queries—so I put it here.

Although the problem was raised for a MySQL database, my solution applies to all databases that can properly optimize Top-N queries.

Read the rest of this entry »

Use The Index, Luke!

In Performance on 2010-08-15 at 19:17

Today, I’d like to introduce my new Web-Book Use The Index, Luke. Use The Index, Luke is a guide to Oracle database performance for developers.

Use The Index, Luke! A Guide to SQL Performance for Developers


I started the book because I noticed that almost every existing book or online document on that topic is stuffed with plenty of information that is not relevant to developers.

Read the rest of this entry »

Analytic Top-N Queries

In Maintainability, Performance on 2010-07-30 at 10:55

One of the more advanced tricks I like to exploit are analytic Top-N queries. Although I am using them for quite a while, I recently discovered a “limitation” that I was not aware of. Actually—to be honest—it’s not a limitation; it is a missing optimization in a rarely used feature that can easily worked around. I must admit that I ask for quite a lot in that case.

The article starts with a general introduction into Top-N queries, applies that technique to analytic queries and explains the case where I miss an optimization. But is is really worth all that efforts? The article concludes with my answer to that question.

Read the rest of this entry »

About Software Quality

In Maintainability, Performance, Reliability, Scalability on 2010-06-12 at 13:00

You might have noticed that this blog never had a kickoff post that explains what this blog is about. Time has come to spend some words on the topic of software quality—as I see it—and how this blog covers some aspects of software quality.

Read the rest of this entry »

Analyze That

In Performance on 2010-04-30 at 17:03

As Jonathan Lewis commented on my article Clustering Factor: Row Migrtion’s Victim, there is even more to say about the difference between the good, old, and deprecated ANALYZE statement and the DBMS_STATS package. Jonathan mentioned that the CBO is using the CHAIN_CNT value in the statistics, if present, and suggested to try my “trapQL” after analyzing the base table in the old fashion.

Read the rest of this entry »

Clustering Factor: Row Migration’s Victim

In Performance on 2010-03-09 at 10:15

This article describes the effects of a high row migration rate on the clustering factor and the optimizer’s ability to select the best execution plan.

In my previous article—Row Migration and Row Movement—I have demonstrated that the “insert empty, update everything” anti-pattern can lead to 100% row migration. This article continues the research on row migration and unveils surprising effects on the clustering factor. To be precise, the clustering factor can become completely bogus in presence of a very high row migration rate. Once the clustering factor is “wrong”, it’s just a finger exercise to construct an optimizer trap and proof that row migration can affect the query plan.

Read the rest of this entry »

Row Migration and Row Movement

In Performance on 2010-02-23 at 10:52

The Oracle database knows three distinct processes that are easily mixed up: Row Chaining, Row Migration and Row Movement.

Luckily all three are well described in excellent articles: The Secrets of Oracle Row Chaining and Migration and Row Movement in Oracle.

For the impatient, I provide some very short definitions:

Row Chaining
Distribution of a single table row across multiple data blocks.
Row Migration
Relocation of an entire table row to a new place, without updating the indexes.
Row Movement
Relocation of an entire table row to a new place and updating the indexes.

This article was inspired by the question if Oracle 11r2 performs Row Movement instead of Row Migration for ordinary UPDATE statements—that is, in absence of partitions. The short answer is: no, it doesn’t. The long answer is the rest of this article.

Read the rest of this entry »

Latency: Security vs. Performance

In Performance on 2009-12-22 at 08:01

I have witnessed a very short talk between a network engineer and a top-level manager at a client’s Christmas party. The network guy explained that the firewall adds about 0.2 milliseconds latency to each round trip between the application server and the database, which adds up to some hours in one particular case. So the manager asked what could be done and the network guy provided two solutions:

  • Change the application to make less round trips
  • Accept the security risk and don’t put a firewall in between those two tiers

Funny enough the network guy explained that the second option needs the managers signature because it bypasses the corporate security guidelines and somebody must take the responsibility for that.

Consider you are the manager, would you sign that a paper?

Read the rest of this entry »