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


Update Performance

Todays installment covers update performance. As slow as insert and delete together, but sometimes still faster.

Delete Performance

Todays installment covers delete performance. It is similar, yet different, than insert performance covered last week.

DML Performance

Todays installment opens the—for the time being—last chapter in Use The Index, Luke. After spending a year and a half writing about the benefits of proper indexing, I have, of course, also tell you about the dark side of indexes. That is insert, update and delete performance.

After the very short chapter intro, todays installment shows you the indexing impact on insert statement.

Window-Functions for Pagination

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.

Paging through Results

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.

Partial Results

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.

Indexing Group By

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.

Indexing ASC/DESC and NULLS FIRST/LAST

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.

Pipelined ORDER BY: The Third Power

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.

Clustered Indexes and Index-Organized Tables

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.

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/