SQLite 3.18.0 pre-release snapshot

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

SQLite 3.18.0 pre-release snapshot

Richard Hipp-3
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
[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
|  
Report Content as Inappropriate

sqlite3_set_last_insert_rowid()

Simon Slavin-3

On 6 Mar 2017, at 9:30pm, Richard Hipp <[hidden email]> wrote:

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

The mention of sqlite3_set_last_insert_rowid() reminds me of a question I have:

Here’s the documentation for last_insert_rowid():

"If an INSERT occurs within a trigger or within a virtual table method, then this routine will return the rowid of the inserted row as long as the trigger or virtual table method is running. But once the trigger or virtual table method ends, the value returned by this routine reverts to what it was before the trigger or virtual table method began."

This means that each TRIGGER is its own little story, right ?  Triggers can make their own inserts.  Which may themselves have triggers.  SQLite maintains a happy stack of values for last_insert_rowid(), pushing and popping them each time you enter or exit a trigger ?

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
|  
Report Content as Inappropriate

Re: sqlite3_set_last_insert_rowid()

Richard Hipp-3
On 3/6/17, Simon Slavin <[hidden email]> wrote:
>
> This means that each TRIGGER is its own little story, right ?  Triggers can
> make their own inserts.  Which may themselves have triggers.  SQLite
> maintains a happy stack of values for last_insert_rowid(), pushing and
> popping them each time you enter or exit a trigger ?

Correct
--
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
|  
Report Content as Inappropriate

Re: sqlite3_set_last_insert_rowid()

Simon Slavin-3

On 6 Mar 2017, at 10:01pm, Richard Hipp <[hidden email]> wrote:

> On 3/6/17, Simon Slavin <[hidden email]> wrote:
>>
>> This means that each TRIGGER is its own little story, right ?  Triggers can
>> make their own inserts.  Which may themselves have triggers.  SQLite
>> maintains a happy stack of values for last_insert_rowid(), pushing and
>> popping them each time you enter or exit a trigger ?
>
> Correct

Thank you.  I wrote one piece of code which assumed this but wasn’t sure whether it was certain in future versions.  What you wrote assures me that I can assume future versions of SQLite3 will work the same way.

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
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

Jens Alfke-2
In reply to this post by Richard Hipp-3

> On Mar 6, 2017, at 1:30 PM, Richard Hipp <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

Simon Slavin-3
In reply to this post by Richard Hipp-3

On 6 Mar 2017, at 9:30pm, Richard Hipp <[hidden email]> 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).

-------

<https://www.sqlite.org/draft/pragma.html#pragma_foreign_key_check>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

Richard Hipp-3
On 3/6/17, Simon Slavin <[hidden email]> 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
[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
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

jose isaias cabrera-3
In reply to this post by Richard Hipp-3

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

Richard Hipp-3
On 3/6/17, jose isaias cabrera <[hidden email]> 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
[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
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

jose isaias cabrera-3

Richard Hipp wrote...

On 3/6/17, jose isaias cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>
wrote:

>
> Richard Hipp wrote...
>
> On 3/6/17, jose isaias cabrera <[hidden email]> 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
> [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
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

Eduardo Morras-2
In reply to this post by Richard Hipp-3
On Mon, 6 Mar 2017 18:52:48 -0500
Richard Hipp <[hidden email]> wrote:

> On 3/6/17, Simon Slavin <[hidden email]> 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
> [hidden email]


---   ---
Eduardo Morras <[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
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

Olivier Mascia
In reply to this post by Richard Hipp-3
> Le 7 mars 2017 à 04:13, Richard Hipp <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite 3.18.0 pre-release snapshot

Richard Hipp-3
On 3/7/17, Olivier Mascia <[hidden email]> wrote:
>> Le 7 mars 2017 à 04:13, Richard Hipp <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...