2012-01-13Partial Results

It is not always required to fetch the entire result of an SQL query—the first few rows are sometimes enough. If you need to show the latest news only, for example. Most applications allow fetching more results in that case. Either via paging or the more modern “infinite scrolling” variant. But the respective SQL queries can easily cause performance problems, if they need to sort all news items every time to find the most recent ones. Pipelined order by is therefore very useful for these queries.

But using a pipelined order by is not only about saving the sorting efforts. It is more important that it does not need to read and sort the entire result set before delivering the first rows. That means, the pipelined order by has very low startup costs. It is therefore possible to abort the execution after fetching a few rows without discarding the efforts to prepare the final result.

This chapter demonstrates using pipelined order by execution to retrieve partial results efficiently. The syntax provided by the various databases is rather inconsistent, still they are executing the queries in a very similar way. This once again illustrates that they all put their pants on one leg at a time.