Use The Index, Luke Blog - Latest News About SQL Performance


On Tour

I have just finalized the tour schedule for my "SQL Performance Basics Workshop for Developers". The most important tips and tricks every developer must know—in just 3 hours. It takes place in your office because every developer—the entire team—should take part.

The tour covers Austria, Germany and Switzerland. Just let me know if there isn't a scheduled date for your area. I'm sure we can arrange something. Small teams can also take the virtual workshop.

Hope to see you soon.

Production Load

"But it worked in test environment" is a statement I hear often when a production update causes performance problems. It turns out that the problem did exist in test environment as well—but at a different scale.

Today's installment shows the impact of background load on SQL execution speed. That's the factor where even the best test environments diverge from production because realistic load is rarely available. After all, a full stress test is hardly doable for developer. So, we have to pay more attention to the observations during development.

P.S.
Something went wrong with an update last week. That caused old articles to re-appear as unread. There is, unfortunately, nobody else to blame than myself. So, sorry for the confusion and thanks to all of you who told me about the problem.

Identifying Index Filter Predicates

The last article demonstrated the impact of index filter predicates. They give a false sense of safety because the execution plan looks fine on the first sight. However, a closer look pays off sooner or later.

Although the scalability tests were done on three different SQL databases, there was one important point missing: How to identify index filter predicates?

Todays installment fills the gap and explains how index filter predicates look like—in different databases:

Oracle
Index filter predicates are shown in the textual explain plan and Oracle SQL Developer.
PostgreSQL
PostgreSQL does not show index filter predicates in execution plans. However, the explain plan shows table level filter predicates that look like index filter predicates on the first sight.
SQL Server
Although well hidden, Microsoft SQL Server shows index filter predicates in the graphical execution plan and in the textual/tabular form. However, SQL Server uses different names.

Todays installment is a little bit spread. So, choose your database to find the relevant part.

PS: Need Help Right Now? Check out my Instant-Coaching offer.

Testing and Scalability

Todays installment of "Use The Index, Luke" (aka. SQL Performance Explained) opens a new chapter: "Testing and Scalability".

The chapter demonstrates the effects of proper indexing for scalability and tries to answer the question if having a close look at indexing is worth the effort. In other words: "Is it really worth reading an entire book about SQL indexing?"

I guess you know my answer.

The first section puts the focus on the data volume. It is, however, not discussing that a FULL TABLE SCAN can be fast on a tiny table. It's about a sub-optimal index and its impact on scalability.

PS: Need Help Right Now? Check out my Instant-Coaching offer.

SQL Performance Explained - e-book download

There is a download of "Use The Index, Luke"—finally. However, it got its own name to attract a wider audience. That's SQL Performance Explained.

It's a DRM free ePUB download.

It is, however, not possible to update the ePUB in the same frequency as the Web-Edition. It's therefore released in two parts. "Volume 1—Basic Indexing" was just put online and includes the first two Chapters—that is, everything which is online as of today. "Volume 2—Advanced Indexing" will be released after the rest of the book has been published on the web. That's probably 2012.

It's your chance to catch up if you missed the beginning. Just download to your e-book reader or smartphone and read wherever you like. The FAQ has some hints how to do it.

PS.: Yes, it's really the same as "Use The Index, Luke". Just another name.

Myth: Dynamic SQL is Slow

Here is an update of the Myth Directory: Dynamic SQL is Slow. It complements the Smart Logic Obfuscation from the previous installment.

It was actually intended as "small" update—but that didn't work out. It is now fully blown, including ready to run code samples in Java (Hibernate and JPA), PHP (Doctrine) and Perl (DBIx::Class).

PS: Need Help Right Now? Check out my Instant-Coaching offer.

Obfuscated Conditions

The previous installments of Use The Index, Luke explained all you need to know about writing efficient where clauses and creating effective indexes to support them.

Todays installment completes the chapter about the where clause. That means that the book is about half-way done.

I've had a very interesting and enjoyable time writing the guide—but it was also very exhausting. That's why "Luke" needs a little rest.

The next regular installment will be posted February 2011.

However, that's the schedule for the next regular installment—I am planning some specials for the holiday season. Stay tuned.

Today's installment is about "Obfuscated Conditions". That are anti-patterns that kill performance—again and again. There's a little new exercise in the Q&A forum as well.

PS: Need Help Right Now? Check out my Instant-Coaching offer.

LIKE and Index Combine

The previous installment of Use The Index, Luke revisited the importance of the index column order in context of range conditions and explained the difference between access and filter predicates.

Today's installment covers the SQL LIKE operator and explains the related performance problems with access and filter predicates.

The second part of today installment focuses on one of the most frequent questions about indexing: "Why not using one separate index for each column?".

PS: Need Help Right Now? Check out my Instant-Coaching offer.

Searching for Ranges

The previous installment of Use The Index, Luke explained how to index NULL in the Oracle database.

Today's installment is the first to discuss the performance implications of range conditions such as less (<), greater (>) and BETWEEN.

The second and final part is due in late November and will cover SQLs LIKE operator as well as the hardly tunable multi-column range search.

PS: Need Help Right Now? Check out my Instant-Coaching offer.

Indexing NULL

The previous installment of Use The Index, Luke discussed the use of bind parameters in SQL to prevent SQL injection and reduce parsing overhead.

Today's installment explains the Oracle databases handling of NULL in indexes and explains how to improve performance for "IS NULL" queries.

PS: Need Help Right Now? Check out my Instant-Coaching offer.

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/