I am looking for the fastest way to insert many rows to a simple table.
By "simple" I mean a relation table (with just a couple integer/key fields)
or even a single-column (temp table used for filtering as an alternative to
a "in" literal), but I want to insert hundreds of thousandths of
So "insert from select" seems to have some avantage, and I was wondering if
there are other ways to leverage it?
On Fri, May 27, 2016 at 12:29 AM, Dominique Pellé <dominique.pe...@gmail.com
> Richard Hipp <d...@sqlite.org> wrote:
> > On 5
> https://www.sqlite.org/whentouse.html :
I am currently relying on SQLite as main database engine for blockchain
explorers, that's usually between 1 and 2 millions http queries per day,
pretty much all of them hitting more than 60 GB of SQLite databases. There
is a mix of simple table queries,
I have problem where I need a "multi-table index" ersatz, or maybe a better
data structure :-)
The problem is as follow:
- Table A : some fields plus fields A1 & A2
- Table B : some fields plus fields B1 & B2
Both tables have several dozen millions of rows, and both are accessed
Yes A2 & B2 are already indexed (individually and in composite indexes)
The problem is that this indexing is not selective enough when taken in
Le 3 mars 2015 12:36, "Simon Davies" a ?crit
> On 3 March 2015 at 11:10, Eric Grange wrote:
> > Hi,
> It might be possible to write a virtual table module that does the same
> as your index on C, but with C being a view.
I had a look that way, but AFAICT virtual tables do not support indexing,
so I would have to index manually.
On Tue, Mar 3, 2015 at 4:57 PM, Dan Kennedy w
I recently added a field and index to an existing table, and now SQLite
seems to be using that index in place of the primary key, even on simple
CREATE TABLE vin (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tx_id INTEGER NOT NULL,
would not be too practical.
On Tue, May 19, 2015 at 10:15 AM, Eric Grange wrote:
> I recently added a field and index to an existing table, and now SQLite
> seems to be using that index in place of the primary key, even on simple
> Table declarat
While it happened I tested on 126.96.36.199 and 188.8.131.52
The DB is using WAL mode, and there were several connections to it, I
suppose this might have interfered with the index or stats?
On Tue, May 19, 2015 at 10:50 AM, Clemens Ladisch
> Eric Grange wrote:
> > I recently added a
I would like to use SQLite in a scripting context where there are thousands
of "potential" virtual tables, but of these, only a very very small subset
(a couple or a handful at most) would be actually used in any given script,
so it wouldn't make sense to expose them all, all the time.
Is there a way to perform an online backup with unbuffered I/O?
I am currently performing backups with the command-line tool and ".backup",
but this appears to wreak havoc on the OS disk cache.
I am able to mitigate this by disabling buffering on the destination drive,
but that cannot be used
I have been experimenting with using the json1 extension to generate json
sql-side rather than code-side, but I am hitting an issue with the CR & LF
characters which are not escaped, which results in json that fails
JSON.parse or use as JSONP in browsers. Ideally CR / LF should be encoded
I can make do with replace() for now (as in my particular case, I only have
CRLF & TAB to replace)
On Thu, Feb 4, 2016 at 11:39 AM, Richard Hipp wrote:
> On 2/4/16, Richard Hipp wrote:
> > On 2/4/16, Eric Grange wrote:
> >> Hello,
IME when SQLite is applicable (mostly the one writer limit), it usually
runs circles around to server DBs, provided:
- you have comparable CPU
- you have enough cache memory, or free RAM for OS disk caching, to be in a
comparable memory scenario
- you do not have a virtualisation layer, especially
I am wondering if very small BLOBs are stored inline with the record fields
(in the same page) or in another page?
So far I had not worried about it, but now I have a usage case where I need
to store 128 and 256 bit integers (numbers, not IDs or hashes), SQLite does
not support such large
> All BLOBs are stored inline. The only complication comes when the total
row length is longer than a page.
That is besides my original question but what is the complication in that
Is the blob partially stored in the row, or is some indirection to other
pages used, with only
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Eric Grange
> > Sent: Thursday, March 03, 2016 3:16 AM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Are BLOB stored inline wi
We've begun implementing some virtual tables with latest SQLite build
and run into a few questions performance-wise, all these questions
revolve around the same issue (join performance).
1) Is there a way to know which fields are going to be potentially
requested by xColumn? For instance if I
Using 3.6.21 and FTS3, I've noticed than when doing search queries,
using docid as in
select fts.docid from fts where fts.content match "la"
the search is much slower than when using rowid as in
select fts.rowid from fts where fts.content match "la"
Isn't docid supposed to
> I tried your example and got ~0.45s for json and ~0.37s for my
I have also tried a custom virtual table, and I also get figures about
20-30% faster than with json.
> Interesting: when NOT using "primary key" (I have a flag for this), the
> times are ~ 0.26s vs 0.18s vs 0.47s
Seems to be fine from here, and probably not too critical, as not all
websites have a purpose on mobile devices.
However, while you are at it, an improvement for the website when browsed
from a computer would be to limit the max width of the pages, especially
for the documentation pages.
On Tue, Sep 6, 2016 at 10:19 AM, Stephan Beal <sgb...@googlemail.com> wrote:
> On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange <zar...@gmail.com> wrote:
> > However, while you are at it, an improvement for the website when browsed
> > from a computer would be
> On 2016/09/06 10:19 AM, Stephan Beal wrote:
>> On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange <zar...@gmail.com> wrote:
>> However, while you are at it, an improvement for the website when browsed
>>> from a computer would be to limit the max width of th
Nice solution and works like a charm, thanks Clemens!
On Mon, Sep 26, 2016 at 4:22 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Eric Grange wrote:
> > I am generating a json with json_object() function, and I would like to
> > omit the null or default fields from the o
I am generating a json with json_object() function, and I would like to
omit the null or default fields from the output json (for compacity reasons)
When there is only one such field, I have used a "case when" like in
case when field2 is null then
On Wed, Oct 5, 2016 at 12:50 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Eric Grange wrote:
> > Ran some tests with variations of Clemens's backup exemple, and adding
> > sqlite3_exec(src, "PRAGMA cache_size=1", NULL, NULL, NULL);
On Wed, Oct 5, 2016 at 8:42 AM, Eric Grange <zar...@gmail.com> wrote:
> Thanks for the tool Clemens!
> > No, this is what makes the backup restart. With a step size of -1, it
> would never restart.
> Hmm, given WAL mode, this brings another question: w
ant to provide some
progress indicator or pace the backup? (ie. pace I/O bandwidth ?)
Or in other words, given WAL mode, what do we "lose" when using -1 and to
copy everything at once during a backup?
On Tue, Oct 4, 2016 at 5:33 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
Given a fairly large database (dozens of gigabytes), which uses WAL, and is
being accessed continuously (mostly read transactions, but regular write
transactions as well), what are the fastest and less disruptive ways to
back it up?
A basic ".backup" from the CLI can occasionnally take
On Tue, Oct 4, 2016 at 4:05 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 4 Oct 2016, at 2:53pm, Eric Grange <zar...@gmail.com> wrote:
> > I am going on the assumption that if something fails during backup, the
> > backup itself will be toast anyway, but
I also tried setting the field name to null, but that triggers an error
"json_object() labels must be TEXT"
On Mon, Sep 26, 2016 at 2:28 PM, R Smith <rsm...@rsweb.co.za> wrote:
> On 2016/09/26 11:15 AM, Eric Grange wrote:
> What suffixes to sqlite database files do you use or see in the wild?
On Fri, Oct 21, 2016 at 7:46 AM, Jean-Christophe Deschamps wrote:
> At 01:17 21/10/2016, you wrote:
> What suffixes to sqlite database files do you use or see in the wild?
> If this is true, then perf is entirely unsuitable for
> microoptimization, since microoptimization depends on having
> reproducible results.
Reproducibility can be misleading however if it is achieved by simulated
CPU cache and instruction pipelines, as this could lead to favoring the
For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
Personally I never had a need for a RIGHT JOIN, not because of theoretical
or design considerations, but it just never came into my flow of thought
when writing SQL...
I guess some automated SQL query generators could use
A bonus of having them defined in the core is that it avoids the minor
inconsistencies that are bound to arise in custom implementations (starting
with the name of math functions)
Main downside is probably not going to be the size, but that it reserves
more names, and may conflict with existing
Very interesting development, thanks for pushing the boundaries at each new
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
The main problem with multiple writers would be in conflict resolution,
locking and deadlocks.
Imagine a simple accounting transactions "update accounts set value = value
+ 1 where ..." if run at the same time from 2 threads (or close enough),
then if you do not have conflict resolution in place
> Could I suggest that the STMT virtual table be renamed as something like
> Or anything else that starts with "sqlite_" ?
Seconded, especially as more and more virtual tables become available (a
good thing), there is a greater risk of name collision, so an "sqlite_"
> ASCII / ANSI is a 7-bit format.
ASCII is a 7 bit encoding, but uses 8 bits in just about any implementation
out there. I do not think there is any 7 bit implementation still alive
outside of legacy mode for low-level wire protocols (RS232 etc.). I
personally have never encountered a 7 bit ASCII
figure out down the road where the garbage in your database originated from
(incorrect input? bug in the heuristics? etc.)
On Wed, Jun 28, 2017 at 10:40 PM, Tim Streater <t...@clothears.org.uk> wrote:
> On 28 Jun 2017 at 14:20, Rowan Worth <row...@dug.com> wrote:
> > On
> Isn't it all just obfuscation?
Not really, the encryption protects the file, wherever it is, as long as
the attacker does not have access to the application keys or application
> If the adversary is another process on the same host, encrypting the db
> just adds obfuscation, which is
Alas, there is no end in sight to the pain for the Unicode decision to not
make the BOM compulsory for UTF-8.
Making it optional or non-necessary basically made every single text file
ambiguous, with non-trivial heuristics and implicit conventions required
instead, resulting in character
>Easily solved by never including a superflous BOM in UTF-8 text
And that easy option has worked beautifully for 20 years... not.
Yes, BOM is a misnommer, yes it "wastes" 3 bytes, but in the real world
"text files" have a variety of encodings.
No BOM = you have to fire a whole suite of
> In case 7 we have little choice but to invoke heuristics or defer to the
> user, yes?
Yes in theory, but "no" in the real world, or rather "not in any way that
In the real world, text files are heavily skewed towards 8 bit formats,
meaning just three cases dominate the debate:
For complete tamper-proofness, you need "something" external to the system,
ie. not in the database, not in the code, and not on the server:
- if you use signatures, then those signatures should be made by a secure
signing service or device
- if you use hashes (be it a merkle tree, a blockchain,
> I've also attached results of "EXPLAIN UPDATE ITEM SET FIELD4 = 10 WHERE
DS = 15;".
Not zipvfs specific, but that kind of update can be quite inefficient if
the record is large (in terms of bytes, or records per database page) as
your table declaration hints to.
This will be especially
I am experiencing a massive performance issue on a query with a left join
in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few
The problematic query looks like
select d.key_field, count(*) nb
from low_volume_table b
join mid_volume_table c on
results in the same table scan of
high_volume_table first, just without the index.
- using the unary "+" on the d table filters has no effect on the query
plan (as these are not indexed in the first place I guess)
Using unlikely() on the d table filters seems to be the only option that
On Tue, Jun
is dynamically generated from user options and filters (which can indeed
lead to SQL that is not really "optimal").
Is having a cross join somewhere among the joins enough to "disable" the
left join strength reduction for other joins?
On Tue, Jun 26, 2018 at 5:58 PM, Richard Hipp wrote:
As quick insight I gleaned from this list sometime ago: if you only need to
be able to search and sort, the blobs can be used.
If you can live with a fixed size (f.i. 256bits), then just use the
fixed-size big-endian representation as blob content.
For variable-length big integers, encode them
> On 9 Jan 2018, at 9:50am, Eric Grange <zar...@gmail.com> wrote:
> > then I fill that table with something like
> > INSERT INTO RANKED
> > SELECT key, value
> > FROM ...something rather
Tue, Jan 9, 2018 at 11:44 AM, Dominique Devienne <ddevie...@gmail.com>
> On Tue, Jan 9, 2018 at 11:26 AM, Eric Grange <egra...@glscene.org> wrote:
> > So the order by is used to control the insertion order, so that the RANK
> > autoinc primary key ends up with na
I have a problem where I have a large set of (key, value) which I want to
sort by value, and then store in a table having (rank, key, value) fields,
so that for a given key I can quickly find the rank, or for a given rank
range, I can quickly list the keys & values.
Since there is no
t case ?
On Tue, Jan 9, 2018 at 6:21 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 9 Jan 2018, at 11:35am, Eric Grange <egra...@glscene.org> wrote:
> > In both cases, since things are constantly in flux, the absolute rank and
> > neighbor do not really matte
r using a database is to automate the creation
> and use of efficient search algorithms with the tradeoff of more
> expenditure on disk space.
> I hope you find an acceptable solution,
> On Mon, Jul 30, 2018 at 4:32 AM Eric Grange wrote:
Is there a quick way (as in speed) to obtain the number of database blocks
(or kilobytes) allocated for each table and each index ?
I have been using various manual approaches so far (from using length() to
vacuum and drops), but none of them are really fast
or can practical to automate.
Thanks, the text output is interesting with built-in documentation.
On Mon, Jul 30, 2018 at 9:06 AM, D Burgess wrote:
> download sqlite3_analyzer
> On Mon, Jul 30, 2018 at 4:46
Is there a way to reduce the size of an index on strings/blobs ?
I have tables which are a key + value, the key is an "integer primary key
autoincrement", which is used for references in all other tables of the
The values are cryptographic GUIDs (so 256 to 512 bits in size) with a
Jul 30, 2018 at 11:16 AM, Paul Sanderson <
> If I understand correctly then changing from a base64 index to a blob
> containing the raw bytes would save 25%
> SQLite Forensics Book <https://www.amazo
30, 2018 at 10:11 AM Eric Grange
> >> @David Yip
> >> > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB),
> >> > can query the dbstat virtual table for the number of pages used per
> >> table and index, e
> SQLite supports function-based indexes, but unfortunately if does not support
Far fetched maybe, but could a virtual table or table-valued functions be
used to provide that?
ie. use the virtual table to pass data directly to an index, and then
12:05 PM, Rowan Worth wrote:
> On 30 July 2018 at 17:53, Eric Grange wrote:
> > @Rowan Worth
> > > What if you could create a "lite" index, which stores just the rowids
> > a particular order and
> > > refers back to the table for the rest
Is there a way to have sqlite3_column_decltype return the affinity for an
states that a CAST can be used to specify the affinity of a column, however
sqlite3_column_decltype does not report that affinity, as is
to be able to recover that info whe it is provide through a
On Fri, Mar 30, 2018 at 6:04 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 30 Mar 2018, at 11:22am, Eric Grange <zar...@gmail.com> wrote:
> > Is there a way to have sqlite3_column_decltype return the affini
ed type of "type". >>
so the sql engine already has to keep track of the affinity specified
through "cast()" if I understood the above correctly
On Sun, Apr 1, 2018 at 3:19 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 1 Apr 2018, at 8:19am, Eric Grange <
> Add the LEFT JOIN strength reduction optimization that converts a LEFT
JOIN into an ordinary JOIN
A question on this one: I have been using LEFT JOIN for many years (and not
juste in SQLIte) in cases where a JOIN could have been used as a way to
"hint" query optimizers which tables to scan
For version 3.25.2, the precompiled x64 DLL appears missing, there are
several vsix downloads, but when extracting the bundled DLLs
(in the Redist/Retail directories), they appear different (or non-standard
ones that need some form of post-processing ?),
for instance the x86 dll is larger
I am running into a little trouble when trying to use and "upsert" from a
Starting from the "vocabulary" exemple at
https://sqlite.org/lang_UPSERT.html this works
INSERT INTO vocabulary(word, count)
SELECT 'jovial', 1
ON CONFLICT(word) DO UPDATE SET count=count+1
> SELECT w FROM A WHERE 1
> ON CONFLICT(word) DO UPDATE SET wcount=wcount+1
> SELECT * FROM vocabulary
>-- word |wcount
>-- -- |
>-- jovial | 2
> PS: I used "wcount" rather because &
der, with an order by clause in a
> subselect if necessary.
> If you have an order by clause which is already fulfilled by the
> visitation order, SQLite will not sort again.
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlit
Is json_group_array() supposed to honor a sorting clause or not ? (and
concatenation aggregates in general)
I have a query like
...bunch of fields here...
from some_table st
...bunch of joins here...
could end up wrecking existing queries where the subtype currently survives.
Le mar. 8 janv. 2019 à 11:41, Dominique Devienne a
> On Tue, Jan 8, 2019 at 11:04 AM Dominique Devienne
> > On Tue, Jan 8, 2019 at 10:50 AM Eric Grange wrote:
Is there are way to get more information about an SQL syntax error message ?
For example on a largish SQL query I got the following error message
near "on": syntax error
but as the query is basically a long list of joins, this is not too helpful
Having a character offset, a line
Mail list logo