Re: [sqlite] Speed regression after 3.6.17
"D. Richard Hipp" wrote in message news:4c2248d2-898d-41ec-81fc-bafdb726c...@hwaci.com... > > On Jan 11, 2010, at 9:28 PM, ve3meo wrote: >> Oddly enough, by revising the query to explicitly >> use an index, the two later releases are much faster... > > Did you run ANALYZE before you tried using explicit indices? > No. You have introduced me to it. So I tried using a SQLite manager with the 3.6.17 release. Shaved 25% off the unindexed (SQLite optimised only) query time to ~190s. Multiplied the previously fast explicitly indexed query from 3s to 210s. I was happy with the 3s. Here are the EXPLAIN QUERY PLAN results before and after ANALYZE: Before: order,from,detail 0,0,TABLE EventTable, 1,1,TABLE FactTypeTable USING PRIMARY KEY, 2,2,TABLE FamilyTable USING PRIMARY KEY, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 0,0,TABLE EventTable, 1,1,TABLE FactTypeTable USING PRIMARY KEY, 2,2,TABLE FamilyTable USING PRIMARY KEY, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 0,2,TABLE NameTable WITH INDEX idxNamePrimary, 1,0,TABLE EventTable WITH INDEX idxOwnerDate, 2,1,TABLE FactTypeTable USING PRIMARY KEY, 0,0,TABLE NameTable WITH INDEX idxNamePrimary, 0,0,TABLE WitnessTable, 1,1,TABLE roletable USING PRIMARY KEY, 2,2,TABLE EventTable USING PRIMARY KEY, 3,5,TABLE FactTypeTable USING PRIMARY KEY, 4,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 5,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, After: order,from,detail 0,1,TABLE FactTypeTable, 1,2,TABLE FamilyTable, 2,0,TABLE EventTable WITH INDEX idxOwnerEvent, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 0,1,TABLE FactTypeTable, 1,2,TABLE FamilyTable, 2,0,TABLE EventTable WITH INDEX idxOwnerEvent, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 0,1,TABLE FactTypeTable, 1,2,TABLE NameTable WITH INDEX idxNamePrimary, 2,0,TABLE EventTable WITH INDEX idxOwnerEvent, 0,0,TABLE NameTable WITH INDEX idxNamePrimary, 0,1,TABLE roletable, 1,5,TABLE FactTypeTable, 2,2,TABLE EventTable, 3,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 4,0,TABLE WitnessTable WITH INDEX idxWitnessEventID, 5,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed regression after 3.6.17
On Jan 11, 2010, at 9:28 PM, ve3meo wrote: > Oddly enough, by revising the query to explicitly > use an index, the two later releases are much faster... Did you run ANALYZE before you tried using explicit indices? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed regression after 3.6.17
Max Vlasov writes: > > On Mon, Jan 11, 2010 at 2:33 AM, Tom Holden wrote: > > > I have been trying out a number of SQLite managers, one test being the > > execution time for the same query on the same database on the same computer. > > The scattering of results was very surprising with a spread on the order of > > 10:1. > > > > Is the message posted 11 hours ago about the same issue? ( > http://www.mail-archive.com/sqlite-users-CzDROfG0BjIdnm+yROfE0A public.gmane.org/msg49650.html) > Anyway I still suggest the same (see in the thread) - compare VDBE code > sequences > > Max Yes, that is the developer of one of the SQLite managers who posted the earlier message re "Performance regression...". I am not a developer, merely a new user of applications that use SQLite who is delving into the application database. I compared the EXPLAIN QUERY PLAN results for SQLite 3.5.4, 3.6.17 and 3.6.20. The first two are identical; the third is quite different. I don't quite know what that means; maybe it indicates that the later version joins the tables less efficiently. Oddly enough, by revising the query to explicitly use an index, the two later releases are much faster than for the unindexed query while the first returns an error near 'INDEXED'. Here are the results of the EXPLAIN QUERY PLAN for the original, unindexed query: For 3.5.4 and 3.6.17 which executed in ~240s: "order", "from", "detail" "0", "3", "TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1", "2", "TABLE FamilyTable WITH INDEX idxFamilyFatherID" "2", "0", "TABLE EventTable WITH INDEX idxOwnerDate" "3", "1", "TABLE FactTypeTable USING PRIMARY KEY" "4", "4", "TABLE NameTable AS NameTable2 WITH INDEX idxNamePrimary" "0", "3", "TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1", "2", "TABLE FamilyTable WITH INDEX idxFamilyFatherID" "2", "0", "TABLE EventTable WITH INDEX idxOwnerDate" "3", "1", "TABLE FactTypeTable USING PRIMARY KEY" "4", "4", "TABLE NameTable AS NameTable2 WITH INDEX idxNamePrimary" "0", "2", "TABLE NameTable WITH INDEX idxNamePrimary" "1", "0", "TABLE EventTable WITH INDEX idxOwnerDate" "2", "1", "TABLE FactTypeTable USING PRIMARY KEY" "0", "0", "TABLE NameTable WITH INDEX idxNamePrimary" "0", "3", "TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1", "0", "TABLE WitnessTable WITH INDEX idxWitnessPersonID" "2", "1", "TABLE roletable USING PRIMARY KEY" "3", "2", "TABLE EventTable USING PRIMARY KEY" "4", "5", "TABLE FactTypeTable USING PRIMARY KEY" "5", "4", "TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" And for 3.6.20 which executed the same query in ~2500s: "order","from","detail" "0","3","TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1","4","TABLE NameTable AS NameTable2 WITH INDEX idxNamePrimary" "2","2","TABLE FamilyTable WITH INDEX idxFamilyMotherID" "3","0","TABLE EventTable WITH INDEX idxOwnerDate" "4","1","TABLE FactTypeTable USING PRIMARY KEY" "0","3","TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1","4","TABLE NameTable AS NameTable2 WITH INDEX idxNamePrimary" "2","2","TABLE FamilyTable WITH INDEX idxFamilyMotherID" "3","0","TABLE EventTable WITH INDEX idxOwnerDate" "4","1","TABLE FactTypeTable USING PRIMARY KEY" "0","2","TABLE NameTable WITH INDEX idxNamePrimary" "1","0","TABLE EventTable WITH INDEX idxOwnerDate" "2","1","TABLE FactTypeTable USING PRIMARY KEY" "0","0","TABLE NameTable WITH INDEX idxNamePrimary" "0","3","TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1","0","TABLE WitnessTable WITH INDEX idxWitnessPersonID" "2","1","TABLE roletable USING PRIMARY KEY" "3","2","TABLE EventTable USING PRIMARY KEY" "4","5","TABLE FactTypeTable USING PRIMARY KEY" "5","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" Here are the results for the indexed query, identical for 3.6.17 and 3.6.20 but an error with 3.5.4 so no result to compare, executes in ~2s: "order","from","detail" "0","0","TABLE EventTable" "1","1","TABLE FactTypeTable USING PRIMARY KEY" "2","2","TABLE FamilyTable USING PRIMARY KEY" "3","3","TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "4","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" "0","0","TABLE EventTable" "1","1","TABLE FactTypeTable USING PRIMARY KEY" "2","2","TABLE FamilyTable USING PRIMARY KEY" "3","3","TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "4","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" "0","2","TABLE NameTable WITH INDEX idxNamePrimary" "1","0","TABLE EventTable WITH INDEX idxOwnerDate" "2","1","TABLE FactTypeTable USING PRIMARY KEY" "0","0","TABLE NameTable WITH INDEX idxNamePrimary" "0","0","TABLE WitnessTable" "1","1","TABLE roletable USING PRIMARY KEY" "2","2","TABLE EventTable USING PRIMARY KEY" "3","5","TABLE FactTypeTable USING PRIMARY KEY" "4","3","TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "5","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" The results for these two queries taken together suggest tha
[sqlite] BUG - dereferencing type-punned pointer in os_win.c
Ok it's not really a bug; it's just a compiler warning. I get this warning
when compiling the amalgamation:
gcc -Os -Wall -DFOSSIL_I18N=0 -L/mingw/lib -I/mingw/include -I. -I./src
-DSQLITE_OMIT_LOAD_EXTENSION=1 -DSQLITE_THREADSAFE=0
-DSQLITE_DEFAULT_FILE_FORMAT=4 -Dlocaltime=fossil_localtime -c
./src/sqlite3.c -o sqlite3.o
./src/sqlite3.c: In function `getLastErrorMsg':
./src/sqlite3.c:28450: warning: dereferencing type-punned pointer will break
strict-aliasing rules
I think the change set below resolves the warning without introducing
another bug. I could use a code review.
PS C:\rev\src\sqlite3\src> fossil info
project-name: SQLite
repository: c:\rev\fossil\sqlite3.f
local-root: C:/rev/src/sqlite3/
user-home: : C:/Users/rev/AppData/Local
project-code: 2ab58778c2967968b94284e989e43dc11791f548
server-code: 2fa7c8b2762294d28396292f74c7b94c9c50af75
checkout: a2b1183d9e9898d06d623b342bbb552e85a9b3f6 2010-01-11 12:00:48
UTC
parent: 14dc46a74aafe44c0bf7dffd26268395b2c5edb2 2010-01-09 07:33:54
UTC
tags: trunk
PS C:\rev\src\sqlite3\src> fossil diff os_win.c
--- os_win.c
+++ os_win.c
@@ -1253,25 +1253,29 @@
** buffer, excluding the terminating null char.
*/
DWORD error = GetLastError();
DWORD dwLen = 0;
char *zOut = 0;
+ union {
+WCHAR** pzwc;
+LPWSTR lpws;
+ } wu;
if( isNT() ){
-WCHAR *zTempWide = NULL;
+*wu.pzwc = NULL;
dwLen = FormatMessageW(FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS,
NULL,
error,
0,
- (LPWSTR) &zTempWide,
+ wu.lpws,
0,
0);
if( dwLen > 0 ){
/* allocate a buffer and convert to UTF8 */
- zOut = unicodeToUtf8(zTempWide);
+ zOut = unicodeToUtf8(*wu.pzwc);
/* free the system buffer allocated by FormatMessage */
- LocalFree(zTempWide);
+ LocalFree(*wu.pzwc);
}
/* isNT() is 1 if SQLITE_OS_WINCE==1, so this else is never executed.
** Since the ASCII version of these Windows API do not exist for WINCE,
** it's important to not reference them for WINCE builds.
*/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained "disk i/o error", Unix
On Mon, Jan 11, 2010 at 10:09 AM, Ian Jackson wrote: > I wrote: >> I have recently had an apparently isolated failure of a program making >> some updates to a sqlite database. The only information I have is >> this error message: >> >> DBD::SQLite::st execute failed: disk I/O error(10) at dbdimp.c line 423 >> [for Statement "SELECT * FROM sell NATURAL LEFT JOIN commods WHERE >> commodname IS NULL"] at CommodsDatabase.pm line 158. >> PROCESSING FAILED > Try running the above statement from the sqlite shell. If you still get the error, something is fubar with your db. If not, something is fubar with your Perl implementation. Not much help, but this might take you a step or two further. > This has just happened again, twice, recently. What additional > instrumentation should I apply to my system to find out the cause ? > > I'm using the Debian package sqlite3 3.5.9-6 on Debian lenny i386, > in Perl, via DBD::SQLite (libdbd-sqlite3-perl 1.14-3). > > As I wrote earlier: > > CommodsDatabase.pm is my code. That part is doing a consistency check > before saying COMMIT. I don't know exactly what sqlite was doing, but > I was alarmed. I checked my system logs and there are no reports of > problems with the disks. There are no reports of the filesystem > having been full and while possible it doesn't seem likely. > > Ian. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained "disk i/o error", Unix
Ian Jackson writes: > I wrote: >> I have recently had an apparently isolated failure of a program >> making some updates to a sqlite database. The only information I >> have is this error message: >> >> DBD::SQLite::st execute failed: disk I/O error(10) at dbdimp.c >> line 423 [for Statement "SELECT * FROM sell NATURAL LEFT JOIN >> commods WHERE commodname IS NULL"] at CommodsDatabase.pm line 158. >> PROCESSING FAILED > > This has just happened again, twice, recently. What additional > instrumentation should I apply to my system to find out the cause ? You can try to run the program under strace and check for failed system calls. Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reasons for SQLITE_CANTOPEN
Edzard Pasma writes: >> When my program has been running for a while, I suddenly get an >> SQLITE_CANTOPEN error when I'm trying to open a database >> connection with a new thread. The database file, however, is >> definitively present and accessible. > > Hope strace (see Roger Binns' post) will help you further. Also lsof > may help. The problem was indeed that I reached the maximum number of fds. Thanks for the help! Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Limitation on Column count
Hello all, I'm considering using SQLite for a new application. The schema will contain a bunch of small tables with few columns (~10) plus one large table with many columns and 1000...1 rows. 'Many columns' typically fits into the default 2000 column limit, but can exceed it at times (that is, on some of the foreseen databases). It will never exceed the theoretical / compile time selectable limit of 32k columns. Queries on this big table will be rather straight-forward: either on the table alone (SELECT * FROM table_large) or one join on one field to one of the smaller tables. The http://www.sqlite.org/limits.html Limits page warns: "There are places in the SQLite code generator that use algorithms that are O(N²) where N is the number of columns." which is kind of discouraging to increase max. column count at compile time, but is not very specific about when this happens... I now have two design options: - increase max. column count at compile time (possibly setting SQLITE_LIMIT_COLUMN on databases where I don't need more than (say) 1000 columns) and accept the quoted performance degradation. - alternatively, in the client handle cases with more than 2000 columns, splitting the storage up into two (or more) tables Any advise, experience - or more specifics on the "O(N²)" remark are highly welcome! Thanks for your help - Stefan -- View this message in context: http://old.nabble.com/Limitation-on-Column-count-tp27117364p27117364.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reasons for SQLITE_CANTOPEN
Op 11-jan-2010, om 1:15 heeft Nikolaus Rath het volgende geschreven > Edzard Pasma writes: >> Op 10-jan-2010, om 19:25 heeft Nikolaus Rath het volgende geschreven: >> >>> Filip Navara writes: > I am accessing the same database from several threads, each > using a > separate connection. Shared cache is not enabled. > > When my program has been running for a while, I suddenly get an > SQLITE_CANTOPEN error when I'm trying to open a database > connection with > a new thread. The database file, however, is definitively present > and > accessible. Hi! Does "pragma journal_mode=truncate;" make any difference? Is this on Windows? Do you have TortoiseSVN installed on the same system? >>> >>> No to all questions, I'm afraid. Seems that my problem is a >>> different one. >> >> Does your application attach at least 20 further databases within >> each of the 15 connections? >> Does it open at least 250 files any other way? >> >> If any yes, then you have too many open files! > > No, there is only one database for each connection. The idea with the > open files may still be a good one though, I will look into that. But > why should the limit be 250? On this system I have an ulimit of 1024 > open fds, and I guess that on other systems it would at least still be > some power of 2. The number of 250 was just a rough indication, taking into account already open database connections and other IO channels. I reasoned that if you have that many open files and get the particular error, it is almost sure it is caused by the open files limit. On my system that is 256. I had not expected it to vary so much. up to 8k on some enterprise Linux distribution. Hope strace (see Roger Binns' post) will help you further. Also lsof may help. regards, Edzard Pasma ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained "disk i/o error", Unix
I wrote: > I have recently had an apparently isolated failure of a program making > some updates to a sqlite database. The only information I have is > this error message: > > DBD::SQLite::st execute failed: disk I/O error(10) at dbdimp.c line 423 > [for Statement "SELECT * FROM sell NATURAL LEFT JOIN commods WHERE commodname > IS NULL"] at CommodsDatabase.pm line 158. > PROCESSING FAILED This has just happened again, twice, recently. What additional instrumentation should I apply to my system to find out the cause ? I'm using the Debian package sqlite3 3.5.9-6 on Debian lenny i386, in Perl, via DBD::SQLite (libdbd-sqlite3-perl 1.14-3). As I wrote earlier: CommodsDatabase.pm is my code. That part is doing a consistency check before saying COMMIT. I don't know exactly what sqlite was doing, but I was alarmed. I checked my system logs and there are no reports of problems with the disks. There are no reports of the filesystem having been full and while possible it doesn't seem likely. Ian. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graphs and sql
On Mon, Jan 11, 2010 at 07:53:28AM +, Nick Atty scratched on the wall: > I store all the data for my waterways route planner in SQLite, but I > load it into memory for running Dijkstra's algorithm on it to find the > shortest (when weighted) paths. It's at canalplan.eu if anyone wants a > play. > > One problem you rapidly run into when storing graphs in SQL, in my > limited and non-expert experience, is that - as in this example - you > end up with edge records each of which refers to two vertices. My > database maintenance and update code is riddled with: > > SELECT ... FROM link WHERE place1=x AND place2=y OR place1=y AND place2=x; > > and similar. Apart from imposing a condition (such as always having v1 > < v2 in the example code) is there any sensible way round this? Put in A->B and B->A as two different edges. Yes, the database is going to get a bit bigger, but most graph algorithms use directed edges anyways-- having "two way" edges is usually just a generalization. Using directed edges also allows you to easily designate one-way routes, or routes with asymmetric weights (river with a strong current?). It also simplifies the SQL quite a bit, which will likely make index utilization better. Of course, storage and maintenance goes up, but such are trade-offs. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Why I always got error SQLITE_MISUSE 21 /* Library used incorrectly */
> So for a temp solution, I will open the database again if the > application detect there is a SQLITE_MISUSE, is this solution safe enough? I'd say no, it isn't. This error shows a problem in your application, most probably some race condition that you didn't see. So you want just to close your eyes and re-open the connection. But that will mean that somewhere else in your application you'll have problems and less visible ones like loss of some data or maybe even segmentation fault. So you better look at your code and at how you use SQLite's API. If your sources of SQLite don't have SQLITE3_THREADSAFE variable then these sources are most probably not thread-safe. And if you use one database connection from all your threads you have to properly guard each usage with mutex. So check that your code always does that. Pavel On Mon, Jan 11, 2010 at 8:59 AM, tim shen wrote: > > Hi Pavel, > > Since we are at the last stage to release the project, is it safe to replace > with the latest sqlite version without our QA's full testing of the > application's function? I knew there are many improvement in the latest > version. :-). So for a temp solution, I will open the database again if the > application detect there is a SQLITE_MISUSE, is this solution safe enough? > > Thanks, > Tim > > > > Pavel Ivanov-2 wrote: >> >>> I am now using SQLite version 3.3.5, and i cannot find flag >>> SQLITE3_THREADSAFE in source code, so my question is how can i ensure i >>> compile with this option? >> >> Probably there was no such flag in that version yet (I don't know >> enough about it). Why don't you use the latest version? >> >>> The second question is when i met SQLITE_MISUSE error, can i reset or >>> reopen >>> the database to avoid this error? >> >> You cannot reset database connection, but you can close it (probably >> finalizing all statements before that using >> http://www.sqlite.org/c3ref/next_stmt.html if it exist in 3.3.5) and >> open it again. >> >> >> Pavel >> >> On Sun, Jan 10, 2010 at 12:27 AM, tim shen wrote: >>> >>> hi Pavel, >>> Thanks for your reply and reminder. >>> I am now using SQLite version 3.3.5, and i cannot find flag >>> SQLITE3_THREADSAFE in source code, so my question is how can i ensure i >>> compile with this option? >>> >>> The second question is when i met SQLITE_MISUSE error, can i reset or >>> reopen >>> the database to avoid this error? >>> >>> Regards, >>> Tim >>> >>> >>> Pavel Ivanov-2 wrote: Oh, and one more thing: sqlite-dev list is for questions related to developing SQLite itself. For problems with using SQLite you should write to sqlite-users list (put in CC). Pavel On Fri, Jan 8, 2010 at 7:50 AM, Pavel Ivanov wrote: > As you're new to SQLite you could easily make some incorrect usages of > SQLite's API. But we won't be able to say to you exactly what is > incorrect until we don't see the actual code. So please show it to us. > Incidentally exact value of SQLITE3_THREADSAFE (0, 1 or 2) during > compilation is also important as well as any other flags you use. > > And just out of curiosity: how did you check that the reason "using > sqlite_close or calling sqlite_exec with the same database pointer > simultaneously from two separate threads" is not applied to you if as > you said in the application "you saved this handle as global variable, > then in other threads you use it"? > > > Pavel > > On Fri, Jan 8, 2010 at 3:42 AM, Tim Shen wrote: >> Hi All, >> >> I am new to SQLite and recently I took over a new project which >> using >> SQLite DB under WinCE. We build the SQlite.dll using flag >> SQLITE3_THREADSAFE, and used Multi-Thread programming in the >> application. >> The problem is that in some operations, function call sqlite3_exec() >> will >> throw out error SQLITE_MISUSE. >> I call the sqlite3_open16() and sqlite3_close() in main thread and >> saved >> this handle as global variable. Then in other threads, I will use this >> global variable to call other sqlite3 function such as >> sqlite3_exec(),sqlite3_get_table(),sqlite3_free_table(). >> I searched on the sqlite3 site, and got the following information >> >> SQLITE_MISUSE >> This error might occur if one or more of the SQLite API routines is >> used >> incorrectly. Examples of incorrect usage include calling sqlite_exec >> after >> the database has been closed using sqlite_close or calling sqlite_exec >> with >> the same database pointer simultaneously from two separate threads. >> >> I checked above 2 possible reason may caused this error and make sure >> it >> is >> not caused by them. Any other advise you guys can give it to me? >> Thanks >> in >> advance. >> >> Regards, >> Tim >> >> ___ >> sqlite-dev mailing list
Re: [sqlite] [sqlite-dev] Why I always got error SQLITE_MISUSE 21 /* Library used incorrectly */
Hi Pavel, Since we are at the last stage to release the project, is it safe to replace with the latest sqlite version without our QA's full testing of the application's function? I knew there are many improvement in the latest version. :-). So for a temp solution, I will open the database again if the application detect there is a SQLITE_MISUSE, is this solution safe enough? Thanks, Tim Pavel Ivanov-2 wrote: > >> I am now using SQLite version 3.3.5, and i cannot find flag >> SQLITE3_THREADSAFE in source code, so my question is how can i ensure i >> compile with this option? > > Probably there was no such flag in that version yet (I don't know > enough about it). Why don't you use the latest version? > >> The second question is when i met SQLITE_MISUSE error, can i reset or >> reopen >> the database to avoid this error? > > You cannot reset database connection, but you can close it (probably > finalizing all statements before that using > http://www.sqlite.org/c3ref/next_stmt.html if it exist in 3.3.5) and > open it again. > > > Pavel > > On Sun, Jan 10, 2010 at 12:27 AM, tim shen wrote: >> >> hi Pavel, >> Thanks for your reply and reminder. >> I am now using SQLite version 3.3.5, and i cannot find flag >> SQLITE3_THREADSAFE in source code, so my question is how can i ensure i >> compile with this option? >> >> The second question is when i met SQLITE_MISUSE error, can i reset or >> reopen >> the database to avoid this error? >> >> Regards, >> Tim >> >> >> Pavel Ivanov-2 wrote: >>> >>> Oh, and one more thing: sqlite-dev list is for questions related to >>> developing SQLite itself. For problems with using SQLite you should >>> write to sqlite-users list (put in CC). >>> >>> Pavel >>> >>> On Fri, Jan 8, 2010 at 7:50 AM, Pavel Ivanov wrote: As you're new to SQLite you could easily make some incorrect usages of SQLite's API. But we won't be able to say to you exactly what is incorrect until we don't see the actual code. So please show it to us. Incidentally exact value of SQLITE3_THREADSAFE (0, 1 or 2) during compilation is also important as well as any other flags you use. And just out of curiosity: how did you check that the reason "using sqlite_close or calling sqlite_exec with the same database pointer simultaneously from two separate threads" is not applied to you if as you said in the application "you saved this handle as global variable, then in other threads you use it"? Pavel On Fri, Jan 8, 2010 at 3:42 AM, Tim Shen wrote: > Hi All, > > I am new to SQLite and recently I took over a new project which > using > SQLite DB under WinCE. We build the SQlite.dll using flag > SQLITE3_THREADSAFE, and used Multi-Thread programming in the > application. > The problem is that in some operations, function call sqlite3_exec() > will > throw out error SQLITE_MISUSE. > I call the sqlite3_open16() and sqlite3_close() in main thread and > saved > this handle as global variable. Then in other threads, I will use this > global variable to call other sqlite3 function such as > sqlite3_exec(),sqlite3_get_table(),sqlite3_free_table(). > I searched on the sqlite3 site, and got the following information > > SQLITE_MISUSE > This error might occur if one or more of the SQLite API routines is > used > incorrectly. Examples of incorrect usage include calling sqlite_exec > after > the database has been closed using sqlite_close or calling sqlite_exec > with > the same database pointer simultaneously from two separate threads. > > I checked above 2 possible reason may caused this error and make sure > it > is > not caused by them. Any other advise you guys can give it to me? > Thanks > in > advance. > > Regards, > Tim > > ___ > sqlite-dev mailing list > sqlite-...@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev > >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> -- >> View this message in context: >> http://old.nabble.com/Re%3A--sqlite-dev--Why-I-always-got-error-SQLITE_MISUSE-21--*-Library-used-incorrectly-*--tp27075467p27095772.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Re%3A--sqlite-dev--Why-I-always-got-error-SQLITE_MISUSE-21--*-Libra
Re: [sqlite] Speed regression after 3.6.17
On Mon, Jan 11, 2010 at 2:33 AM, Tom Holden wrote: > I have been trying out a number of SQLite managers, one test being the > execution time for the same query on the same database on the same computer. > The scattering of results was very surprising with a spread on the order of > 10:1. > Is the message posted 11 hours ago about the same issue? ( http://www.mail-archive.com/sqlite-users@sqlite.org/msg49650.html) Anyway I still suggest the same (see in the thread) - compare VDBE code sequences Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Why I always got error SQLITE_MISUSE 21 /* Library used incorrectly */
> I am now using SQLite version 3.3.5, and i cannot find flag > SQLITE3_THREADSAFE in source code, so my question is how can i ensure i > compile with this option? Probably there was no such flag in that version yet (I don't know enough about it). Why don't you use the latest version? > The second question is when i met SQLITE_MISUSE error, can i reset or reopen > the database to avoid this error? You cannot reset database connection, but you can close it (probably finalizing all statements before that using http://www.sqlite.org/c3ref/next_stmt.html if it exist in 3.3.5) and open it again. Pavel On Sun, Jan 10, 2010 at 12:27 AM, tim shen wrote: > > hi Pavel, > Thanks for your reply and reminder. > I am now using SQLite version 3.3.5, and i cannot find flag > SQLITE3_THREADSAFE in source code, so my question is how can i ensure i > compile with this option? > > The second question is when i met SQLITE_MISUSE error, can i reset or reopen > the database to avoid this error? > > Regards, > Tim > > > Pavel Ivanov-2 wrote: >> >> Oh, and one more thing: sqlite-dev list is for questions related to >> developing SQLite itself. For problems with using SQLite you should >> write to sqlite-users list (put in CC). >> >> Pavel >> >> On Fri, Jan 8, 2010 at 7:50 AM, Pavel Ivanov wrote: >>> As you're new to SQLite you could easily make some incorrect usages of >>> SQLite's API. But we won't be able to say to you exactly what is >>> incorrect until we don't see the actual code. So please show it to us. >>> Incidentally exact value of SQLITE3_THREADSAFE (0, 1 or 2) during >>> compilation is also important as well as any other flags you use. >>> >>> And just out of curiosity: how did you check that the reason "using >>> sqlite_close or calling sqlite_exec with the same database pointer >>> simultaneously from two separate threads" is not applied to you if as >>> you said in the application "you saved this handle as global variable, >>> then in other threads you use it"? >>> >>> >>> Pavel >>> >>> On Fri, Jan 8, 2010 at 3:42 AM, Tim Shen wrote: Hi All, I am new to SQLite and recently I took over a new project which using SQLite DB under WinCE. We build the SQlite.dll using flag SQLITE3_THREADSAFE, and used Multi-Thread programming in the application. The problem is that in some operations, function call sqlite3_exec() will throw out error SQLITE_MISUSE. I call the sqlite3_open16() and sqlite3_close() in main thread and saved this handle as global variable. Then in other threads, I will use this global variable to call other sqlite3 function such as sqlite3_exec(),sqlite3_get_table(),sqlite3_free_table(). I searched on the sqlite3 site, and got the following information SQLITE_MISUSE This error might occur if one or more of the SQLite API routines is used incorrectly. Examples of incorrect usage include calling sqlite_exec after the database has been closed using sqlite_close or calling sqlite_exec with the same database pointer simultaneously from two separate threads. I checked above 2 possible reason may caused this error and make sure it is not caused by them. Any other advise you guys can give it to me? Thanks in advance. Regards, Tim ___ sqlite-dev mailing list sqlite-...@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- > View this message in context: > http://old.nabble.com/Re%3A--sqlite-dev--Why-I-always-got-error-SQLITE_MISUSE-21--*-Library-used-incorrectly-*--tp27075467p27095772.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Speed regression after 3.6.17
I have been trying out a number of SQLite managers, one test being the execution time for the same query on the same database on the same computer. The scattering of results was very surprising with a spread on the order of 10:1. I followed up with one of the developers and he was able to identify the cause and give me a patch to accelerate his product. What he found was that those competing products that executed the fastest were using an old version of SQLite while the slowest ones used the latest. He was able to identify a speed regression occurring at 3.6.18. His patch was to replace my sqlite3.dll from 3.6.22 with one from 3.6.17. I confirmed the improvement in speed. The query involves the UNION ALL of 5 SELECTs of 4 tables with INNER JOINS plus a self-JOIN on 2 of the SELECTs and an ORDER BY on 1 field. Regards, Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compile error when SQLITE_OMIT_LOAD_EXTENSION
If building amalgamation SQLITE_OMIT_LOAD_EXTENSION then this sqliteInt.h line seems to cause the syntax error: void sqlite3AutoLoadExtensions(sqlite3*); This line should probably be within the #ifndef SQLITE_OMIT_LOAD_EXTENSION Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] REPORT BUG FIXED IN 3.6.22 in select parser (introduced in some version from 3.3.4)
Hello, I wanted to report the BUG again with a clear description and reproduction but on trying to reproduce it with the last version 3.6.22 I've realized that it is fixed! (great!) I send you anyway the minimal way I found to reprode it, maybe can be included in some test Best regards, Alejandro FIXED IN 3.6.22 BUG: Wrong "column not present in both tables" error message in some ways of expressing tables between parenthesis REPRODUCIBLE: Always Refer the table to reproduce it sqlite sqlite 3.3.43.6.21 Query -- --- ok ok CREATE TABLE t1 (x); ok ok CREATE TABLE t2 (y); ok ok CREATE TABLE t3 (x, y, z); ok *FAILS! * SELECT * FROM (t1, t2), t3 USING(x,y); ok *FAILS! * SELECT * FROM (t1, t2), t3 USING(x); ok ok ! SELECT * FROM (t1, t2), t3 USING(y); ok ok ! SELECT * FROM (SELECT * FROM t1, t2), t3 USING(x,y); specifically using sqlite3 command line under windows SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE t1 (x); sqlite> CREATE TABLE t2 (y); sqlite> CREATE TABLE t3 (x, y, z); sqlite> SELECT * FROM (t1, t2), t3 USING(x,y); *Error: cannot join using column x - column not present in both tables* sqlite> SELECT * FROM (t1, t2), t3 USING(x); *Error: cannot join using column x - column not present in both tables* sqlite> SELECT * FROM (t1, t2), t3 USING(y); sqlite> SELECT * FROM (SELECT * FROM t1, t2), t3 USING(x,y); sqlite> --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Sqlite Query Escaping
Am 31.12.2009 um 18:14 schrieb Kees Nuyt: > In short: if you use the sqlite3_prepare*() and > sqlite3_bind*() interface you don't have to escape single > quotes. Only if you compose a complete SQL statement for the > deprecated sqlite3_exec() call and using the sqlite3 command > line tool, where single quotes are used for text literals, > you'd have to worry about escaping single quotes Actually, even when using sqlite3_prepare*(), you'll have to escape quotes if you're _not_ binding all parameters. Obviously, this is very similar to using sqlite3_exec(), but the above quote makes it sound like using sqlite3_prepare*() might be sufficient to avoid escaping... It's actually the use of sqlite3_bind*() that makes escaping unnecessary... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performace regression bug of sqlite 3.6.18
On Mon, Jan 11, 2010 at 4:17 AM, Hub Dog wrote: > I think I found a performance regression bug of sqlite 3.6.1 8. A sql will > cost 1800 seconds to return the query result with sqlite 3.6.18 and with > the > previous version it only cost about 170 seconds. > I have a suggestion for you. If you're able to compare the queries in different versions, can you EXPLAIN them (I suppose you're aware of this operator that should be appended before your query), export results from different sqlite versions into two csv files and compare them as texts (Ms word, diff, whatever). I think there will be not much difference in VDBE code. If there's a difference, post it here. But if the code sequences are the same, there's a probably a problem in your code or data files, not sqlite. I think you're following the idea. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
