SQLite 3.20 Improves Query Planner
SQLite 3.20 Improves Query Planner
Written by Kay Ewbank   
Tuesday, 08 August 2017

There's a major upgrade to SQLite, the embeddable SQL database library that is found in many memory constrained gadgets such as cellphones, PDAs, and MP3 players. The new release has an improved query planner, and an extension that suggest tab-completions for interactive user interfaces.

SQLite is an in-process library that implements a self-contained, serverless, transactional SQL database engine. The entire SQL database with multiple tables, indexes, triggers, and views, is contained in a single disk file.

SQLite 3.20 has several new extensions. A virtual table extension, SQLite_Stmt implements a virtual table that provides information about all prepared statements associated with the database connection. A new Union virtual table extension adds another virtual table that makes multiple independent rowid tables tables look like a single large table.

The Completion extension is described as a work in progress. It implements a table-valued function named "completion" that can be used to suggest completions of partially entered words during interactive SQL input. The completion table can be used to help implement tab-completion, for example.

The query planner has been improved in a number of ways. It will now move constant WHERE expressions outside the loop of any individual loops for each ORed term of an OR scan. Another improvement to the handling of WHERE clauses is that WHERE clause constraints are evaluated last when they involve correlated queries. This has been added because such clauses may never have to be evaluated at all, so it makes sense to work them out only if they are actually reached.

If you specify bound parameters, their values are now examined to work out whether a partial index is usable. Another improvement means that if two plans have the same estimated cost, bias, the selection is made to use the one that does not use the sorter.

The flattening optimization will no longer be used on sub-queries on the RHS of a LEFT JOIN if that subquery reads data from a virtual table. This has been changed because that action prevents the query planner from creating automatic indexes on the results of the sub-query, which can slow down the query.

There are also improvement to the command shell, and better date and time functions that can be used in CHECK constraints, in indexes on expressions, and in the WHERE clauses of partial indexes.

SQLite 3.2 has also had its performance optimzed with the result it should user around 2% fewer CPU cycles.

sqlite

More Information

SQLite Site

Related Articles

SQLite 3.17 Adds SHA1 Extensions 

SQLite 3.16 Adds Pragma Functions  

SQLite 3.15 Adds Row Value Support

Portable Version Of DB Browser For SQLite

SQLite 3.9 

SQLite Updated

 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on, Twitter, FacebookGoogle+ or Linkedin.

 

Banner


Tabs v Spaces - More Analysis
17/07/2017

The recent finding that developers who use spaces for indentation make more money that those who use tabs led to a few raised eyebrows and puzzled looks. Now reanalysis of the data finds further  [ ... ]



Where To Find Top U.S. Developers
28/07/2017

You might imagine that California, where Silicon Valley is located and with the highest concentration of  software developers, would be the state with the United States' top coders. In fact, acco [ ... ]


More News

 

 
 

 

blog comments powered by Disqus

Last Updated ( Wednesday, 09 August 2017 )
 
 

   
Banner
RSS feed of news items only
I Programmer News
Copyright © 2017 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.