Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Richard Hipp]

On 3/7/17, Olivier Mascia  wrote:
>> Le 7 mars 2017 à 04:13, Richard Hipp  a écrit :
>>
>> the database connection remembers (in RAM) specifically which
>> tables and indexes it has considered for use and will only run ANALYZE
>> on those tables for which some prior query would have benefited from
>> having good sqlite_stat1 numbers during the current session.
>
> What if or what impact is there from this pragma optimize when the software
> has been built with SQLITE_ENABLE_STAT4?

Then the ANALYZE commands that are run will also build the
sqlite_stat4 table.  But STAT4 data is not a factor in determining
when ANALYZE is run.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Olivier Mascia]

> Le 7 mars 2017 à 04:13, Richard Hipp  a écrit :
> 
> the database connection remembers (in RAM) specifically which
> tables and indexes it has considered for use and will only run ANALYZE
> on those tables for which some prior query would have benefited from
> having good sqlite_stat1 numbers during the current session.

What if or what impact is there from this pragma optimize when the software has 
been built with SQLITE_ENABLE_STAT4?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Eduardo Morras]

On Mon, 6 Mar 2017 18:52:48 -0500
Richard Hipp  wrote:

> On 3/6/17, Simon Slavin  wrote:
> >
> >> See
> >> https://www.sqlite.org/draft/pragma.html#pragma_optimize for
> >> additional information.
> >
> > I?m sure this is extremely far-future-looking, but a default mask
> > of 254 (0xfe) might be better than the stated default of 14 (0x0e).
> 
> Default mask changed to 0xfffe, which allows for up to 14 new
> default-on optimizations and up to 48 new default-off optimizations.

Could a trigger be fired on optimize? This way we could add database/schema 
specific optimizations (Delete all rows in table tab where column value is 
NULL, for example)

Thanks for the great work.

> -- 
> D. Richard Hipp
> d...@sqlite.org


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Eric Grange]

Very interesting development, thanks for pushing the boundaries at each new
release!

Would it be possible to consider some form of deferred optimize?
ie. rather than optimize when closing the connection, it would just write
the optimize info gathered during the heavy queries, for use in a future
optimize.

The rational is that heavy queries can come over several hours (and
multiple DB connections). During those "rush hours" running an ANALYZE
could be quite detrimental when the databases are large (even for read-only
queries, it would affect the service performance through I/O usage), and in
my particular usage case, somewhat less efficient queries are less of a
problem than stalling the service.

Once the rush hours have passed, the optimize would be run with the
previously collected data (I currently run a regular ANALYZE outside of
rush hours, but I gather it is only rarely beneficial).

Eric

On Tue, Mar 7, 2017 at 4:22 AM, jose isaias cabrera 
wrote:

>
> Richard Hipp wrote...
>
> On 3/6/17, jose isaias cabrera  wrote:
>
>>
>> Richard Hipp wrote...
>>
>> Have you ever wondered when you should run ANALYZE on an SQLite
>>> database?  It is tricky to figure out when that is appropriate.  The
>>>
>> Thanks for this.  I actually run this ANALYZE weekly with a script.  This
>> will be better.  I can run it everyday don't do any harm.  Thanks.
>>
>
> Thanks for letting me know.  I don't know if this applies in your case
>> or not, but reading your note made me realize that the documentation
>> might be misleading and/or unclear as written.
>>
>
> The "PRAGMA optimize" command should be run from the same database
>> connection that is doing the heavy queries.  The reason for this is
>> that the database connection remembers (in RAM) specifically which
>> tables and indexes it has considered for use and will only run ANALYZE
>> on those tables for which some prior query would have benefited from
>> having good sqlite_stat1 numbers during the current session.  That is
>> why "PRAGMA optimize" should be run as the database connection is
>> closing, rather than when it is first opened.
>>
>
> So it is not (currently) helpful to run "PRAGMA optimize" from a
>> separate connection, or a connection that is mostly idle.  It needs to
>> be the connection that is actually doing the interesting queries so
>> that SQLite can know which tables need to be analyzed.
>>
>
> This is exactly how it's going to be used...
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[jose isaias cabrera]

Richard Hipp wrote...

On 3/6/17, jose isaias cabrera  wrote:


Richard Hipp wrote...


Have you ever wondered when you should run ANALYZE on an SQLite
database?  It is tricky to figure out when that is appropriate.  The

Thanks for this.  I actually run this ANALYZE weekly with a script.  This
will be better.  I can run it everyday don't do any harm.  Thanks.



Thanks for letting me know.  I don't know if this applies in your case
or not, but reading your note made me realize that the documentation
might be misleading and/or unclear as written.



The "PRAGMA optimize" command should be run from the same database
connection that is doing the heavy queries.  The reason for this is
that the database connection remembers (in RAM) specifically which
tables and indexes it has considered for use and will only run ANALYZE
on those tables for which some prior query would have benefited from
having good sqlite_stat1 numbers during the current session.  That is
why "PRAGMA optimize" should be run as the database connection is
closing, rather than when it is first opened.



