[sqlite] Problems with pragma journal_mode
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 -fno-jump-tables -fno-section-anchors -fno-merge-constants -fno-builtin -nostdlib -Werror-implicit-function-declaration -Wconversion -fstack-usage -std=c99 -c -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Montag, 20. April 2015 15:55 To: General Discussion of SQLite Database Subject: Re: [sqlite] Problems with pragma journal_mode 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 -DSQLITE_DEFAULT_MMAP_SIZE=0 -DSQLITE_TEMP_STORE=0 -DSQLITE_DEFAULT_CACHE_SIZE=500 Please double-check to ensure that you do not have additional SQLITE defines stuck in a configuration file someplace. > > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of > Richard Hipp > Sent: Montag, 20. April 2015 12:18 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Problems with pragma journal_mode > > 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 0 0 000 > 1 JournalMode0 1 500 > 2 ResultRow 1 1 000 > 3 Halt 0 0 000 > > What does: "PRAGMA compile_options;" and "SELECT sqlite_source_id();" show? > > > >> Is it the result of what you expected? >> >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org >> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of >> Richard Hipp >> Sent: Freitag, 17. April 2015 16:59 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Problems with pragma journal_mode >> >> On 4/17/15, Janke, Julian wrote: >>> Hello, >>> Thanks for your reply. >>> >>> I changed my code again: >>> >>> 1) rc = sqlite3_open(dbPath, ); --> SQLITE_OK >>> 2) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;", >>> testCallbackPrint, 0, ); >>> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, >>> , NULL); >>> --> SQLITE_OK >>> 4) rc = sqlite3_step(stmt); --> SQLITE_DONE >> >> I do not understand this. "PRAGMA journal_mode" should always give a >> return value, even when it fails. sqlite3_step() should have >> returned SQLITE_ROW. >> >> Please try instead, "EXPLAIN PRAGMA journal_mode=WAL". Verify that >> you get multiple rows of output in that case. >> >> >>> 5) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;", >>> testCallbackPrint, 0, ); >>> 6) txt = sqlite3_column_text (stmt, 0); --> returns an empty string >>> 7) rc = sqlite3_finalize(stmt); --> SQLITE_OK >>> 8) rc = sqlite3_close(db); --> SQLITE_OK >>> >>> Repeated the same steps with >>> >>> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode;", 24, , >>> NULL); --> SQLITE_OK >>> >>> To my knowledge, at line 4 SQLITE_ROW instead of SQLITE_DONE should >>> be returned. >>> >>> "These routines may only be called when the most recent call to >>> sqlite3_step() has returned SQLITE_ROW and neither sqlite3_reset() >>> nor >>> sqlite3_finalize() have been called subsequently." >>> >>> It looks as if all PRAGMA instructions are completely ignored, since >>> the query for the status of the JOURNAL_MODE returns no result. >>> >>> Is it possible that I have turned off PRAMAs by anything or >>> something is missing, so PRAMAs can run? >>> >>> Thanks for your help >>> >>> >>> -Original Message- >>> From: sqlite-users-bounces at mailinglists.sqlite.org >>> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of >>> Simon Slavin >>> Sent: Donnerstag, 16. April 2015 17:56 >>> To: General Discussion of SQLite Database >>> Subject: Re: [sqlite] Problems with pragma journal_mode >>> >>> >>> On 16 Apr 2015, at 2:33pm, Janke, Julian >>> >>> wrote: >>> rc = sqlite3_step(stmt); --> returns SQLITE_DONE >>> >>> After the above two lines, print the value returned by >>> >>> sqlite3_column_text(stmt, 0) >>> >>> I'm not good at C off the top of my head but I think it's something >>> like >>> >>> - >>> >>> const unsigned char * theText; >>> >>> [...] >>> >>> rc =
[sqlite] Problems with pragma journal_mode
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
Sent: Montag, 20. April 2015 12:18
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with pragma journal_mode
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 0 0 000
1 JournalMode0 1 500
2 ResultRow 1 1 000
3 Halt 0 0 000
What does: "PRAGMA compile_options;" and "SELECT sqlite_source_id();" show?
> Is it the result of what you expected?
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
> Richard Hipp
> Sent: Freitag, 17. April 2015 16:59
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problems with pragma journal_mode
>
> On 4/17/15, Janke, Julian wrote:
>> Hello,
>> Thanks for your reply.
>>
>> I changed my code again:
>>
>> 1) rc = sqlite3_open(dbPath, ); --> SQLITE_OK
>> 2) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;",
>> testCallbackPrint, 0, );
>> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, ,
>> NULL);
>> --> SQLITE_OK
>> 4) rc = sqlite3_step(stmt); --> SQLITE_DONE
>
> I do not understand this. "PRAGMA journal_mode" should always give a
> return value, even when it fails. sqlite3_step() should have returned
> SQLITE_ROW.
>
> Please try instead, "EXPLAIN PRAGMA journal_mode=WAL". Verify that
> you get multiple rows of output in that case.
>
>
>> 5) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;",
>> testCallbackPrint, 0, );
>> 6) txt = sqlite3_column_text (stmt, 0); --> returns an empty string
>> 7) rc = sqlite3_finalize(stmt); --> SQLITE_OK
>> 8) rc = sqlite3_close(db); --> SQLITE_OK
>>
>> Repeated the same steps with
>>
>> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode;", 24, ,
>> NULL); --> SQLITE_OK
>>
>> To my knowledge, at line 4 SQLITE_ROW instead of SQLITE_DONE should
>> be returned.
>>
>> "These routines may only be called when the most recent call to
>> sqlite3_step() has returned SQLITE_ROW and neither sqlite3_reset()
>> nor
>> sqlite3_finalize() have been called subsequently."
>>
>> It looks as if all PRAGMA instructions are completely ignored, since
>> the query for the status of the JOURNAL_MODE returns no result.
>>
>> Is it possible that I have turned off PRAMAs by anything or something
>> is missing, so PRAMAs can run?
>>
>> Thanks for your help
>>
>>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org
>> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
>> Simon Slavin
>> Sent: Donnerstag, 16. April 2015 17:56
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Problems with pragma journal_mode
>>
>>
>> On 16 Apr 2015, at 2:33pm, Janke, Julian
>> wrote:
>>
>>> rc = sqlite3_step(stmt);
>>> --> returns SQLITE_DONE
>>
>> After the above two lines, print the value returned by
>>
>> sqlite3_column_text(stmt, 0)
>>
>> I'm not good at C off the top of my head but I think it's something
>> like
>>
>> -
>>
>> const unsigned char * theText;
>>
>> [...]
>>
>> rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, ,
>> NULL);
>>
>> rc = sqlite3_step(stmt);
>> theText = sqlite3_column_text (stmt, 0); printf ("text returned:
>> %s", theText);
>>
>> rc = sqlite3_finalize(stmt);
>>
>> -
>>
>> Hope if I got it wrong someone else will tell you. By the way, you
>> can also check the values returned from sqlite3_finalize() and
>> sqlite3_close() since they can tell you useful things if something
>> went wrong.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> Firma: Capgemini Deutschland GmbH
>> Aufsichtsratsvorsitzender: Antonio Schnieder ? Gesch?ftsf?hrer: Dr.
>> Michael Schulte (Sprecher) ? Jost F?rster ? Dr. Peter Lempp ? Dr.
>> Volkmar Varnhagen
>>
>> Amtsgericht Berlin-Charlottenburg, HRB 98814 This message contains
>> information that may be privileged or confidential and is the
>> property of the Capgemini Group. It is intended only for the person
>> to whom it is addressed. If you are not the intended recipient, you
>> are not authorized to read, print, retain, copy, disseminate,
>>
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
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 ask for permission to > make this information public, or to anonymize even the names of the > tables and columns. Knowing: a) the kind of data (do you use blobs or big text columns?) b) if data was inserted on primary key sort order or randomized c) if you have a multi-gigabyte db d) pragmas configuration e) column order (put blobs and text at the end of table column) may point us to help you more without exposing internal information. Also remember to don't use primary key on multicolumn indexes and upgrade your sqlite3 to newer version (from 3.7.13 to 3.8.9) > > Thanks for your help, > > -- > Nicolas Boullis > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
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 before ANALYZE, and 17.5s of CPU time after ANALYZE. Much better! But if I use the database withouth PRIMARY KEY/UNIQUE constraints, it uses 5.7s of CPU time before ANALYZE, and 86s of CPU time after ANALYZE? The performance with PRIMARY KEY/UNIQUE constraints, after ANALYZE, is decent enough, but it?s still significantly slower than the database without the constraints before ANALYZE, which looks odd to me. > 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 ask for permission to make this information public, or to anonymize even the names of the tables and columns. Thanks for your help, -- Nicolas Boullis
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
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 does not keep statistics automatically. Run ANALYZE. If that does not help, we'll have to look at the actual queries (and their EXPLAIN QUERY PLAN output). Regards, Clemens
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
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 measurement yet, but I?d say at least 10? faster). As I understand it, SQLite builds implicit indexes for PRIMARY KEY/UNIQUE constraints, but I would not expect those indexes to significantly decrease the performance of SELECT requests? For what it?s worth, my program is written in Python 3, and run on an up-to-date Debian Wheezy system, with Python 3.2.3-7, dynamically linked with libsqlite3 3.7.13-1+deb7u1. Is there anything well-known that explains this performance difference? Thanks for your help, -- Nicolas Boullis Footnote: ? The program first creates and fills 2 temporary tables, with locally-gathered data, and then only performs SELECT queries with both the 2 temporary tables and the permanent tables. Nothing is ever written to the permanent tables, and the temporary tables are created with no contraint.
[sqlite] Problems with pragma journal_mode
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 other PRAGMAs, like "PRAGMA database_list;" and "PRAGMA table_info=TABLE;". See the complete list at (https://www.sqlite.org/pragma.html#toc). Are any pragmas working on your system? On 4/20/15, Janke, Julian wrote: > 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 > -fno-jump-tables > -fno-section-anchors > -fno-merge-constants > -fno-builtin > -nostdlib > -Werror-implicit-function-declaration > -Wconversion > -fstack-usage > -std=c99 > -c > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard > Hipp > Sent: Montag, 20. April 2015 15:55 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Problems with pragma journal_mode > > 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 > -DSQLITE_DEFAULT_MMAP_SIZE=0 > -DSQLITE_TEMP_STORE=0 > -DSQLITE_DEFAULT_CACHE_SIZE=500 > > Please double-check to ensure that you do not have additional SQLITE defines > stuck in a configuration file someplace. > >> >> >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org >> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of >> Richard Hipp >> Sent: Montag, 20. April 2015 12:18 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Problems with pragma journal_mode >> >> 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 0 0 000 >> 1 JournalMode0 1 500 >> 2 ResultRow 1 1 000 >> 3 Halt 0 0 000 >> >> What does: "PRAGMA compile_options;" and "SELECT sqlite_source_id();" >> show? >> >> >> >>> Is it the result of what you expected? >>> >>> -Original Message- >>> From: sqlite-users-bounces at mailinglists.sqlite.org >>> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of >>> Richard Hipp >>> Sent: Freitag, 17. April 2015 16:59 >>> To: General Discussion of SQLite Database >>> Subject: Re: [sqlite] Problems with pragma journal_mode >>> >>> On 4/17/15, Janke, Julian wrote: Hello, Thanks for your reply. I changed my code again: 1) rc = sqlite3_open(dbPath, ); --> SQLITE_OK 2) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;", testCallbackPrint, 0, ); 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, , NULL); --> SQLITE_OK 4) rc = sqlite3_step(stmt); --> SQLITE_DONE >>> >>> I do not understand this. "PRAGMA journal_mode" should always give a >>> return value, even when it fails. sqlite3_step() should have >>> returned SQLITE_ROW. >>> >>> Please try instead, "EXPLAIN PRAGMA journal_mode=WAL". Verify that >>> you get multiple rows of output in that case. >>> >>> 5) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;", testCallbackPrint, 0, ); 6) txt = sqlite3_column_text (stmt, 0); --> returns an empty string 7) rc = sqlite3_finalize(stmt); --> SQLITE_OK 8) rc = sqlite3_close(db); --> SQLITE_OK Repeated the same steps with 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode;", 24, , NULL); --> SQLITE_OK To my knowledge, at line 4 SQLITE_ROW instead of SQLITE_DONE should be returned. "These routines may only be called when the most recent call to sqlite3_step() has returned SQLITE_ROW and neither sqlite3_reset() nor sqlite3_finalize() have been called subsequently." It looks as if all PRAGMA instructions are completely ignored, since the query for the status of the JOURNAL_MODE returns no result. Is it possible that I have turned off PRAMAs by anything or something is missing, so PRAMAs can run? Thanks for your help -Original Message- From: sqlite-users-bounces at
[sqlite] building SQLite DLL with Visual C++
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 I doing wrong? Jay
[sqlite] Problems with pragma journal_mode
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
-DSQLITE_DEFAULT_MMAP_SIZE=0
-DSQLITE_TEMP_STORE=0
-DSQLITE_DEFAULT_CACHE_SIZE=500
Please double-check to ensure that you do not have additional SQLITE
defines stuck in a configuration file someplace.
>
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
> Hipp
> Sent: Montag, 20. April 2015 12:18
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problems with pragma journal_mode
>
> 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 0 0 000
> 1 JournalMode0 1 500
> 2 ResultRow 1 1 000
> 3 Halt 0 0 000
>
> What does: "PRAGMA compile_options;" and "SELECT sqlite_source_id();" show?
>
>
>
>> Is it the result of what you expected?
>>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org
>> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
>> Richard Hipp
>> Sent: Freitag, 17. April 2015 16:59
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Problems with pragma journal_mode
>>
>> On 4/17/15, Janke, Julian wrote:
>>> Hello,
>>> Thanks for your reply.
>>>
>>> I changed my code again:
>>>
>>> 1) rc = sqlite3_open(dbPath, ); --> SQLITE_OK
>>> 2) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;",
>>> testCallbackPrint, 0, );
>>> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, ,
>>> NULL);
>>> --> SQLITE_OK
>>> 4) rc = sqlite3_step(stmt); --> SQLITE_DONE
>>
>> I do not understand this. "PRAGMA journal_mode" should always give a
>> return value, even when it fails. sqlite3_step() should have returned
>> SQLITE_ROW.
>>
>> Please try instead, "EXPLAIN PRAGMA journal_mode=WAL". Verify that
>> you get multiple rows of output in that case.
>>
>>
>>> 5) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;",
>>> testCallbackPrint, 0, );
>>> 6) txt = sqlite3_column_text (stmt, 0); --> returns an empty string
>>> 7) rc = sqlite3_finalize(stmt); --> SQLITE_OK
>>> 8) rc = sqlite3_close(db); --> SQLITE_OK
>>>
>>> Repeated the same steps with
>>>
>>> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode;", 24, ,
>>> NULL); --> SQLITE_OK
>>>
>>> To my knowledge, at line 4 SQLITE_ROW instead of SQLITE_DONE should
>>> be returned.
>>>
>>> "These routines may only be called when the most recent call to
>>> sqlite3_step() has returned SQLITE_ROW and neither sqlite3_reset()
>>> nor
>>> sqlite3_finalize() have been called subsequently."
>>>
>>> It looks as if all PRAGMA instructions are completely ignored, since
>>> the query for the status of the JOURNAL_MODE returns no result.
>>>
>>> Is it possible that I have turned off PRAMAs by anything or something
>>> is missing, so PRAMAs can run?
>>>
>>> Thanks for your help
>>>
>>>
>>> -Original Message-
>>> From: sqlite-users-bounces at mailinglists.sqlite.org
>>> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
>>> Simon Slavin
>>> Sent: Donnerstag, 16. April 2015 17:56
>>> To: General Discussion of SQLite Database
>>> Subject: Re: [sqlite] Problems with pragma journal_mode
>>>
>>>
>>> On 16 Apr 2015, at 2:33pm, Janke, Julian
>>> wrote:
>>>
rc = sqlite3_step(stmt);
--> returns SQLITE_DONE
>>>
>>> After the above two lines, print the value returned by
>>>
>>> sqlite3_column_text(stmt, 0)
>>>
>>> I'm not good at C off the top of my head but I think it's something
>>> like
>>>
>>> -
>>>
>>> const unsigned char * theText;
>>>
>>> [...]
>>>
>>> rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, ,
>>> NULL);
>>>
>>> rc = sqlite3_step(stmt);
>>> theText = sqlite3_column_text (stmt, 0); printf ("text returned:
>>> %s", theText);
>>>
>>> rc = sqlite3_finalize(stmt);
>>>
>>> -
>>>
>>> Hope if I got it wrong someone else will tell you. By the way, you
>>> can also check the values returned from sqlite3_finalize() and
>>> sqlite3_close() since they can tell you useful things if something
>>> went wrong.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>
>>> Firma: Capgemini Deutschland GmbH
[sqlite] Corruption and TEMPORARY tables
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. Thanks very much for your help, Richard. I read the mailing list, but I'm still an inexperienced user. Joe
[sqlite] Problems with pragma journal_mode
'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: Freitag, 17. April 2015 16:59
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with pragma journal_mode
On 4/17/15, Janke, Julian wrote:
> Hello,
> Thanks for your reply.
>
> I changed my code again:
>
> 1) rc = sqlite3_open(dbPath, ); --> SQLITE_OK
> 2) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;",
> testCallbackPrint, 0, );
> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, ,
> NULL);
> --> SQLITE_OK
> 4) rc = sqlite3_step(stmt); --> SQLITE_DONE
I do not understand this. "PRAGMA journal_mode" should always give a return
value, even when it fails. sqlite3_step() should have returned SQLITE_ROW.
Please try instead, "EXPLAIN PRAGMA journal_mode=WAL". Verify that you get
multiple rows of output in that case.
> 5) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;",
> testCallbackPrint, 0, );
> 6) txt = sqlite3_column_text (stmt, 0); --> returns an empty string
> 7) rc = sqlite3_finalize(stmt); --> SQLITE_OK
> 8) rc = sqlite3_close(db); --> SQLITE_OK
>
> Repeated the same steps with
>
> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode;", 24, ,
> NULL); --> SQLITE_OK
>
> To my knowledge, at line 4 SQLITE_ROW instead of SQLITE_DONE should be
> returned.
>
> "These routines may only be called when the most recent call to
> sqlite3_step() has returned SQLITE_ROW and neither sqlite3_reset() nor
> sqlite3_finalize() have been called subsequently."
>
> It looks as if all PRAGMA instructions are completely ignored, since
> the query for the status of the JOURNAL_MODE returns no result.
>
> Is it possible that I have turned off PRAMAs by anything or something
> is missing, so PRAMAs can run?
>
> Thanks for your help
>
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
> Simon Slavin
> Sent: Donnerstag, 16. April 2015 17:56
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problems with pragma journal_mode
>
>
> On 16 Apr 2015, at 2:33pm, Janke, Julian
> wrote:
>
>> rc = sqlite3_step(stmt);
>> --> returns SQLITE_DONE
>
> After the above two lines, print the value returned by
>
> sqlite3_column_text(stmt, 0)
>
> I'm not good at C off the top of my head but I think it's something
> like
>
> -
>
> const unsigned char * theText;
>
> [...]
>
> rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, ,
> NULL);
>
> rc = sqlite3_step(stmt);
> theText = sqlite3_column_text (stmt, 0); printf ("text returned: %s",
> theText);
>
> rc = sqlite3_finalize(stmt);
>
> -
>
> Hope if I got it wrong someone else will tell you. By the way, you
> can also check the values returned from sqlite3_finalize() and
> sqlite3_close() since they can tell you useful things if something went wrong.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> Firma: Capgemini Deutschland GmbH
> Aufsichtsratsvorsitzender: Antonio Schnieder ? Gesch?ftsf?hrer: Dr.
> Michael Schulte (Sprecher) ? Jost F?rster ? Dr. Peter Lempp ? Dr.
> Volkmar Varnhagen
>
> Amtsgericht Berlin-Charlottenburg, HRB 98814 This message contains
> information that may be privileged or confidential and is the property
> of the Capgemini Group. It is intended only for the person to whom it
> is addressed. If you are not the intended recipient, you are not
> authorized to read, print, retain, copy, disseminate, distribute, or
> use this message or any part thereof. If you receive this message in
> error, please notify the sender immediately and delete all copies of this
> message.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This message contains information that may be privileged or confidential and is
the property of the Capgemini Group. It is intended only for the person to whom
it is addressed. If you are not the intended recipient, you are not authorized
to read, print, retain, copy, disseminate, distribute, or use this message or
any part thereof. If you receive this message in error, please notify the
sender immediately and delete all copies of this message.
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
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 constraints, the program > uses 540s of CPU time before ANALYZE, and 17.5s of CPU time after > ANALYZE. Much better! > > But if I use the database withouth PRIMARY KEY/UNIQUE constraints, it > uses 5.7s of CPU time before ANALYZE, and 86s of CPU time after ANALYZE? > Please send us: (1) The output of "SELECT sqlite_source_id();" (2) The complete text of you SELECT statement. (3) The output of the ".fullschema" command from the most recent version of the sqlite3.exe command-line shell run on your database file after it is ANALYZE-ed. -- D. Richard Hipp drh at sqlite.org
[sqlite] Problems with pragma journal_mode
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 0 0 000
1 JournalMode0 1 500
2 ResultRow 1 1 000
3 Halt 0 0 000
What does: "PRAGMA compile_options;" and "SELECT sqlite_source_id();" show?
> Is it the result of what you expected?
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
> Hipp
> Sent: Freitag, 17. April 2015 16:59
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problems with pragma journal_mode
>
> On 4/17/15, Janke, Julian wrote:
>> Hello,
>> Thanks for your reply.
>>
>> I changed my code again:
>>
>> 1) rc = sqlite3_open(dbPath, ); --> SQLITE_OK
>> 2) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;",
>> testCallbackPrint, 0, );
>> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, ,
>> NULL);
>> --> SQLITE_OK
>> 4) rc = sqlite3_step(stmt); --> SQLITE_DONE
>
> I do not understand this. "PRAGMA journal_mode" should always give a return
> value, even when it fails. sqlite3_step() should have returned SQLITE_ROW.
>
> Please try instead, "EXPLAIN PRAGMA journal_mode=WAL". Verify that you get
> multiple rows of output in that case.
>
>
>> 5) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;",
>> testCallbackPrint, 0, );
>> 6) txt = sqlite3_column_text (stmt, 0); --> returns an empty string
>> 7) rc = sqlite3_finalize(stmt); --> SQLITE_OK
>> 8) rc = sqlite3_close(db); --> SQLITE_OK
>>
>> Repeated the same steps with
>>
>> 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode;", 24, ,
>> NULL); --> SQLITE_OK
>>
>> To my knowledge, at line 4 SQLITE_ROW instead of SQLITE_DONE should be
>> returned.
>>
>> "These routines may only be called when the most recent call to
>> sqlite3_step() has returned SQLITE_ROW and neither sqlite3_reset() nor
>> sqlite3_finalize() have been called subsequently."
>>
>> It looks as if all PRAGMA instructions are completely ignored, since
>> the query for the status of the JOURNAL_MODE returns no result.
>>
>> Is it possible that I have turned off PRAMAs by anything or something
>> is missing, so PRAMAs can run?
>>
>> Thanks for your help
>>
>>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org
>> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
>> Simon Slavin
>> Sent: Donnerstag, 16. April 2015 17:56
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Problems with pragma journal_mode
>>
>>
>> On 16 Apr 2015, at 2:33pm, Janke, Julian
>> wrote:
>>
>>> rc = sqlite3_step(stmt);
>>> --> returns SQLITE_DONE
>>
>> After the above two lines, print the value returned by
>>
>> sqlite3_column_text(stmt, 0)
>>
>> I'm not good at C off the top of my head but I think it's something
>> like
>>
>> -
>>
>> const unsigned char * theText;
>>
>> [...]
>>
>> rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, ,
>> NULL);
>>
>> rc = sqlite3_step(stmt);
>> theText = sqlite3_column_text (stmt, 0); printf ("text returned: %s",
>> theText);
>>
>> rc = sqlite3_finalize(stmt);
>>
>> -
>>
>> Hope if I got it wrong someone else will tell you. By the way, you
>> can also check the values returned from sqlite3_finalize() and
>> sqlite3_close() since they can tell you useful things if something went
>> wrong.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> Firma: Capgemini Deutschland GmbH
>> Aufsichtsratsvorsitzender: Antonio Schnieder ? Gesch?ftsf?hrer: Dr.
>> Michael Schulte (Sprecher) ? Jost F?rster ? Dr. Peter Lempp ? Dr.
>> Volkmar Varnhagen
>>
>> Amtsgericht Berlin-Charlottenburg, HRB 98814 This message contains
>> information that may be privileged or confidential and is the property
>> of the Capgemini Group. It is intended only for the person to whom it
>> is addressed. If you are not the intended recipient, you are not
>> authorized to read, print, retain, copy, disseminate, distribute, or
>> use this message or any part thereof. If you receive this message in
>> error, please notify the sender immediately and delete all copies of this
>> message.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
>
