Output of pragma optimize?

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

Output of pragma optimize?

Bart Smissaert
Looking at the results of sqlite3_column_count (1) and also
sqlite3_column_name (optimize) it
seems that pragma optimize can have an output. What would that output be
and what would be a way to show such ouput?
Using the latest 3.20.1

RBS
_______________________________________________
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: Output of pragma optimize?

Clemens Ladisch
Bart Smissaert wrote:
> Looking at the results of sqlite3_column_count (1) and also
> sqlite3_column_name (optimize) it
> seems that pragma optimize can have an output. What would that output be
> and what would be a way to show such ouput?

<http://www.sqlite.org/pragma.html#pragma_optimize> says:
| The optional MASK argument is a bitmask of optimizations to perform:
|
| 1. Debugging mode. Do not actually perform any optimizations but
|    instead return one line of text for each optimization that would
|    have been done. Off by default.


Regards,
Clemens
_______________________________________________
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: Output of pragma optimize?

Bart Smissaert
OK, thanks, I got it.
What would be a way to show this output?
I created a large table with no indexes, but no output. Then added an index
but no analyse, but no output.

RBS


On Sat, Aug 26, 2017 at 9:16 PM, Clemens Ladisch <[hidden email]> wrote:

> Bart Smissaert wrote:
> > Looking at the results of sqlite3_column_count (1) and also
> > sqlite3_column_name (optimize) it
> > seems that pragma optimize can have an output. What would that output be
> > and what would be a way to show such ouput?
>
> <http://www.sqlite.org/pragma.html#pragma_optimize> says:
> | The optional MASK argument is a bitmask of optimizations to perform:
> |
> | 1. Debugging mode. Do not actually perform any optimizations but
> |    instead return one line of text for each optimization that would
> |    have been done. Off by default.
>
>
> Regards,
> Clemens
> _______________________________________________
> 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: Output of pragma optimize?

Richard Hipp-3
On 8/26/17, Bart Smissaert <[hidden email]> wrote:
> OK, thanks, I got it.
> What would be a way to show this output?
> I created a large table with no indexes, but no output. Then added an index
> but no analyse, but no output.

Create two indexes on the table.  Then run some query that could
potentially use either index.  Then when you run "PRAGMA optimize" it
will tell you that you should run ANALYZE on that table.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Output of pragma optimize?

Bart Smissaert
Thanks, tried that but no output.
Should I run: pragma optimize(1) ?

RBS

On Sat, Aug 26, 2017 at 9:54 PM, Richard Hipp <[hidden email]> wrote:

> On 8/26/17, Bart Smissaert <[hidden email]> wrote:
> > OK, thanks, I got it.
> > What would be a way to show this output?
> > I created a large table with no indexes, but no output. Then added an
> index
> > but no analyse, but no output.
>
> Create two indexes on the table.  Then run some query that could
> potentially use either index.  Then when you run "PRAGMA optimize" it
> will tell you that you should run ANALYZE on that table.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: Output of pragma optimize?

Richard Hipp-3
On 8/26/17, Bart Smissaert <[hidden email]> wrote:
> Thanks, tried that but no output.
> Should I run: pragma optimize(1) ?
Yes.  Except that now that ANALYZE has been run, it wouldn't do
anything.  DROP the sqlite_stat1 table, then run the query again, then
run "PRAGMA optimize(1)".


--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Output of pragma optimize?

Bart Smissaert
Tried that but no output.
Will check my application code, my guess it doesn't handle this statement
well.

RBS

On Sat, Aug 26, 2017 at 10:05 PM, Richard Hipp <[hidden email]> wrote:

> On 8/26/17, Bart Smissaert <[hidden email]> wrote:
> > Thanks, tried that but no output.
> > Should I run: pragma optimize(1) ?
> Yes.  Except that now that ANALYZE has been run, it wouldn't do
> anything.  DROP the sqlite_stat1 table, then run the query again, then
> run "PRAGMA optimize(1)".
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: Output of pragma optimize?