So it is not (currently) helpful to run "PRAGMA optimize" from a
separate connection, or a connection that is mostly idle.  It needs to
be the connection that is actually doing the interesting queries so
that SQLite can know which tables need to be analyzed.


This is exactly how it's going to be used...

josé 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Richard Hipp]

On 3/6/17, jose isaias cabrera  wrote:
>
> Richard Hipp wrote...
>
>>Have you ever wondered when you should run ANALYZE on an SQLite
>>database?  It is tricky to figure out when that is appropriate.  The
> Thanks for this.  I actually run this ANALYZE weekly with a script.  This
> will be better.  I can run it everyday don't do any harm.  Thanks.

Thanks for letting me know.  I don't know if this applies in your case
or not, but reading your note made me realize that the documentation
might be misleading and/or unclear as written.

The "PRAGMA optimize" command should be run from the same database
connection that is doing the heavy queries.  The reason for this is
that the database connection remembers (in RAM) specifically which
tables and indexes it has considered for use and will only run ANALYZE
on those tables for which some prior query would have benefited from
having good sqlite_stat1 numbers during the current session.  That is
why "PRAGMA optimize" should be run as the database connection is
closing, rather than when it is first opened.

So it is not (currently) helpful to run "PRAGMA optimize" from a
separate connection, or a connection that is mostly idle.  It needs to
be the connection that is actually doing the interesting queries so
that SQLite can know which tables need to be analyzed.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[jose isaias cabrera]

Richard Hipp wrote...


Have you ever wondered when you should run ANALYZE on an SQLite
database?  It is tricky to figure out when that is appropriate.  The
Thanks for this.  I actually run this ANALYZE weekly with a script.  This 
will be better.  I can run it everyday don't do any harm.  Thanks.


josé 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Richard Hipp]

On 3/6/17, Simon Slavin  wrote:
>
>> See
>> https://www.sqlite.org/draft/pragma.html#pragma_optimize for
>> additional information.
>
> I’m sure this is extremely far-future-looking, but a default mask of 254
> (0xfe) might be better than the stated default of 14 (0x0e).

Default mask changed to 0xfffe, which allows for up to 14 new
default-on optimizations and up to 48 new default-off optimizations.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Simon Slavin]

On 6 Mar 2017, at 9:30pm, Richard Hipp  wrote:

> See
> https://www.sqlite.org/draft/pragma.html#pragma_optimize for
> additional information.

I’m sure this is extremely far-future-looking, but a default mask of 254 (0xfe) 
might be better than the stated default of 14 (0x0e).

---



Would it be possible to improve the description of this check to make it clear 
whether all violations, or only the first violation, of each FOREIGN KEY is 
reported ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Jens Alfke]

> On Mar 6, 2017, at 1:30 PM, Richard Hipp  wrote:
> 
> Have you ever wondered when you should run ANALYZE on an SQLite
> database?  It is tricky to figure out when that is appropriate.  The
> new "PRAGMA optimize" command tries to automate the decision for you.

Very nice!! Thanks for implementing this.

I noticed one typo in the docs of this pragma:
_For_ achieve the best long-term query performance…
Should be something like
_To_ achieve the best long-term query performance…

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 3.18.0 pre-release snapshot

[Richard Hipp]

There is a fresh source-code snapshot of the unreleased 3.18.0 version
of SQLite up on the download page:

https://www.sqlite.org/download.html
https://www.sqlite.org/draft/releaselog/3_18_0.html   <--- Change log

Have you ever wondered when you should run ANALYZE on an SQLite
database?  It is tricky to figure out when that is appropriate.  The
new "PRAGMA optimize" command tries to automate the decision for you.
Simply run "PRAGMA optimize" just prior to closing your database
connection, and SQLite willl automatically run ANALYZE if it thinks
doing so will improve query performance.  Usually, the "PRAGMA
optimize" will be a no-op.  But every now and then it will reanalyze
important indexes and thereby help you queries to run optimally.  See
https://www.sqlite.org/draft/pragma.html#pragma_optimize for
additional information.  Feedback on this new feature is encouraged.

NOTE TO FOSSIL USERS:

If you access the SQLite sources directly using the Fossil version
control system (https://www.fossil-scm.org/fossil/doc/trunk/www/index.wiki)
then you should upgrade to the latest version of Fossil - probably the
latest unreleased trunk version.  Newer versions of Fossil understand
SHA3-256 hash names on artifacts in addition to the older SHA1 names.
Older versions of Fossil only understand SHA1 names.  We expect to
start using SHA3-256 hash names in the SQLite repository soon. If you
fail to upgrade, your older "fossil" application will give an error
the first time you try to "fossil update" after SHA3 content is added.
See https://www.fossil-scm.org/fossil/doc/trunk/www/hashpolicy.wiki
fpr additional information.



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users