Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

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

Re: [sqlite] New word to replace "serverless"

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

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

> 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

Re: [sqlite] Performance vs. memory trade-off question

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

Re: [sqlite] Limit on number of columns in SQLite table

> 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

[sqlite] Location of error in SQL statements ?

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

Re: [sqlite] [EXTERNAL] json_group_array() and sorting

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, 2019 at 10:50 AM Eric Grange wrote: > >&g

Re: [sqlite] [EXTERNAL] json_group_array() and sorting

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

[sqlite] json_group_array() and sorting

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...

Re: [sqlite] upsert from select

ocabulary(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 "wcount" rather because &

[sqlite] upsert from select

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

[sqlite] SQLite pre-compiled DLL for Windows x64

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

Re: [sqlite] Reducing index size

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, >Donald > > > > > > On Mon, Jul 30, 2018 at 4:32 AM Eric Grange wrote:

Re: [sqlite] Reducing index size

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 for the rest

Re: [sqlite] Reducing index size

@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

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

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

Re: [sqlite] Reducing index size

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.amazo

[sqlite] Reducing index size

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

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

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, 2018 at 4:46

[sqlite] Quick way to figure SQLite database block size per table & indexes

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.

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

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:

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

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 works. On Tue, Jun

[sqlite] Performance issue with left join in 3.24 compared with 3.22

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

Re: [sqlite] How to Handle BigInt

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

Re: [sqlite] sqlite3_column_decltype and affinity

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 <

Re: [sqlite] sqlite3_column_decltype and affinity

to be able to recover that info whe it is provide through a cast. 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

[sqlite] sqlite3_column_decltype and affinity

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

Re: [sqlite] The upcoming 3.23.0 release

> 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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

Tue, Jan 9, 2018 at 11:44 AM, Dominique Devienne <ddevie...@gmail.com> wrote: > 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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

<slav...@bigfraud.org> wrote: > 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

[sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

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

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

> 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

Re: [sqlite] Proof that a line has been modified

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,

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

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

Re: [sqlite] Version 3.20.0 coming soon...

> 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

Re: [sqlite] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

der 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 <t...@clothears.org.uk> wrote: > On 28 Jun 2017 at 14:20, Rowan Worth <row...@dug.com> wrote: > > > On

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

> 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

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

> 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

Re: [sqlite] UTF8-BOM not disregarded in CSV import

>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

Re: [sqlite] UTF8-BOM not disregarded in CSV import

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

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

> 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

Re: [sqlite] RIGHT JOIN! still not supported?

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

Re: [sqlite] More built-in functions for basic math

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

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

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

Re: [sqlite] SQLite 3.16.0 enters testing

> 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

Re: [sqlite] Typical suffixes of sqlite database files

> 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

Re: [sqlite] Fastest way to backup a "live" database

Eric 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); >

Re: [sqlite] Fastest way to backup a "live" database

Eric 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

Re: [sqlite] Fastest way to backup a "live" database

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:

Re: [sqlite] Fastest way to backup a "live" database

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

[sqlite] Fastest way to backup a "live" database

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

Re: [sqlite] Ideas for optional json fields?

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

Re: [sqlite] Ideas for optional json fields?

t;:"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 <rsm...@rsweb.co.za> wrote: > > On 2016/09/26 11:15 AM, Eric Grange wrote: &g

[sqlite] Ideas for optional json fields?

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',

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

t; > 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

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

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

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

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.

Re: [sqlite] Fastest way to add many simple rows to a table?

> 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

Re: [sqlite] Fastest way to add many simple rows to a table?

rings. 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 > wrote: > Richard Hipp <d...@sqlite.org> wrote: > > > On 5

[sqlite] Fastest way to add many simple rows to a table?

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

[sqlite] Are BLOB stored inline with the rest of the record fields?

inal 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 stored inline wi

[sqlite] Are BLOB stored inline with the rest of the record fields?

> 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

[sqlite] Are BLOB stored inline with the rest of the record fields?

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

[sqlite] Performance comparison between SQLite and SQL Server?

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

[sqlite] json1 not escaping CRLF characters

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, > >> > >&

[sqlite] json1 not escaping CRLF characters

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

[sqlite] Backing up SQLite DB with unbuffered I/O

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

[sqlite] SQLite not using primary key index anymore

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

[sqlite] SQLite not using primary key index anymore

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

[sqlite] SQLite not using primary key index anymore

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,

[sqlite] Multi-table index ersatz?

ot; 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, Dan Kennedy w

[sqlite] Multi-table index ersatz?

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, &

[sqlite] Multi-table index ersatz?

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

[sqlite] Appropriate Uses For SQLite

> 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,

[sqlite] Registering Virtual Tables on the spot (when needed)

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

[sqlite] FTS3 performance rowid vs docid

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

[sqlite] Virtual Tables performance

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