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 have
Hello All,
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 b
Hi,
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
milliseconds.
The problematic query looks like
select d.key_field, count(*) nb
from low_volume_table b
join mid_volume_table c on c.key
ults 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
works.
On Tue, Jun
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:
Hi,
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.
Eri
rgess
> download sqlite3_analyzer
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
> from
> http://www2.sqlite.org/2018/sqlite-tools-linux-x86-324.zip
>
>
>
> On Mon, Jul 30, 201
Hi,
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
schema.
The values are cryptographic GUIDs (so 256 to 512 bits in size) with a
"co
Mon, Jul 30, 2018 at 11:16 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:
> If I understand correctly then changing from a base64 index to a blob
> containing the raw bytes would save 25%
>
> Paul
> www.sandersonforensics.com
> SQLite Forensics Book <https://www.
30, 2018 at 10:11 AM Eric Grange
> wrote:
> >
> >> @David Yip
> >> > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB),
> you
> >> > can query the dbstat virtual table for the number of pages used per
> >> table and index, e
@Dominique Devienne
> SQLite supports function-based indexes, but unfortunately if does not support
"function-based columns".
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
expos
0, 2018 at 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
> in
> > a particular order and
> > > refers back to the table fo
gt;
> One of the main reasons for 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,
>Donald
>
>
>
>
>
> On Mon, Jul 30, 2
Hi,
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 (122
Hi,
I am running into a little trouble when trying to use and "upsert" from a
select clause.
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
but as soon
ial'
> )
> INSERT INTO vocabulary(word)
> 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
Hi,
Is json_group_array() supposed to honor a sorting clause or not ? (and
concatenation aggregates in general)
I have a query like
select json_group_array(json_object(
'id', st.id,
'num', st.numeric_field,
...bunch of fields here...
))
from some_table st
...bunch of joins here...
where
u need to enforce that order, 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-b
improved, as otherwise query
planner improvements
could end up wrecking existing queries where the subtype currently survives.
Eric
Le mar. 8 janv. 2019 à 11:41, Dominique Devienne a
écrit :
> On Tue, Jan 8, 2019 at 11:04 AM Dominique Devienne
> wrote:
> >
> > On Tue, Jan 8
Hi,
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
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 y
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, or
> 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 prominen
Hi,
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 ROW_NUMB
wrote:
> On 9 Jan 2018, at 9:50am, Eric Grange wrote:
>
> > then I fill that table with something like
> >
> > INSERT INTO RANKED
> > SELECT key, value
> > FROM ...something rather complex and big...
> > ORDER BY value desc
> >
On Tue, Jan 9, 2018 at 11:44 AM, Dominique Devienne
wrote:
> On Tue, Jan 9, 2018 at 11:26 AM, Eric Grange wrote:
>
> > So the order by is used to control the insertion order, so that the RANK
> > autoinc primary key ends up with natural rank order
>
>
> But then
t case ?
On Tue, Jan 9, 2018 at 6:21 PM, Simon Slavin wrote:
> On 9 Jan 2018, at 11:35am, Eric Grange wrote:
>
> > In both cases, since things are constantly in flux, the absolute rank and
> > neighbor do not really matter
> > (outside the top ranks), but changes in rank
> 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 first
Hi,
Is there a way to have sqlite3_column_decltype return the affinity for an
expression ?
https://www.sqlite.org/datatype3.html#affinity_of_expressions
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 docume
be able to recover that info whe it is provide through a
cast.
On Fri, Mar 30, 2018 at 6:04 PM, Simon Slavin wrote:
> On 30 Mar 2018, at 11:22am, Eric Grange wrote:
>
> > Is there a way to have sqlite3_column_decltype return the affinity for an
> > expression ?
eclared 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 wrote:
> On 1 Apr 2018, at 8:19am, Eric Grange wrote:
>
> > The dec
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 as
Hi,
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
queries
Table declaration:
CREATE TABLE vin (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tx_id INTEGER NOT NULL,
from_vout_id INTEGER,
addr_
this
would not be too practical.
On Tue, May 19, 2015 at 10:15 AM, Eric Grange wrote:
> Hi,
>
> 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
> queries
>
> Table declarat
While it happened I tested on 3.8.10.1 and 3.8.8.1
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
wrote:
> Eric Grange wrote:
> > I recently added a
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 for
Hello,
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
a
Thanks!
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
Hi,
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 int
> All BLOBs are stored inline. The only complication comes when the total
row length is longer than a page.
Ok thanks!
That is besides my original question but what is the complication in that
case?
Is the blob partially stored in the row, or is some indirection to other
pages used, with only so
t; -Original Message-
> > 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 s
> I tried your example and got ~0.45s for json and ~0.37s for my
> implementation;
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. Currently
On Tue, Sep 6, 2016 at 10:19 AM, Stephan Beal wrote:
> On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange 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 the pages, especially
> &g
10:19 AM, Stephan Beal wrote:
>
>> On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange 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 the pages, especially
>>> for the documen
Hi,
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
select
case when field2 is null then
json_object('field1', field
"value","field2":""}
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 wrote:
>
> On 2016/09/26 11:15 AM, Eric Grange wrote:
>
>> Hi,
>&g
Nice solution and works like a charm, thanks Clemens!
On Mon, Sep 26, 2016 at 4:22 PM, Clemens Ladisch 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 output json
>
Hi,
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 hours,
Thanks
On Tue, Oct 4, 2016 at 4:05 PM, Simon Slavin wrote:
>
> On 4 Oct 2016, at 2:53pm, Eric Grange wrote:
>
> > I am going on the assumption that if something fails during backup, the
> > backup itself will be toast anyway, but is that safe otherwise?
>
> No. Y
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 wrote:
> Eric Grange wrote:
> > If
case.
Eric
On Wed, Oct 5, 2016 at 8:42 AM, Eric Grange 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: why the sqlite CLI
Eric
On Wed, Oct 5, 2016 at 12:50 PM, Clemens Ladisch 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);
> >
> What suffixes to sqlite database files do you use or see in the wild?
.sql3
.dat
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?
>>
>
> I routinely/always use .sq3 but
> 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
wrong micr
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
o
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 cu
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 it
> 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
memory.
> If the adversary is another process on the same host, encrypting the db
> just adds obfuscation, which is se
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 corruptio
>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 heuristics
> 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
matters"
In the real world, text files are heavily skewed towards 8 bit formats,
meaning just three cases dominate the debate:
- ASCII
> 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
to
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 wrote:
> On 28 Jun 2017 at 14:20, Rowan Worth wrote:
>
> > On 27 June 2017 at 18:42, Eric Grange wrote:
>
> Could I suggest that the STMT virtual table be renamed as something like
sqlite_statements ?
> 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_"
namespace
Then, do you know a way to optimize my usage ?
I suppose you have a pattern to which database are accessed at any point
in time?
If so rather than opening and closing databases directly, you could pool
them, so that if an operation is needed on a recently opened database,
it will still be o
Hi,
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.
I'm lo
Hi,
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 them
at least up to 3 columns and when using integers or
small strings.
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é wrote:
> Richard Hipp 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, co
Hi,
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
indep
Yes A2 & B2 are already indexed (individually and in composite indexes)
The problem is that this indexing is not selective enough when taken in
isolation.
Le 3 mars 2015 12:36, "Simon Davies" a ?crit
:
> On 3 March 2015 at 11:10, Eric Grange wrote:
> >
> > Hi,
&
"better" solution.
> 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,
> I wrote earlier that for us use case where we are reading whole rows is
the most common one.
> [...]
> we are looking for ways to store this in a stable format which will be
supported for next 50 years, without modifying to original data too much.
If you do not need access to individual columns
While I am using SQLIite mostly in x86-64 with plentiful RAM, I suspect the
reduced lookaside size will help CPU L1/L2 caches, which is definitely
never plentiful. I would not be surprised if it resulted in a speedup in
practical high-load multi-threaded scenarios.
Also future SQLite code changes
> Indeed turning off memstatus leads to a 500% (from ~3s to ~0.5s)
performance increase.
> Changing the threading mode or the indirection level of the mutexes calls
seems to have no significant effect.
That is quite significant.
Looking at the code, it seems the mutex requirement is mostly for the
Maybe "edge" database ? Or "local" database ? Both are trending terms, on
the theme of taking control and performance back from the cloud.
"Embedded" would be technically good, but is often associated with devices
and small things these days.
Le mar. 28 janv. 2020 à 05:58, Rowan Worth a écrit :
If the trouble comes from a big "IN()", an approach can be to pass all the
values in a JSON array (one parameter) and use json_each in the query.
This is completely safe vs SQL injection, and IME quite efficient.
IME using JSON + json_each is also very efficient to fill temporary tables
(indexed i
79 matches
Mail list logo