Re: [sqlite] Version 3.3.14
Grate ! I find the "amalgamation" very useful in integrating SQLite in existing source trees, compiling it using various flags together with other sources etc. There is one thing that I always have to modify: In function sqlite3UnixCurrentTime, comming from os_unix.c, the call to gettimeofday takes an unused parameter, a pointer to sTz. For various reasons I have to compile using "-std=c99" flag, in which case "struct timezone" is not declared (or I didn't figured out yet a some other parameters). In any case, I think it is fairly safe to replace struct timezone sTz; /* Not used */ gettimeofday(&sNow, &sTz); with: gettimeofday(&sNow, NULL); This may also save a little space on the stack. From what I know the second parameter was never used on Linux; the manual page from other systems also states that if the second parameter is not null, then the behavior of the function is not specified. So it seems like a safer bet to pass NULL as the second parameter. I'm relatively new to SQLite, and I'm quite swamped with an upcoming release of my project, so please excuse me if I didn't find out yet what is the proper way of sending this kind of "simple stupid piece of code" (patch, discussion list, private email, etc). Cheers and many thanks for SQLite! -iulian [EMAIL PROTECTED] wrote: SQLite version 3.3.14 is now available on the SQLite website [...] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] testing with single source file
[EMAIL PROTECTED] wrote: [...] In past releases of SQLite, we have made available a ZIP archive with preprocessed source files. In the future, we may change this so that instead of a ZIP archive full of individual files, we ship a single "sqlite3.c" source file which contains all of the source file in a single translation unit. By this mode of delivery, we hope to enable users to see the performance improvements we are seeing in our red bars. I just want to thanks for adding the "sqlite3.c" target to the makefile! All tests run without problems compiled with a sqlite3.c produced from CVS head ! Few notes: 1. I had to change the declaration of these two functions in test7.c: void sqlite3_server_start(void); void sqlite3_server_stop(void); 2. I compiled everything using "-Wall -O3"; fixed few compiler warnings related to unused or not initialized variables (probably irrelevant). 3. I didn't used -DSQLITE_MEMDEBUG, so all malloc and vtab_err tests were skipped. 4. System used: Linux, i686, gcc 3.4.2, glibc 2.3.3, Pentium4/3.00GHz/1MB L2. Cheers, -Iulian - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA locking_mode = EXCLUSIVE
Really interesting. Is there an easy way of producing the big "sqlite3.c" from CVS head? I'm curios to run some of my tests this way. -i [EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: What operations/work patterns will benefit most from PRAGMA locking_mode = EXCLUSIVE? Can you roughly quantify the speedups in such cases? When you do "PRAGMA locking_mode=EXCLUSIVE", it means that SQLite will not release its EXCLUSIVE lock after its does its first write until you either close the connection or you do "PRAGMA locking_mode=NORMAL". There are various optimizations that can occur when this is the case. There is a plot of runtime of CVSHEAD versus version 3.3.13 at http://www.sqlite.org/relspeed-20070326-ephemeral.gif (As the name implies, this GIF will ephemeral. Do not expect it to be there for more than a few days.) Any value less than 1.0 means that performance has improved. The test script is http://www.sqlite.org/cvstrac/fileview?f=sqlite/test/speed2.test The orange bars show the relative performance of CVSHEAD with no special build options. 1.0 is the performance of version 3.3.13 so anything less than 1.0 is an improvement. The red bars show CVSHEAD with all the source files concatenated into a single big file and compiled as a single translation unit. Compiling this way allows the compiler to do additional optimizations that result in improved performance. The blue bars are the same sqlite-as-one- great-big-source-file optimization but with -DSQLITE_OMIT_XFER_OPT=1 defined. The tests are cumulative. The database that results from one test feeds into the next. The two "insert" tests at the top both consists of a large number (5) of distinct INSERT statements. These tests are dominated by the parser and code generator and all of our recent work has been directed toward optimizing the backend so these tests do not show any improvement. You can see that the new "XFER Optimizer" actually results in a slight performance drop since for each INSERT statement, the parser has to check to see if the XFER optimization applies. (It never does in the first two tests.) The interface between the B-Tree layer and the Pager layer has been refactored. This refactoring prevented the compiler from doing certain function inlinings that it was able to do before, resulting in a performance drop for the orange bar. But when the pager and b-tree modules were combined into a single source file, the function inlining was able to occur again and so performance improved. Tests select4 through select6 are about twice as fast. We believe this is because the pager cache is no longer flushed unless the database is changed by another process. VACUUM is much faster due to the XFER optimization. Furthermore, VACUUM with the XFER optimization greatly reduces th database fragmentation. This is (we believe) why performance is so much better in the later tests for the orange and red bars. Operations on a defragmented database file go much faster. Changes have been made to the b-tree and pager layers that omit certain unnecessary writes to the database file and to the rollback journal. This reduction in disk I/O results in the 20x performance improvements seen for tests delete1 and drop1. CVSHEAD passes the "quick" regression tests. But we know there are still issues with the code. CVSHEAD is not currently recommended for use in shipping products, but it is adequate for development work, we believe. In past releases of SQLite, we have made available a ZIP archive with preprocessed source files. In the future, we may change this so that instead of a ZIP archive full of individual files, we ship a single "sqlite3.c" source file which contains all of the source file in a single translation unit. By this mode of delivery, we hope to enable users to see the performance improvements we are seeing in our red bars. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] round and storage class
Hello everybody ! First of all I have to say that I'm a fairly new user of SQLite, so be kind :-) The problem is with the "round" function: the result have a decimal point even if the second argument is zero (the default): sqlite> select round(1.234); 1.0 sqlite> select round(1.234,0); 1.0 I'm not sure if this shouldn't produce '1' instead of '1.0'. The reason for bringing this up is the resulted storage class in a statement like this: INSERT INTO tableint SELECT ROUND(some_expression) FROM other_table; I know that in theory this shouldn't matter for SQLite, but I suppose that some queries would be more efficient if the storage class is INTEGER for the columns involved (please tell me if I'm wrong here). So, here are the questions: 1. If you feel that round should behave this way, how do I submit a patch. Not a lot of modifications nor terrible smart (basically some work done at the end of roundFunc from func.c), but I'm also curios how one can submit patches (sorry if I missed this info from sqlite.org). 2. Is there a way to find the storage class for a particular field from a particular row of a table? I'm not sure for example if the next statements will produce the same storage class: create tableint (i integer); insert into tableint values(1); insert into tableint values(1.0); insert into tableint values(round(1)); I'm just getting used with VDBE, but from running these with "explain" I think everything is clear until reaching "MakeRecord". For example, the first statement would produce something like: cut - 5|Integer|1|0| 6|MakeRecord|1|0|d cut - And the second: cut - 5|Real|0|0|1.0 6|MakeRecord|1|0|d cut - The 'd' from MakeRecord is just the column affinity, doesn't specify how the actual record was stored: sqlite> explain insert into tableint values(1.5); ... 5|Real|0|0|1.5 6|MakeRecord|1|0|d ... Same code, but obviously 1.5 is stored as real. Regards, -Iulian - To unsubscribe, send email to [EMAIL PROTECTED] -