[sqlite] New wiki page for Performance Tuning
Hi All, I just created a new wiki page off the wiki home called PerformanceTuning. I have started to place clipets from emails in the group which relate to making queries or DB operations run quicker. Please add your bits and pieces http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning regards Greg O - Original Message - From: Mauricio Piacentini To: [EMAIL PROTECTED] Sent: Thursday, January 15, 2004 1:30 PM Subject: Re: [sqlite] SQLite Browser (Mac OS 10.3) [EMAIL PROTECTED] wrote: >>> ...on Mac OS 10.3? >> >> >> What error you get? > None. That's what's strange about it. It won't even launch. It starts in > the dock but dies one second (literally) later. I can run it on MacOSX 10.3.2. However the version of Qt used to compile the binaries does not support Panther officially, so the widgets do not look correct, specially buttons. There might be other subtle issues with Panther. I will try to find time to compile a newer version against the latest Qt, but please post a request directly to the sourceforge foruns if you have not done so. If you are not using the binaries and have compiled from source you probably do not have Qt setup correctly for static compilation, or your environment is not setup correctly to use Qt shared libraries from the Finder. Since this list is dedicated to SQLite I would recommend checking the Qt forums, or posting to the sqlitebrowser message boards. Regards, Mauricio Piacentini - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] sqlite command-line utility (was: manual? documentat ion?)
> What does the...
> "(one varchar(10), two smallint)"
> ...in the following example...
> "sqlite> create table tbl1(one varchar(10), two smallint);"
> ...mean/do?
It creates the table 'tbl1' shaped like this.
+-+
| one | two |
+-+
| | |
| | |
Don't worry too much about the 'varchar(10)' and 'smallint' for now.
Other databases use this but SQLite just ignores it (not *exactly* true,
it uses it in a subtle way).
Then put some data in it with the command:
INSERT INTO tbl1 VALUES('hello', 'world');
INSERT INTO tbl1 VALUES(1, 2);
+---+
| one | two |
+---+
| hello | world |
| 1 | 2 |
Once there is stuff in the table, inspect it with:
SELECT one, two FROM tbl1;
Or just grab one of the rows:
SELECT one, two FROM tbl1 WHERE one = 'hello';
There you go. Stick "database admin" on your resume. :)
Dan.
[sqlite] Autoincrement not sequential
Hi all, I'm writing a little performance tester app for sqlite (which btw so far produces very impressive results!). I'm filling 100,000 records into a table where the first field is a "integer primary key" so as to let it increment the number on insert automatically. Interestingly, it doesn't produce sequential numbers. Sometimes it skips 1 or 2 numbers. There are no triggers associated with that table. Is that normal? Cheers - Balthasar Indermuehle All science is either physics or stamp collecting. Ernest Rutherford - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Close() and file locks...
> "eno" == eno <[EMAIL PROTECTED]> writes: eno> Marco Bambini wrote: >> After the 2.8.10 release ... "This version fixes a critical locking >> bug in Unix. It turns out that any call to close() clears all locks >> on file that was closed (who knew?) which then left the database >> vulnerable to corruption from other processes. That bug has been >> cleared by embargoing all close() calls until all locks of been >> released." I have made some investigation... From "Advanced >> Programming in the UNIX Environment" by R. Stevens: eno> thank you for this clarification. But what does R. Stevens write eno> about how to circumvent this strange behaviour? I sent some info to the list earlier this week on this subject, but I believe I sent it from the wrong address. Anyway, I also looked through Stevens this past weekend and I didn't find any suggestions in particular to work around this. Although, he does have an explanation of why when close() is called that the kernel can't tell if the file descriptor being closed has the associated lock(s) or not. I can look it up again tonight and post the relevant information if people are interested. After looking at the locking and the IPC sections of Stevens, I came up with the following idea. Please make any suggestions, corrections etc. >From my understanding this problem will only appear within the same process id. An advisory lock will work between different process and this includes across a fork(), but maybe not across an exec(). So the corruption can only occur when 2 or more file descriptors are opened within the same process id. This would include multithreaded applications in which all the threads have the same PID, and it also is evident in Linux where threads have different PID's. As a possible solution, use a shared memory semaphore locking scheme to replace the advisory record locking system currently being used. I thought the generation of the shared memory key_t might be a problem but using ftok() with the database filename should work just fine. I can see an advantage with this system in that you wouldn't need to use a combination of advisory locking (for between processes) with mutexes (for within the same process) to ensure safety. One system, semaphores could be used. Additionally, according to data in Stevens, using a semaphore is faster than using an advisory lock. But I don't think this includes the allocation and destruction of the shared memory used by the semaphores. And locking is probably not a performance bottleneck within SQLite. Of course the huge disadvantage to this system is that if the process with sqlite is killed for some reason, there could be spare shared memory lying around. That may be an unacceptable risk. I haven't completely thought through all the ramifications of using semaphores, but I figured it wouldn't hurt to share my thoughts up to this point. Maybe it will help. enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] vdbe question
hi how can i create vdbe for same special sql query, then check how many row a will get, and for example move to row 100 and fetch 20 rows , its could be just sql cursor but i didn't find it in sqlite .. what can i do about it ? thanks :) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Decodes
Hi, Are there any plans to include a DECODE as a standard built-in function in SQLite? I have written one myself very easily, and it seems to be a nice, quick feature to add. Regards, Steve
[sqlite] Cannot drop trigger
sqlite> drop trigger xx; SQL error: no such trigger: xx sqlite> select name, length(name) from sqlite_master ...> where type='trigger' and name like '%xx%'; xx|2 sqlite> pragma integrity_check; ok I had to dump the database and recreate it to get rid of the trigger. I kept a copy of the db file, but I don't think I can duplicate the problem in a new file. Is this worth pursuing under these circumstances? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] tcl tests on 2.8.11
I've run it on linux and windows and got the same failures.
Did anybody run the test suit on the new version ?
Thanks
Avner
The test output:
date-8.1...
Expected: [{2003-10-26 12:34:00}]
Got: [{2004-01-18 15:31:14}]
date-8.2...
Expected: [{2003-10-27 12:34:00}]
Got: [{2004-01-19 15:31:14}]
date-8.3...
Expected: [{2003-10-28 12:34:00}]
Got: [{2004-01-20 15:31:14}]
date-8.4...
Expected: [{2003-10-22 12:34:00}]
Got: [{2004-01-21 15:31:14}]
date-8.5...
Expected: [{2003-10-01 00:00:00}]
Got: [{2004-01-01 00:00:00}]
date-8.6...
Expected: [{2003-01-01 00:00:00}]
Got: [{2004-01-01 00:00:00}]
date-8.7...
Expected: [{2003-10-22 00:00:00}]
Got: [{2004-01-15 00:00:00}]
date-8.8...
Expected: [{2003-10-23 12:34:00}]
Got: [{2004-01-16 15:31:14}]
date-8.9...
Expected: [{2003-10-23 12:34:00}]
Got: [{2004-01-16 15:31:14}]
date-8.10...
Expected: [{2003-10-23 18:34:00}]
Got: [{2004-01-16 21:31:14}]
date-8.11...
Expected: [{2003-10-21 12:34:00}]
Got: [{2004-01-14 15:31:14}]
*** Giving up...
11 errors out of 22053 tests
Failures on these tests: date-8.1 date-8.2 date-8.3 date-8.4 date-8.5
date-8.6 d
ate-8.7 date-8.8 date-8.9 date-8.10 date-8.11
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