Bart Smissaert
In reply to this post by Richard Hipp-3
I looked at my application code and that seems OK, but the output is a
single null value.
What kind of output should I get?

RBS

On Sat, Aug 26, 2017 at 10:05 PM, Richard Hipp <[hidden email]> wrote:

> On 8/26/17, Bart Smissaert <[hidden email]> wrote:
> > Thanks, tried that but no output.
> > Should I run: pragma optimize(1) ?
> Yes.  Except that now that ANALYZE has been run, it wouldn't do
> anything.  DROP the sqlite_stat1 table, then run the query again, then
> run "PRAGMA optimize(1)".
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: Output of pragma optimize?

Simon Slavin-3


On 26 Aug 2017, at 10:58pm, Bart Smissaert <[hidden email]> wrote:

> I looked at my application code and that seems OK, but the output is a
> single null value.
> What kind of output should I get?

That’s just SQLite returning SQLITE_OK because the command ran without problems.

You need to execute that PRAGMA command as if it’s a SELECT command.  It should return a table.  If the tools you’re using do not allow this, turn the PRAGMA into a SELECT command as follows:

SELECT * FROM PRAGMA optimize(1);

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: Output of pragma optimize?

Bart Smissaert
> SELECT * FROM PRAGMA optimize(1);

That doesn't prepare, error at (

RBS

On Sat, Aug 26, 2017 at 11:03 PM, Simon Slavin <[hidden email]> wrote:

>
>
> On 26 Aug 2017, at 10:58pm, Bart Smissaert <[hidden email]>
> wrote:
>
> > I looked at my application code and that seems OK, but the output is a
> > single null value.
> > What kind of output should I get?
>
> That’s just SQLite returning SQLITE_OK because the command ran without
> problems.
>
> You need to execute that PRAGMA command as if it’s a SELECT command.  It
> should return a table.  If the tools you’re using do not allow this, turn
> the PRAGMA into a SELECT command as follows:
>
> SELECT * FROM PRAGMA optimize(1);
>
> Simon.
> _______________________________________________
> 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: Output of pragma optimize?

Keith Medcalf

select * from pragma_optimize(1);

the table format of all pragmas is to prepend pragma_ to the pragma name.

select * from pragma_function_list;
select * from pragma_cache_size;

etc etc.

https://sqlite.org/pragma.html


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Bart Smissaert
>Sent: Saturday, 26 August, 2017 16:38
>To: SQLite mailing list
>Subject: Re: [sqlite] Output of pragma optimize?
>
>> SELECT * FROM PRAGMA optimize(1);
>
>That doesn't prepare, error at (
>
>RBS
>
>On Sat, Aug 26, 2017 at 11:03 PM, Simon Slavin <[hidden email]>
>wrote:
>
>>
>>
>> On 26 Aug 2017, at 10:58pm, Bart Smissaert
><[hidden email]>
>> wrote:
>>
>> > I looked at my application code and that seems OK, but the output
>is a
>> > single null value.
>> > What kind of output should I get?
>>
>> That’s just SQLite returning SQLITE_OK because the command ran
>without
>> problems.
>>
>> You need to execute that PRAGMA command as if it’s a SELECT
>command.  It
>> should return a table.  If the tools you’re using do not allow
>this, turn
>> the PRAGMA into a SELECT command as follows:
>>
>> SELECT * FROM PRAGMA optimize(1);
>>
>> Simon.
>> _______________________________________________
>> 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



_______________________________________________
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: Output of pragma optimize?

Bart Smissaert
This is interesting:

