Re: [sqlite] Performances and Foreign keys

On 24 Apr 2017, at 3:20pm, Clemens Ladisch wrote: > Bubu Bubu wrote: >> Can someone tell me if there can really be performance issues when one uses >> foreign keys in their database? > > The documentation > warns against a

Re: [sqlite] Best practices for huge batch inserts with WAL?

Do you have some indices on the table? Drop them before the huge insert and then recreate again. It is faster. On Mon, 24 Apr 2017 11:00:00 -0700 Jens Alfke wrote: > I’m importing a large data set with a lot of rows — an entire Wikipedia dump, > about 60GB, one article per

Re: [sqlite] Best practices for huge batch inserts with WAL?

What I'd do: 1) Look at the indices, and make sure the input is sorted to insert in index order. Also drop any unnecessary indices and add them back at the end. [Read the code for vacuum to see what kinds of things make sense to defer.] 2) Bump up the cache a lot. Inserting in sorted order

Re: [sqlite] Performances and Foreign keys

Simply make some tests? The experiment is the only measure for the truth. Personally I am using SQLite for my project AsmBB (web forum software in assembly language). The project widely uses FK in order to provide DB consistency. It was tested during several "slashdot effect" kind of events

Re: [sqlite] Performances and Foreign keys

Another situational space/speed helper I found for when you have a sparsely populated foreign key field is that the child key index can be a partial index and still work. create table child (id primary key, parentID references parent, stuff);--parentID is nullable I had cases where parentID

[sqlite] Best practices for huge batch inserts with WAL?

I’m importing a large data set with a lot of rows — an entire Wikipedia dump, about 60GB, one article per row — into a brand new SQLite database in WAL mode. What’s the fastest way to import it? I started with one big transaction, but noted that (of course) the WAL file was growing rapidly

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

On Sun, 23 Apr 2017 21:31:42 +0100 Simon Slavin wrote: > If you don?t understand what you?re doing, hire an experienced > programmer. Ah, but you don't know what you don't know. After all, 90% of programmers rate themselves "above average". When I first heard of "SQL

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

On 24 Apr 2017, at 2:33am, Keith Medcalf wrote: > [history] That’s very interesting. I’m remembering the first DBMS language I used on desktop computers rather than mainframes or minis. It was something called "Q-Pro 4" and included both database commands and user

Re: [sqlite] 0x11C: automatic index warning for CTEs ?

Change UNION ALL to UNION. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Howard Kapustein > Sent: Monday, 24 April, 2017 15:46 > To: SQLite mailing list > Subject: Re:

Re: [sqlite] Best practices for huge batch inserts with WAL?

Scott Hess wrote: > WRT #3, you could also consider journal_mode off (or memory, if your code > requires transactions to work right). In that case, the database state is > indeterminate if you have an app-level crash, but you should be fine if you > make it to the end. It would be a better idea

Re: [sqlite] 0x11C: automatic index warning for CTEs ?

>but for CTEs/views/subqueries, you might have to change the query itself, or >the database schema, or determine that the automatic index is the best choice >in your situation. Yes. The question is how? The CTE's doing a SELECT _PackageID FROM Package where _PackageID is a primary key. If I

Re: [sqlite] Multiple sessions, page sizes and cache

On Fri, Apr 21, 2017 at 5:18 PM, Kim Gräsman wrote: > > I have a nagging suspicion that the tempdb is allocated from the > private heap (we've set pragma temp_store=memory) -- what are the > characteristics of that database? Can I forecast how large it will be? The

[sqlite] Performances and Foreign keys

Hi everyone, Foreign keys have been implemented in sqlite since 3.6.19. My boss has always been reluctant to use this mechanism in our development under the pretext of performance loss. He told me he read that somewhere once, but he can't recall precisely the reasons that lead him think that.

Re: [sqlite] Performances and Foreign keys

On 2017/04/24 2:31 PM, Bubu Bubu wrote: Hi everyone, Foreign keys have been implemented in sqlite since 3.6.19. My boss has always been reluctant to use this mechanism in our development under the pretext of performance loss. He told me he read that somewhere once, but he can't recall

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

On Mon, Apr 24, 2017 at 3:33 AM, Keith Medcalf wrote: > > I’m curious about binding as an idea. [...] > [...] The EXEC SQL interface has all but disappeared in most languages > [...] Oracle still supports https://en.wikipedia.org/wiki/Pro*C but that's pure client-side,

Re: [sqlite] Performances and Foreign keys

On Mon, Apr 24, 2017 at 2:31 PM, Bubu Bubu wrote: > Foreign keys have been implemented in sqlite since 3.6.19. My boss has > always been reluctant to use this mechanism in our development under the > pretext of performance loss. He told me he read that somewhere once, but he

Re: [sqlite] Performances and Foreign keys

Bubu Bubu wrote: > Can someone tell me if there can really be performance issues when one uses > foreign keys in their database? The documentation warns against a case where some operations can be slow if you do not have an index on the child

[sqlite] Lemon: Simple recursive rule causes assertion failed: stateno <= YY_SHIFT_COUNT

This lemon bug was reported about 6 months ago: 8< %include { #include #include #include #include "lemon-bug.h" } %code { int main() { void *pParser; pParser = ParseAlloc(malloc); if (!pParser) { printf("out of