[sqlite] Problems with pragma journal_mode

2015-04-20 Thread Janke, Julian
'EXPLAIN PRAGMA journal_mode=WAL' returns 2 rows 0: 0 Init 0 0 0 1: 1 Halt 0 0 0 Is it the result of what you expected? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent:

[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints

2015-04-20 Thread Nicolas Boullis
Hi, Disclaimer: I am a PostgreSQL user and consider myself a SQLite newbie. I have a program that does mostly? SELECT requests, and it is very slow. But I then figured out that, if I rebuild my SQLite database without PRIMARY KEY/UNIQUE constraints, the program runs much faster (no

[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints

2015-04-20 Thread Clemens Ladisch
Nicolas Boullis wrote: > I have a program that does mostly? SELECT requests, and it is very > slow. But I then figured out that, if I rebuild my SQLite database > without PRIMARY KEY/UNIQUE constraints, the program runs much faster > (no measurement yet, but I?d say at least 10? faster). SQLite

[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints

2015-04-20 Thread Nicolas Boullis
Hi, On Mon, Apr 20, 2015 at 11:18:56AM +0200, Clemens Ladisch wrote: > > SQLite does not keep statistics automatically. Run ANALYZE. Thanks for the explanation. I just performed some measurements. When I use the database with PRIMARY KEY/UNIQUE constraints, the program uses 540s of CPU time

[sqlite] Problems with pragma journal_mode

2015-04-20 Thread Richard Hipp
On 4/20/15, Janke, Julian wrote: > 'EXPLAIN PRAGMA journal_mode=WAL' returns 2 rows > > 0: 0 Init 0 0 0 > 1: 1 Halt 0 0 0 > You should get this: addr opcode p1p2p3p4 p5 comment - - -- - 0 Init

[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints

2015-04-20 Thread Richard Hipp
On 4/20/15, Nicolas Boullis wrote: > Hi, > > On Mon, Apr 20, 2015 at 11:18:56AM +0200, Clemens Ladisch wrote: >> >> SQLite does not keep statistics automatically. Run ANALYZE. > > Thanks for the explanation. I just performed some measurements. > > When I use the database with PRIMARY KEY/UNIQUE

[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints

2015-04-20 Thread Eduardo Morras
On Mon, 20 Apr 2015 12:01:59 +0200 Nicolas Boullis wrote: > Hi, > > On Mon, Apr 20, 2015 at 11:18:56AM +0200, Clemens Ladisch wrote: > > If that does not help, we'll have to look at the actual queries (and > > their EXPLAIN QUERY PLAN output). > > That would certainly help, but I would have to

[sqlite] Problems with pragma journal_mode

2015-04-20 Thread Janke, Julian
SELECT sqlite_source_id(); shows: 2015-02-25 13:29:11 9d6c1880fb75660bbabd693175579529785f8a6b And PRAGMA compile_options; shows 0 -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp

[sqlite] Problems with pragma journal_mode

2015-04-20 Thread Richard Hipp
On 4/20/15, Janke, Julian wrote: > > PRAGMA compile_options; shows 0 This makes me think that the list of compile-time options you showed us earlier is incomplete: -DSQLITE_ENABLE_MEMSYS5 -DSQLITE_ENABLE_8_3_NAMES=2 -DSQLITE_THREADSAFE=0 -DSQLITE_OS_OTHER=1 -DSQLITE_ENABLE_API_ARMOR

[sqlite] Problems with pragma journal_mode

2015-04-20 Thread Janke, Julian
I'm pretty sure that they are completely. Later this discussion I added -DSQLITE_DEFAULT_LOCKING_MODE=1 There are only a few non-SQLite-specific options still available. Tell me if I'm wrong, but I do not think that are the cause of my problem. -mcpu=603e -fno-common -msdata=none

[sqlite] Problems with pragma journal_mode

2015-04-20 Thread Richard Hipp
If the compile-time options you have shown are complete, then the PRAGMA command ought to be working. But clearly PRAGMA is not working. Can you do an experimental build that omits all of your -DSQLITE options and see if "PRAGMA journal_mode;" and "PRAGMA compile_options;" work then? What about

[sqlite] Corruption and TEMPORARY tables

2015-04-20 Thread Joe Pasquariello
On 4/19/2015 7:08 PM, Richard Hipp wrote: >> Since power loss occurs relatively >> frequently, do you recommend synchronous=1 (Normal) or 2 (Full)? > Either will be fine. >> Can a power failure during >> a COMMIT to a TEMPORARY table in memory, with synchronous=0, result in >> corruption > No.

[sqlite] building SQLite DLL with Visual C++

2015-04-20 Thread Jay Smith
I am new to SQLite. I have a book I am learning from and I am a novice. I have a fairly good handle on SQL I want to include SQLite in my VS2012 program. I am attempting to run " LIB /DEF:sqlite3.def " I get error This error "LIB is not recognized as an internal or external command." What am