Some pragmas take effect during the SQL compilation stage, not the
execution stage. This means if using the C-language sqlite3_prepare()
<https://www.sqlite.org/c3ref/prepare.html>, sqlite3_step()
<https://www.sqlite.org/c3ref/step.html>, sqlite3_finalize()
<https://www.sqlite.org/c3ref/finalize.html> API (or similar in a wrapper
interface), the pragma may run during the sqlite3_prepare()
<https://www.sqlite.org/c3ref/prepare.html> call, not during the
sqlite3_step() <https://www.sqlite.org/c3ref/step.html> call as normal SQL
statements do. Or the pragma might run during sqlite3_step() just like
normal SQL statements. Whether or not the pragma runs during
sqlite3_prepare() or sqlite3_step() depends on the pragma and on the
specific release of SQLite.

When does the optimize pragma take effect?
I suppose any output will still happen after sqlite3_step.

RBS

On Sat, Aug 26, 2017 at 11:59 PM, Keith Medcalf <[hidden email]> wrote:

>
> select * from pragma_optimize(1);
>
> the table format of all pragmas is to prepend pragma_ to the pragma name.
>
> select * from pragma_function_list;
> select * from pragma_cache_size;
>
> etc etc.
>
> https://sqlite.org/pragma.html
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Bart Smissaert
> >Sent: Saturday, 26 August, 2017 16:38
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Output of pragma optimize?
> >
> >> SELECT * FROM PRAGMA optimize(1);
> >
> >That doesn't prepare, error at (
> >
> >RBS
> >
> >On Sat, Aug 26, 2017 at 11:03 PM, Simon Slavin <[hidden email]>
> >wrote:
> >
> >>
> >>
> >> On 26 Aug 2017, at 10:58pm, Bart Smissaert
> ><[hidden email]>
> >> wrote:
> >>
> >> > I looked at my application code and that seems OK, but the output
> >is a
> >> > single null value.
> >> > What kind of output should I get?
> >>
> >> That’s just SQLite returning SQLITE_OK because the command ran
> >without
> >> problems.
> >>
> >> You need to execute that PRAGMA command as if it’s a SELECT
> >command.  It
> >> should return a table.  If the tools you’re using do not allow
> >this, turn
> >> the PRAGMA into a SELECT command as follows:
> >>
> >> SELECT * FROM PRAGMA optimize(1);
> >>
> >> Simon.
> >> _______________________________________________
> >> 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
>
>
>
> _______________________________________________
> 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: Output of pragma optimize?

Simon Slavin-3


On 27 Aug 2017, at 12:29am, Bart Smissaert <[hidden email]> wrote:

> When does the optimize pragma take effect?

It analyzes data not available until you’re running your program.  So it can’t be prepared during compilation.

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: Output of pragma optimize?

Richard Hipp-3
In reply to this post by Bart Smissaert
On 8/26/17, Bart Smissaert <[hidden email]> wrote:
> I looked at my application code and that seems OK, but the output is a
> single null value.
> What kind of output should I get?

The following script shows you what to expect:

CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c);
CREATE INDEX t1b ON t1(b);
CREATE INDEX t1c ON t1(c);
SELECT a FROM t1 WHERE b=5 AND c=9;
PRAGMA optimize(-1);

Looks like the argument to optimize should be -1, not 1, in order to
see the debugging output.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Output of pragma optimize?

Bart Smissaert
Thanks, now it works!
I get:

optimize
-----------
ANALYZE "main"."t1"


RBS

On Sun, Aug 27, 2017 at 12:48 AM, Richard Hipp <[hidden email]> wrote:

> On 8/26/17, Bart Smissaert <[hidden email]> wrote:
> > I looked at my application code and that seems OK, but the output is a
> > single null value.
> > What kind of output should I get?
>
> The following script shows you what to expect:
>
> CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c);
> CREATE INDEX t1b ON t1(b);
> CREATE INDEX t1c ON t1(c);
> SELECT a FROM t1 WHERE b=5 AND c=9;
> PRAGMA optimize(-1);
>
> Looks like the argument to optimize should be -1, not 1, in order to
> see the debugging output.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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