Is there ever a perf advantage to LIMIT outside of a subquery?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Is there ever a perf advantage to LIMIT outside of a subquery?

deon
e.g. If you do:

SELECT c1 from t1 ORDER BY c2 LIMIT 5;

vs. just running the query without the "LIMIT" clause and taking the top 5 rows programmatically?


Obviously in the case of a subquery this makes a difference:
select count(*) from (select * from t1 limit 5);


But would it ever in a straight query with joins etc.?

- Deon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is there ever a perf advantage to LIMIT outside of a subquery?

Simon Slavin-3
On 11 May 2018, at 9:50pm, Deon Brewis <[hidden email]> wrote:

> e.g. If you do:
>
> SELECT c1 from t1 ORDER BY c2 LIMIT 5;
>
> vs. just running the query without the "LIMIT" clause and taking the top 5 rows programmatically?

The LIMIT clause just tells sqlite3_step() to return SQLITE_DONE after the fifth result.  The only difference is whether you want to count to 5 in your own code or have SQLite do it for you.

In practise, you may have a specific reason for wanting the limit to appear in the SQL command, or not wanting it there.  It may be part of your business practise or it may be a detail of how your software works. Do whatever the reader would find easiest to understand.

Remember in both cases to do sqlite3_finalize() or sqlite3_reset() once you have finished fetching results.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is there ever a perf advantage to LIMIT outside of a subquery?

Richard Hipp-3
In reply to this post by deon
On 5/11/18, Deon Brewis <[hidden email]> wrote:
> e.g. If you do:
>
> SELECT c1 from t1 ORDER BY c2 LIMIT 5;
>
> vs. just running the query without the "LIMIT" clause and taking the top 5
> rows programmatically?

Yes, if there is an ORDER BY that cannot be satisfied by an index and
the total number of rows in the output is large relative to the LIMIT.
This is especially in 3.24.0 and later.

Without the LIMIT, all the terms of the original query must be
computed then sorted.  This can require a lot of storage.  With the
LIMIT, only the top N results must be stored.  And beginning with
3.24.0, if it is clear that a particular row will never make the LIMIT
cutoff, then columns that are not part of the ORDER BY clause are
never computed in the first place.  This latter optimization can be a
big win if the non-ORDER BY terms involve expensive functions and/or
correlated subqueries.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users