Blog

Todays installment finalizes the Chapter on partial results and shows how to use SQL:2003 window-Functions for an offset-based pagination query in SQL Server and Oracle.

Continue Reading

Todays installment continues the Chapter about efficiently fetching partial results—that is, whenever you don’t need all the rows. Todays part explains how to fetch the next page efficiently.

Continue Reading

Todays installment opens the Chapter about efficiently fetching partial results—that is, whenever you don’t need all the rows, but just a few from the beginning. These kind of queries are every often used to show lists in a page wise manner.

The installment consists of two parts: the chapter intro and the first section about the Top-N queries.

Continue Reading

Todays update delivers the last part of the Chapter on sorting and grouping. It is a short demonstration how to use indexing for a pipelined group by.

Pipelined order by and group by are the prerequisite for the optimization methods described in the next chapter on partial results.

Besides that, you should definitely have a look a my new website.

My New Website

Read Group By Article

Todays update explains how to control the index order so that queries using ASC/DESC or NULLS FIRST/LAST specifications can also be executed as piplined order by.

Continue Reading

Todays installment is about the third power of indexing: satisfying order by clauses by an index—thus, preventing the need to sort the data.

The installment consists of two parts: the chapter intro and the first section about the interactions between the order by and the where clause.

Continue Reading

The previous installment explained the index-only scan to prevent the table access after an index lookup.

Todays update extends the idea and elaborates on the question if the separate table structure is required at all, if the index covers all table columns.

Continue Reading

The previous installment demonstrated how to use an index to cluster table rows together. The examples showed that even “anywhere” LIKE expressions can be tuned when the column is put into the index to avoid the table access.

Todays installment extends this concept and shows queries that don’t need to access the table at all. It’s about the so-called index-only scan, or, less descriptive, covering index.

Continue Reading

Today’s installment covers the first two parts from Chapter 5, „Clustering Data“. It explains the “cluster” term and demonstrates the impact of clustering data with a simple example.

This will be the basis for the next installment, which explains why adding another where clause may slowdown statement execution. Not just a little bit, but by factors. That is actually the question that has the highest failure rate at the 3 minute test.

Continue Reading

Use The Index, Luke! was one today.

There was quite some progress during this year. Besides the steadily growing content, I am particularly happy that the appendices about execution plans and the example schema cover MySQL, PostgreSQL and SQL Server in addition to Oracle.

But what’s next?

The content will be completed until early 2012 (final outline in the Preface). The second volume of SQL Performance Explained—the e-book edition—will be published at that time as well. Additionally, Use The Index, Luke! will be translated gradually into German. There is, however, no fixed schedule for that, but you can already subscribe the German blog.

In the German-speaking area, I will also hold my "SQL Performance Basics Workshop for Developers". Individual online workshops are available worldwide in German or English language.

Finally, I have prepared something for the first anniversary of Use The Index, Luke. How good do you know SQL-Performance? I hope you had the feeling of success when you applied your experience from the book to your real-world problems. Did you actually blog or tweet about it? Let me know. However, you can also take the new 3-minute test to see how good you are. Although the test takes just three minutes, it’s not simple. Some of the questions are not yet covered by the book — don’t be surprised if you don’t get the perfect score. Short explanations are given at the end, longer ones will come in the next few month on Use The Index, Luke!

Take the 3-minute test

Workshop