Re: [sqlite] Multiple clients accessing one DB

2011-03-21 Thread thilo
On 2/9/2011 7:42 PM, Sam Carleton wrote: > Currently I have two and sometimes three clients access the SQLite db, all > on the same machine. > > * A C# program that doesn't ever stay connection all that long. > * An Apache application that stays connected all the time. > * A Qt application that sta

Re: [sqlite] Bulk loading large dataset; looking for speedups.

2011-03-21 Thread Simon Slavin
On 22 Mar 2011, at 1:27am, Douglas Eck wrote: > I fixed the slowness by sorting the sql statements in ascending key > order and dumping to a new text file, then running sqlite on the new > text file. That *really* helped :-) Neat. Must remember that sorting improves speed that much. Simon. __

Re: [sqlite] Bulk loading large dataset; looking for speedups.

2011-03-21 Thread Simon Slavin
On 22 Mar 2011, at 12:47am, Douglas Eck wrote: > I0321 16:48:04.492315 3924 gordon_generate_database.py:120] Running > sqlite3 /tmp/test.db < > /tmp/gordon_generate_databaseu0g1_c/gordon_sql-7-of-00020. > I0321 16:59:39.222325 3924 gordon_generate_database.py:123] Ran in > 694.731889009 sec

Re: [sqlite] Bulk loading large dataset; looking for speedups.

2011-03-21 Thread Douglas Eck
I fixed the slowness by sorting the sql statements in ascending key order and dumping to a new text file, then running sqlite on the new text file. That *really* helped :-) -Doug On Mon, Mar 21, 2011 at 5:47 PM, Douglas Eck wrote: > Hi all, > I've got a reasonably large dataset to load via sql i

[sqlite] Bulk loading large dataset; looking for speedups.

2011-03-21 Thread Douglas Eck
Hi all, I've got a reasonably large dataset to load via sql insert statements (3.3M inserts spread out over 4 or 5 tables) into an empty database. The Db is created using sqlalchemy. I then drop all indexes using sql statements. The inserts are performed by calling sqlite3 my.db < my_sql_text_file

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Simon Slavin
On 21 Mar 2011, at 5:46pm, Enrico Thierbach wrote: > On 21.03.2011, at 06:34, Simon Slavin wrote: > >> Suppose you have a TABLE employees with ten thousand rows and no indexes, >> and you execute >> >> SELECT id,firstname,surname FROM employees WHERE firstname='Guilherme' AND >> age=46 >> >>

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Enrico Thierbach
Hi Simon, I just stumbled about this post. > On 21.03.2011, at 06:34, Simon Slavin wrote: > > Suppose you have a TABLE employees with ten thousand rows and no indexes, and > you execute > > SELECT id,firstname,surname FROM employees WHERE firstname='Guilherme' AND > age=46 > > The quer

Re: [sqlite] sqlite-users Digest, Vol 39, Issue 20

