[sqlite] SQLite slows by a factor of 1000 after foreign key

2015-10-01 Thread Simon Slavin
On 1 Oct 2015, at 6:36pm, Andrew Cunningham wrote: > A FOREIGN KEY violation that occurs in IMMEDIATE mode, though it causes an > error, is often not actually a real error. Sometimes the "VIOLATION" (of > referential integrity) is corrected at a later point ( order of operations > is

[sqlite] SQLite slows by a factor of 1000 after foreign key

2015-10-01 Thread Klaas V
Andrew Cunningham wrote: >I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY >violation occurs, SQLite will continue to run, but performance slows down >by about 1000x. Breaking into the code shows that it seems to be >continually re-reading the database. When I finally

[sqlite] Performance issue with CTE

2015-10-01 Thread Kees Nuyt
On Thu, 1 Oct 2015 13:40:23 +0200, Clemens Ladisch wrote: > OFFSET is inefficient because the database still has to compute all the > rows before skipping over them. > > To do paging, remember the first and last date values on the page, and > for the previous/next page, just continue from there:

[sqlite] Performance issue with CTE

2015-10-01 Thread Clemens Ladisch
Philippe Riand wrote: > I have a table with 500,000+ records. The table has a date column, > that I?m using to sort my queries (the columns has an index). Simple > queries on the table work very well, using ORDER BY, LIMIT & OFFSET. > I?m actually extracting ?pages? of rows that I?m displaying in

[sqlite] Performance issue with CTE

2015-10-01 Thread E.Pasma
Op 1 okt 2015, om 04:10 heeft Philippe Riand wrote: > I have a table with 500,000+ records. The table has a date column, > that I?m using to sort my queries (the columns has an index). Simple > queries on the table work very well, using ORDER BY, LIMIT & OFFSET. > I?m actually extracting

[sqlite] SQLite slows by a factor of 1000 after foreign key violation

2015-10-01 Thread Andrew Cunningham
I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY violation occurs, SQLite will continue to run, but performance slows down by about 1000x. Breaking into the code shows that it seems to be continually re-reading the database. When I finally get to COMMIT , an exception is

[sqlite] Best Practices

2015-10-01 Thread R.Smith
On 2015-10-01 01:08 AM, eluken at pentarch.org wrote: > Well, when you put it like that :) > > I might allow the user to change the name based on the game name. But > there again, special characters. Ideally, the functionality would > allow for multiple databases. > > I also need to figure

[sqlite] Best Practices

2015-10-01 Thread Simon Slavin
On 1 Oct 2015, at 12:08am, eluken at pentarch.org wrote: > Well, when you put it like that :) > > I might allow the user to change the name based on the game name. But there > again, special characters. Ideally, the functionality would allow for > multiple databases. You could make a rule