Partial 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.

Faster, easier and more memorable than reading.

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.

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
277
views

Updating multiple rows using a subquery in SQL

Jan 08 at 09:52 Jan 26
subquery update sql
1
vote
1
answer
210
views

Should 'id' (the primary key) be included in an index

Jan 03 at 15:24 Jan 26
index include
0
votes
1
answer
227
views

Best index for a multiple join-tables and filter

Jan 03 at 14:31 Markus Winand ♦♦ 216
index join where