Re: [sqlite] sqlite3_serialize / sqlite3_deserialize (3.23.0 draft)

Olivier Mascia wrote: > What are use cases for these sqlite3_serialize / deserialize? A common question is "how do I save my in-memory database to disk?" (The common answer is "use the backup API" or "use an on-disk DB in the first place".) Anyway, there are cases where you have a blob

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

2018-03-16 16:37 GMT+01:00 Richard Hipp : > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? > > I just need a single integer, the count of uses of the AUTOINCREMENT > in your overall schema. You

Re: [sqlite] sqlite3_serialize / sqlite3_deserialize (3.23.0 draft)

On 3/18/18, Olivier Mascia wrote: > Hello, > > What are use cases for these sqlite3_serialize / deserialize? > I understand what they do, from the documentation. > Though I'd like to understand what typically they were introduced for? > (Always trying to learn something here).

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 (Out of 3 databases. ) On 16 March 2018 at 15:37, Richard Hipp wrote: > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? > > I just need a single integer, the count of uses of the

Re: [sqlite] sqlite3_serialize / sqlite3_deserialize (3.23.0 draft)

> Le 18 mars 2018 à 11:31, Richard Hipp a écrit : > >> What are use cases for these sqlite3_serialize / deserialize? >> I understand what they do, from the documentation. >> Though I'd like to understand what typically they were introduced for? >> (Always trying to learn

Re: [sqlite] sqlite3_serialize / sqlite3_deserialize (3.23.0 draft)

On 3/18/18, Olivier Mascia wrote: > > Is the serialized format quite compact, or full > of void unused space on 'pages'? (that obviously a good external compression > would get rid of). Or said differently, how far or close is the serialized > format to the on-disk SQLite file

[sqlite] possible bug: select clause column alias shadowing

Hi, I just came across a strange behaviour in sqlite3 (3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d). Consider the following test case:     CREATE TABLE test (name varchar);     INSERT INTO test VALUES ("foo"),("bar"); -- Returns a single row

Re: [sqlite] possible bug: select clause column alias shadowing

On 18 Mar 2018, at 9:31am, Moritz Bruder wrote: > I'm not exactly sure what the SQL standard says As best I can find, SQL92 does not specify what happens when you choose an AS clause giving a value name the same as a column. It doesn't go into much detail at all

[sqlite] sqlite3_serialize / sqlite3_deserialize (3.23.0 draft)

Hello, What are use cases for these sqlite3_serialize / deserialize? I understand what they do, from the documentation. Though I'd like to understand what typically they were introduced for? (Always trying to learn something here). :) -- Best Regards, Meilleures salutations, Met vriendelijke

Re: [sqlite] How to optimise a somewhat-recursive query?

On 18 Mar 2018, at 21:48, Jonathan Moules wrote: > CREATE TABLE lookups ( > url_id INTEGER REFERENCES urls (url_id), > retrieval_datetime DATETIME, > error_code INTEGER, > is_generic_flag BOOLEAN -- about one in

Re: [sqlite] possible bug: select clause column alias shadowing

Simon Slavin wrote: > As best I can find, SQL92 does not specify what happens when you choose > an AS clause giving a value name the same as a column. | 7.3 | | Function | |Specify a table or a grouped table. | | Format | | ::= | | [ ] | [ ] | [ ] |

Re: [sqlite] How to optimise a somewhat-recursive query?

Drop the view, add a new column “last_retrieval_datetime” to the table urls, and whenever fetch a page, update this column 发自我的 iPhone > 在 2018年3月19日，06:15，Clemens Ladisch 写道： > > I have not looked at the schema and queries in detail. > But at a first glance: > >> CREATE

Re: [sqlite] How to optimise a somewhat-recursive query?

I have not looked at the schema and queries in detail. But at a first glance: > CREATE VIEW v_most_recent_lookup_per_url AS > ... > ORDER BY url_id; Drop the ORDER BY; it is useless in a view used in another query, and just might slow things down. > 011SEARCH TABLE lookups

Re: [sqlite] How to optimise a somewhat-recursive query?

Hi Both, Thanks for your thoughts. > SQLite has no DATETIME or BOOLEAN datatype I'm aware that DATETIME (and BOOLEAN) isn't an actual datatype in SQLite, but it functions exactly as you'd expect - I've used them extensively in the past and never had a problem - I'm actually storing ISO8601

Re: [sqlite] How to optimise a somewhat-recursive query?

On 18 Mar 2018, at 11:13pm, Jonathan Moules wrote: > Given there's such a small amount of data at this point, I suspect the issue > is more related to the recursion. I've tried creating these two indexes to > facilicate that Nice idea but I can see why it's not

Re: [sqlite] How to optimise a somewhat-recursive query?

Thanks Simon and Quan. I'm not sure it's the view itself per-se - It takes 0.000s (time too small to measure) for just the full View to be run on this dataset. It turns out the problem is simpler than that and no data changes are needed. I did consider Quan Yong Zhai's option and gave it a

Re: [sqlite] Query speed Regression: 3.15 much faster than 3.22 (Was: How to optimise a somewhat-recursive query? )

On 3/18/18, Jonathan Moules wrote: > > I can provide a small replication database if desired. Please do. Send it as an attachment directly to me. Also please send the exact text of the query that is running slowly. -- D. Richard Hipp d...@sqlite.org

[sqlite] Query speed Regression: 3.15 much faster than 3.22 (Was: How to optimise a somewhat-recursive query? )

Hi List, So, I thought I'd solved my little problem, but upon testing it in my application it subjectively didn't seem any faster. I upgraded the SQLite in my IDE to 3.22, and it is confirming my suspicions. It turns out that when I run the below in 3.22, it takes about 0.150s. But in 3.15

[sqlite] How to optimise a somewhat-recursive query?

Hi List, I have a query that's quite slow on a small sample of data and I'd like to optimise it before larger datasets get in there. With just 1000 rows in each table I'm currently seeing query times of ~0.2 seconds. My attempts to optimise it so far by putting in carefully thought out

[sqlite] Feat Req Get Aliased Table Name.

https://sqlite.org/c3ref/column_database_name.html Source Of Data In A Query Result The names returned are the original un-aliased names of the database, table, and column. Okay. and there's... https://sqlite.org/c3ref/column_name.html These routines return the name assigned to a particular

Re: [sqlite] UPSERT

On 16 March 2018 at 21:44, Paul wrote: > A few years back I've been asking the same question. To be honest, there's > no more > efficient alternative, than the one that can be implemented within library > itself. > Both performance-wise and productivity-wise. > > Doing hacks with

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

5/10 1/11 5/10 Always in conjunction with INTEGER PRIMARY KEY fwiw. Also the following command is perhaps more portable: sqlite3 yourfile.db .schema | grep -ic autoincrement The sqlite3 shell on my system is too old to understand .schema --indent and doesn't output anything so there's