[sqlite] Query optimizer not doing so well with my queries

Hi, I'm a big fan of SQLite, but I've been having some terrible speed problems when doing joins between subqueries. Strangely, if I join twice (first to the main table and then to its subquery), I can get the same answer in tenths of seconds and not minutes - or one thousand times faster. For ex

[sqlite] Best way to handle time slicing of SQL data.

terested in. I guess there's no extra storage as rowid would exist anyway. 2) Can I auto fill Id to UID on insert instead of having to do two operations? 3) Am I on track or is there a better way to approach this problem? Thanks in advance for any feedback. -- Richard Warburton

Re: [sqlite] Transactions for read operations

Thanks to those who responded to my query. Simon: It will be easier to code if every page commits, regardless of whether any write operations occurred (and rollback only error) - so your answer pleased me greatly. Keith: Yes, you're right. I'm not passing a single database connection around, I'm

[sqlite] Transactions for read operations

n error, commit otherwise. However, given that the page will read many times more often than it writes, I'm wondering if this is a bad idea. Thoughts? Thanks. -- Richard Warburton - MSc(Hons), PGDipSci, BE(Hons) +64 9 377-2881 ext 9 Senior Developer - http://www.skagerraksoftware.com/ Skagerrak Softw

Re: [sqlite] Prevent non-Integers going into int fields.

y much the same a conversion via affinity, producing a consistent set of results. And this has the advantage of round that text integers will still be allowed. Peter On 3 August 2014 16:52, Richard Warburton wrote: > Whilst any type going into a field is often seen as a big plus, there a

[sqlite] Prevent non-Integers going into int fields.

Whilst any type going into a field is often seen as a big plus, there are occasions where this may be undesirable. For such occasions, I'm currently thinking that: NOT NULL CHECK( ROUND(fieldname)=fieldname ) looks ok, but I'm wondering if there's a case that still gets through or if there's a be

Re: [sqlite] Only allow child record if another field in parent is false.

Hi, Thanks to those who gave feedback. It looks like that it will be best left to business rules. In the event that enrolmentItems are attached to leaver enrolments (due to a coding error), they will be ignored. Thanks. ___ sqlite-users mailing list s

[sqlite] Only allow child record if another field in parent is false.

Hi, Consider: CREATE TABLE enrolment ( id INTEGER PRIMARY KEY, child INT NOT NULL REFERENCES child (id), start INT NOT NULL, leaver INT NOT NULL DEFAULT 0 CHECK (leaver BETWEEN 0 AND 1), -- BOOL ); CREATE TABLE enrolmentItem ( id INTEGER PRIMARY KEY, enrolment INT NOT NULL