2011-03-21 Thread Enrico Thierbach
Hi Udon, to come to an possibly productive end here: if you think an amd64 compilation would be useful to you (I do doubt that though :(, drop me a note and I will fire up a compiler run on one of my servers, and tar the results. /eno > > @eno > I'm sure RPMs are really cool. But previously

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Simon Slavin
On 21 Mar 2011, at 2:17pm, Guilherme Batista wrote: > So, in general, the the ANALYZE is not advantageous only if I have just 1 > index in my table, in that case the sqlite never use the ANALYZE... If you have exactly one INDEX which looks helpful for the query, SQLite will just use that. It w

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Richard Hipp
On Mon, Mar 21, 2011 at 9:48 AM, Simon Slavin wrote: > > Note that if you provide good INDEXes as you should, then the results of > ANALYZE will never be used at all because the query finder will find it has > an excellent INDEX before it even starts trying to guess how to access the > data by a

Re: [sqlite] In-memory database with persistent storage

2011-03-21 Thread Simon Friis
Thanks! The Online Backup API does exactly what I want. 2011/3/21 Drake Wilson : > That depends on your application.  In general if you think too many > file accesses is a speed problem and you are willing to spend more > memory to deal with it, some of the first things to try would be > fiddling

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Simon Slavin
On 21 Mar 2011, at 12:12pm, Guilherme Batista wrote: > 1) So if I run ANALYZE, it will gather statistics about all columns in all > tables from the database? You can add parameters to the end of the command: http://www.sqlite.org/lang_analyze.html but without them it will analyze everything, j

Re: [sqlite] In-memory database with persistent storage

2011-03-21 Thread Drake Wilson
Quoth Simon Friis , on 2011-03-21 14:13:46 +0100: > Is is possible to make SQLite load a database file into memory and > then save it back to the file again when the connection to the > database is closed? You cannot do that with a single database exactly, but you can use the Online Backup API

Re: [sqlite] In-memory database with persistent storage

2011-03-21 Thread Igor Tandetnik
Simon Friis wrote: > I know how to create a database that exists only in memory by using > the :memory: filename. This however, creates a new database every time > and it can not be saved. It can, with backup API: http://www.sqlite.org/backup.html > Is is possible to make SQLite load a database

[sqlite] In-memory database with persistent storage

2011-03-21 Thread Simon Friis
I know how to create a database that exists only in memory by using the :memory: filename. This however, creates a new database every time and it can not be saved. Is is possible to make SQLite load a database file into memory and then save it back to the file again when the connection to the data

Re: [sqlite] Sorting of Japanese Language

2011-03-21 Thread ashish yadav
Hi , I have looked into ICU , but ICU sort Japanese language using JIS x 4601(Japanese Industrial Standards). So, i am looking for other solution ... Thanks & Regards Ashish On Mon, Mar 21, 2011 at 6:15 PM, Igor Tandetnik wrote: > ashish yadav wrote: > > I am working on one application

Re: [sqlite] Sorting of Japanese Language

2011-03-21 Thread Igor Tandetnik
ashish yadav wrote: > I am working on one application which need Sorting in Japanese language. See if SQLite ICU extension supports what you want: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt If it doesn't, then you'll have to implement a custom collation: http://www.sqli

[sqlite] Sorting of Japanese Language

2011-03-21 Thread ashish yadav
Hi , I am working on one application which need Sorting in Japanese language. Sorting of Japanese needs to have Katakana and Kanji converted to Hiragana and then sorted according to the UTF-8 code. The Hiragana, Katakana, and Kanji characters shall be combined together and sorted by the Hiragana

Re: [sqlite] Help me understand the sqlite query optimizer

2011-03-21 Thread Guilherme Batista
Thanks for the big and helpful explanation Simon! My changes in my study, theoretically, would improve the performance just of big and complex queries, so it's not my intention improve the sqlite, I want just study the effects.. Just a few more questions if anyone can help me 1) So if I run ANALY

Re: [sqlite] what is the tool name which worked as db2'sperformance monitor

2011-03-21 Thread Igor Tandetnik
Simon Slavin wrote: > On 21 Mar 2011, at 6:59am, zeal wrote: > >> i just want to find a tool in oracle which could format the log and tell >> me who eat much cpu time. as i know, it name is >> performance monitor, but i do not know which could do this job in oracle. > > You're posting to a m

Re: [sqlite] Newbie question

2011-03-21 Thread Igor Tandetnik
Kai Peters wrote: > given a table with two columns (SaleDate, SaleVolume) is it possible in one > query to obtain > the following three column result set: > > SalesCurrentYear, SalesLastYEar, SalesAllyears select sum(SaleVolume * (SaleDate >= StartOfCurYear)) SalesCurrentYear, sum(SaleV

Re: [sqlite] Newbie question

2011-03-21 Thread Black, Michael (IS)
Try this. BEGIN TRANSACTION; CREATE TABLE sales(SaleDate date,SaleVolume int); INSERT INTO "sales" VALUES('2010-01-01', 10); INSERT INTO "sales" VALUES('2010-01-02', 20); INSERT INTO "sales" VALUES('2011-01-01', 15); INSERT INTO "sales" VALUES('2011-01-02', 30); INSERT INTO "sales" VALUES('2009-01

Re: [sqlite] what is the tool name which worked as db2's performance monitor

2011-03-21 Thread Simon Slavin
On 21 Mar 2011, at 6:59am, zeal wrote: > i just want to find a tool in oracle which could format the log and tell me > who eat much cpu time. as i know, it name is performance monitor, but i do > not know which could do this job in oracle. You're posting to a mailing list about SQLite. You