Re: [sqlite] unexpected sqlite_busy behaviour within transactions

Hi, A busy_timout is honored by write commands outside of transactions and by single write commands inside transactions, but not if preceded by a read command. I did not find this behaviour in the documentation, thus it might be a bug. It's documented in the description of sqlite3_busy_handl

Re: [sqlite] Size limits

Hi Andy, What are reasonable limits for size Sqlite3 database file and large blobs? Here are some benchmarks on BLOB performance: https://sqlite.org/intern-v-extern-blob.html However, see the note here in section 4.1 about using the incremental BLOB I/O routines so that you don't thrash yo

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

Hi, (3) Each database connection uses about 72 KB less heap space. How much is this as a percentage of a freshly initialised connection and how much as a percentage of some kind of "typical" connection (is there even one?)? andy...@ashurst.eu.org http://www.ashurst.eu.org/ http://www.g

Re: [sqlite] Request: Combining skip-scan with 'max' optimization

Hi, I hadn't seen this thread when I posted my recent thread on optimising MAX aggregates but I suspect this could help my case as well. At the moment I'm trying to limit the amount of data that the aggregate query has to visit in order to keep latency low but this optimisation would give me

Re: [sqlite] Optimising query with aggregate in subselect.

Hi, Thanks to everyone who helped with this! I'll try some stuff out and see if I can get things efficient, fast *and* simple. :-) "There's a small sidenote (that I'm too lazy too find right now) in the select docs that mentions that, in case of using min or max as aggregate, the non-aggr

Re: [sqlite] Optimising query with aggregate in subselect.

Hi, In past attempts at improving query performance these have been added to encourage it to use an index that it can do a SCAN thru' rather than the table that it would need to do a SEARCH thru'. SQLite is not using the PRIMARY INDEX to immediately locate the appropriate row, but is actual

Re: [sqlite] Optimising query with aggregate in subselect.

Hi, Did you try retrieving the data "directly" or do you need the subselect in order to maintain compatibility with other SQL dialects that are no longer able to retrieve data from the row on which the max was found? Thanks Keith! I understood that selecting other columns during an aggregat

Re: [sqlite] Optimising query with aggregate in subselect.

Hi, INNER JOIN "entrys" ON 1 = "entrys"."log-id" AND "specific-entrys"."key" = "entrys"."key" AND "user" = "entrys"."region" AND "specific-entrys"."entry-number" = "entrys"."entry-number" AND "entrys"."key" > "G" I can't solve your problem, but the PRIMARY KEY for "entrys" is ("log-id", "entr

Re: [sqlite] Optimising query with aggregate in subselect.

Hi, ORDER BY "key" DESC This should be ASC, not DESC: I've been working on versions of the query that can go forwards and backwards and made an editor snafu when writing the eMail. Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF

[sqlite] Optimising query with aggregate in subselect.

Hi, I'm trying to implement a "streaming" version of the classic "select the latest version of a record" query. By "streaming" I mean a query that executes by streaming what it needs out of tables and indexes as it needs it rather than using temporary b-trees or materializing anything up fr

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

Hi, I'm having a situation where the results of a large SELECT operation are apparently too big to fit in memory. Obviously I could jerry-rig something to work around this, but I have a vague recollection that SQLite provides a nice way to get the results of a query in "chunks" so that the m

Re: [sqlite] [SPAM?] Is WAL mode serializable?

Hi, Thanks for the detailed explanation of how it works. What I meant to ask was "is there really a difference in the *semantics*?". i.e. from the user perspective, can databases in the two different modes end up in different states? My understanding is NO, as long as the same transactions com

Re: [sqlite] Is WAL mode serializable?

Hi, Is that last quote just imprecise writing or is there really a difference between SQLite's Snapshot Isolation in WAL mode and its Serializable isolation in ROLLBACK mode? Yes, there is a difference. ...sorry, it seems it was my turn to do some sloppy writing! Thanks for the detailed ex

[sqlite] Is WAL mode serializable?

Hi, I'm reading the docs: https://sqlite.org/isolation.html ...and I need some clarification! It is easy to see that ROLLBACK mode is SERIALIZABLE because all concurrent readers have to leave before a PENDING lock is upgraded to an EXCLUSIVE lock. However, the wording for WAL mode is conf

Re: [sqlite] wal

Hi, THe most persists after it has been set. The file comes and goes as needed. I seem to remember there are some caveats in the manual about what directory permissions are required and what happens when you can write the journal file and the database file but not the directory that contains t

Re: [sqlite] Please explain multi-access behaviour to me

Hi, Actually, you're better off setting a timeout rather than handling retry in your own code: As I understand it, SQLITE_BUSY can occur in two situations: one where busy looping will fix it and one where it won't. Using the busy_timeout handles

Re: [sqlite] Please explain multi-access behaviour to me

Hi, A connection doesn't need to check locks on every statement - only when it tries to spill to disk, most commonly during commit. I think I understand what you wrote. So the bit of my program can think that its changes were written to the database and only later might my program find tha

Re: [sqlite] Have SQLite handle values of my own type

Hi, IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the four parts is always three digits long. IPv4 addresses are 32bit unsigned integers internally. The dotted -quad notation is 4 8bit unsigned integers that get concatenated together. If you store them as a 32bit i

Re: [sqlite] What is the recommended way to write to views?

Hi, Like how do I get ROWID of the "just inserted" record in A to insert into C properly. I am thinking "should I make transactions, but we don't have variables"... and going round-and-round without solution. Is last_insert_rowid what you're looking for? https://www.sqlite.org/c3ref/last_inse

Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

Hi, For example, would I do this: Connect to the DB [ Pass the command to save: ]SAVEPOINT 'current' [ User choices are processed: SELECT and UPDATE statements ] [ if error or user changes their mind ]    ROLLBACK TRANSACTION TO SAVEPOINT 'current' [ otherwise... upon success ]    RELEASE S

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

Hi, Integer unix timestamps are only accurate to one second, where ISO8601 (at least as implemented by SQLite) can go to 1 millisecond. Also you have to know the epoch to interpret a unix timestamp - not everybody uses 1970-01-01 00:00:00. Will people be able to figure out what the field value

Re: [sqlite] SQLite slow when lots of tables

Hi, what happens if I put all data in a single table and this table become very huge (for example millions of rows)? Will I have same performace problems? The INSERTing profile has been covered by others but I'll just add something about SELECT as well. It depends on how you query it. i.e

Re: [sqlite] Query Planning Knowledge

Hi, I could use the inner join for the "entrys" join and the "items" join but not the "entry-items" join because each entry can have more than one item. WITH a(id, name) AS (VALUES (1, 'A')), b(id, name) AS (VALUES (1, 'B1'), (1, 'B2')) SELECT * FROM a INNER JOIN b USING (id); 1|

Re: [sqlite] Query Planning Knowledge

Hi David, Thanks for your thoughtful reply. Can't go into as much detail as you. But a couple comments. "primary key unique" is redundant, and will actually create a redundant unique index. Are you refering to the CREATE TABLE clauses for the "items" and "registers" tables? I appear to

[sqlite] Query Planning Knowledge

Hi, I'm having some problems understanding what the query planner is doing and how to convince it to work in the way I want. Sorry that this is such a long and involved eMail. I've tried to describe my problem and show the steps I have taken in debugging it. I have made some effort to presen