LIMIT versus sqlite3_step LIMIT times

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

LIMIT versus sqlite3_step LIMIT times

curmudgeon
If a query is sorted on an index is there any advantage to including LIMIT in the stmt as opposed to omitting it and stepping through the result set LIMIT times?

_______________________________________________
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: LIMIT versus sqlite3_step LIMIT times

Cezary H. Noweta
Hello,

On 2018-02-16 11:18, x wrote:
> If a query is sorted on an index is there any advantage to including LIMIT in the stmt as opposed to omitting it and stepping through the result set LIMIT times?

No -- LIMIT appends an additional opcode to check the number of rows and
introduces an effort related to an additional parsing. In general, LIMIT
can reduce a cost of a full external sorting, however this does not
apply to your query.

BTW, in file ``src/select.c'' line 2377:

Expr *pLimit;    /* Saved values of p->nLimit  */

should be

Expr *pLimit;    /* Saved values of p->pLimit  */

-- best regards

Cezary H. Noweta
_______________________________________________
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: LIMIT versus sqlite3_step LIMIT times

Dominique Devienne
On Fri, Feb 16, 2018 at 12:33 PM, Cezary H. Noweta <[hidden email]>
wrote:

> On 2018-02-16 11:18, x wrote:
>
>> If a query is sorted on an index is there any advantage to including
>> LIMIT in the stmt as opposed to omitting it and stepping through the result
>> set LIMIT times?
>>
>
> No -- LIMIT appends an additional opcode to check the number of rows and
> introduces an effort related to an additional parsing. In general, LIMIT
> can reduce a cost of a full external sorting, however this does not apply
> to your query.
>

While you're technically right, I think of SQL as a declarative language,
and as such I'd say giving this information to SQLite is a best practice
IMHO.
Unlikely in this case, but perhaps one day SQLite might be able to optimize
"something" based on it. And in general, not all queries are that simple.
Giving the query planner all possible information should be recommended. My
$0.02. --DD
_______________________________________________
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: LIMIT versus sqlite3_step LIMIT times

curmudgeon
Thanks for the replies. For my purpose it was about avoiding the possibility of having to apply a limit to a query that might already have a limit clause.

From: Dominique Devienne<mailto:[hidden email]>
Sent: 16 February 2018 12:00
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 12:33 PM, Cezary H. Noweta <[hidden email]>
wrote:

> On 2018-02-16 11:18, x wrote:
>
>> If a query is sorted on an index is there any advantage to including
>> LIMIT in the stmt as opposed to omitting it and stepping through the result
>> set LIMIT times?
>>
>
> No -- LIMIT appends an additional opcode to check the number of rows and
> introduces an effort related to an additional parsing. In general, LIMIT
> can reduce a cost of a full external sorting, however this does not apply
> to your query.
>

While you're technically right, I think of SQL as a declarative language,
and as such I'd say giving this information to SQLite is a best practice
IMHO.
Unlikely in this case, but perhaps one day SQLite might be able to optimize
"something" based on it. And in general, not all queries are that simple.
Giving the query planner all possible information should be recommended. My
$0.02. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
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: LIMIT versus sqlite3_step LIMIT times

Dominique Devienne
On Fri, Feb 16, 2018 at 1:11 PM, x <[hidden email]> wrote:

> Thanks for the replies. For my purpose it was about avoiding the
> possibility of having to apply a limit to a query that might already have a
> limit clause.


Good point. I tried, and indeed that's an issue. I really really which
SQLite expose more meta-data about prepared statements :). --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table t (c);
sqlite> insert into t values (1), (2), ('three');
sqlite> select * from t;
1
2
three
sqlite> select * from t limit 1;
1
sqlite> select * from t limit 1 limit 2;
Error: near "limit": syntax error
sqlite>
_______________________________________________
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: LIMIT versus sqlite3_step LIMIT times

curmudgeon
I suppose ‘select * from (original select with limit clause) limit :lim’ gets round it.

From: Dominique Devienne<mailto:[hidden email]>
Sent: 16 February 2018 12:21
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 1:11 PM, x <[hidden email]> wrote:

> Thanks for the replies. For my purpose it was about avoiding the
> possibility of having to apply a limit to a query that might already have a
> limit clause.


Good point. I tried, and indeed that's an issue. I really really which
SQLite expose more meta-data about prepared statements :). --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table t (c);
sqlite> insert into t values (1), (2), ('three');
sqlite> select * from t;
1
2
three
sqlite> select * from t limit 1;
1
sqlite> select * from t limit 1 limit 2;
Error: near "limit": syntax error
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
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: LIMIT versus sqlite3_step LIMIT times

Cezary H. Noweta
In reply to this post by Dominique Devienne
Hello,

On 2018-02-16 13:00, Dominique Devienne wrote:
> While you're technically right, I think of SQL as a declarative language,
> and as such I'd say giving this information to SQLite is a best practice
> IMHO.
> Unlikely in this case, but perhaps one day SQLite might be able to optimize
> "something" based on it. And in general, not all queries are that simple.
> Giving the query planner all possible information should be recommended. My
> $0.02. --DD

Indeed, you are absolutely right -- in addition, I have made a silent
assumption that OP calls SQLite from C code using a simple construct
like ``for ( i = 0; nofrows > i; ++i ) ...step...'' -- there are
scenarios where SQLite VDBE's loop can be faster.

Ad. query planner -- I have mentioned that a cost of a full external
sorting for WHERE condition can be reduced by a LIMIT clause -- this
fact should be obviously considered.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users