[sqlite] New 3.7.0 snapshot - release estimated for July 22

2010-07-14 Thread D. Richard Hipp
The signature of the VFS has changed slightly - the xShmOpen() method has been removed. Implementations are now expected to automatically create the shared memory on the first call to xShmMap(). And the xShmClose() method has been renamed to xShmUnmap(). A new snapshot with these

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Werner Smit
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Drescher Sent: Tuesday, July 13, 2010 12:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite Insert Speed Optimization > I also wrap my statements

[sqlite] error in sum function

2010-07-14 Thread Riccardo Cohen
Hello I've been using sqlite in many projects (thanks for providing it) and found today someting strange with sum function I have a simple database: CREATE TABLE data (irisid char(9),postid varchar(20),basket integer); CREATE INDEX irisididx on data (irisid); CREATE INDEX postididx on data

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Werner Smit
Thanks for all the feedback! It helped a lot. 1. I'm going to try and see what happen if I leave the "end transaction" until 5 insert was done. 2. I'm going to increase cache_size from 8192 to 16384 Extra info, 1. This program saved data to a clarion file before and in sqlite it's about

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread John Drescher
> Thanks for all the feedback! > > It helped a lot. > 1. I'm going to try and see what happen if I leave the "end transaction" > until 5 insert was done. This is what I meant also when I said 500 was too small. John ___ sqlite-users mailing list

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Paul Corke
On 14 July 2010 11:56, Werner Smit wrote: > 3. I'm saving to a network drive. Is this a one-off data import into a new clean sqlite db? If so have you considered writing to a db file on a local drive and then copying the whole file to the network drive afterwards? Paul.

Re: [sqlite] New 3.7.0 snapshot - release estimated for July 22

2010-07-14 Thread Max Vlasov
On Wed, Jul 14, 2010 at 2:12 PM, D. Richard Hipp wrote: > The signature of the VFS has changed slightly - the xShmOpen() method > has been removed. Implementations are now expected to automatically > create the shared memory on the first call to xShmMap(). And the > xShmClose()

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Werner Smit
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Corke Sent: 14 July 2010 01:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite Insert Speed Optimization On 14 July 2010 11:56, Werner Smit wrote:

Re: [sqlite] error in sum function

2010-07-14 Thread Max Vlasov
Riccardo, please do the following query select typeof(basket) as tp FROM Data WHERE tp<>"integer" as you may know sqlite accepts any value regardless of your desired type, so you possible could have inserted a real value not knowing about it. This query will probably show the rows with such

Re: [sqlite] EXTERNAL:Re: Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
You've got the source code. Modify sqlite3journalopen to put your journal in %TEMP% or or maybe getcwd(). I couldn't quite figure out where the journal filename is set -- there's no db-journal in the code so the name setting appears magjic to me. Since you've got complete control over the

[sqlite] Documentation error in explanation of "IN subquery" (lang_expr.html)

2010-07-14 Thread Igor Sereda
On page http://www.sqlite.org/lang_expr.html : "When a SELECT is the right operand of the IN operator, the IN operator returns TRUE if the SELECT result contains no NULLs and if the left operand matches any of the values in the SELECT result." The part "SELECT result contains no NULLs" does not

Re: [sqlite] Database corruption on Linux ext3

2010-07-14 Thread Jim Wilcoxson
On Wed, Jul 14, 2010 at 1:35 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/13/2010 05:30 PM, Jim Wilcoxson wrote: > > I don't think this would work, because the problem described is that the > > writes aren't making it to disk. If

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Simon Slavin
On 14 Jul 2010, at 11:55am, Werner Smit wrote: > ps. When I started with sqlite it took 500 minutes to save the 1 million > records. > I've got it down to just less than 200 minutes with current settings. > Clarion does it in between 100 and 200 minutes. Do you have any indexes defined ? It

Re: [sqlite] error in sum function

2010-07-14 Thread Riccardo Cohen
I have typeof(basket)=real in all records ... I just see now that all data are float numbers in the text file that was used for inserting rows, and did not notice this because SqliteExpert show only ints ! I understand that sqlite accepts data that are not exactly in declared format (other

Re: [sqlite] error in sum function

2010-07-14 Thread Jean-Christophe Deschamps
Ricardo, >I have typeof(basket)=real in all records ... >I just see now that all data are float numbers in the text file that was >used for inserting rows, and did not notice this because SqliteExpert >show only ints ! SQLite Expert (which I use extensively in the Pro version) enforces

Re: [sqlite] error in sum function

2010-07-14 Thread Simon Slavin
On 14 Jul 2010, at 2:19pm, Riccardo Cohen wrote: > I have typeof(basket)=real in all records ... > I just see now that all data are float numbers in the text file that was > used for inserting rows, and did not notice this because SqliteExpert > show only ints ! > > I understand that sqlite

Re: [sqlite] error in sum function

2010-07-14 Thread Riccardo Cohen
I understand, thanks a lot Jean-Christophe Deschamps wrote: > Ricardo, > >> I have typeof(basket)=real in all records ... >> I just see now that all data are float numbers in the text file that was >> used for inserting rows, and did not notice this because SqliteExpert >> show only ints ! > >

Re: [sqlite] error in sum function

2010-07-14 Thread Bogdan Ureche
> > > SQLite Expert (which I use extensively in the Pro version) enforces > displaying types as declared. So if you declare your column as INTEGER > (or INT, ...) then it will display integers regardless of the actual > individual data type using common conversions. This is a side effect > of

Re: [sqlite] error in sum function

2010-07-14 Thread Tim Romano
I tried this in SQLite Manager for Firefox. CREATE TABLE "PRODUCTS" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "product" TEXT, "rank" INTEGER) insert into products (product, rank) values ('gizmo', 1.0) insert into products (product, rank) values ('widget', 2.0) insert into

Re: [sqlite] error in sum function

2010-07-14 Thread Tim Romano
Ignore the typo: should be 2 | 2.2 As someone who tends to make typogarphical errors, I do like forums with post-editing capabilities much better than mailing lists. Regards Tim Romano Swarthmore PA ___ sqlite-users mailing list

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Werner Smit
>>On 14 Jul 2010, at 11:55am, Werner Smit wrote: >> ps. When I started with sqlite it took 500 minutes to save the 1 million >> records. >> I've got it down to just less than 200 minutes with current settings. >> Clarion does it in between 100 and 200 minutes. >Do you have any indexes defined ?

[sqlite] using test_intarray

2010-07-14 Thread Igor Sereda
Thanks for mentioning test_intarray! I'm now considering rewriting parts of our code because sqlite3_intarray_bind is more powerful than using sequences like ?,?,?... A question: does using a virtual table (or precisely virtual table from test_intarray) affect query optimizer? We have lots of

Re: [sqlite] EXTERNAL:Re: Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
According to my math your final database size should be on the order of 100Meg? That means at 200 minutes and 1,000,000 records: 83 inserts per second 8333 bytes per second Both of these values are terrible. #1 What kind of network connection do you have? 100BaseT? #2 What kind of server

Re: [sqlite] using test_intarray

2010-07-14 Thread Simon Slavin
On 14 Jul 2010, at 4:17pm, Igor Sereda wrote: > For example, where we now have a query > > SELECT ...lots-of-joins... WHERE ...lots-of-exprs... AND someColumn IN > (?,?,?,? ...40 params... ) This -- the 'lots-of-joins' and the '40 params' in particular -- suggests you should be rethinking

Re: [sqlite] EXTERNAL:Re: Sqlite Insert Speed Optimization

2010-07-14 Thread Werner Smit
>According to my math your final database size should be on the order of 100Meg? > >That means at 200 minutes and 1,000,000 records: >83 inserts per second >8333 bytes per second >Both of these values are terrible. >#1 What kind of network connection do you have? 100BaseT? >#2 What kind of

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
Can you answer #3 though? Does anybody know how to make the journal file go to a different location than the database? Apprarently it's not treated as a "temporary" file. Perhaps it should be?? Michael D. Black Senior Scientist Northrop Grumman Mission Systems

Re: [sqlite] EXTERNAL:Re: Sqlite Insert Speed Optimization

2010-07-14 Thread Paul Corke
On 14 July 2010 17:00, Werner Smit wrote: > It DOES sound terrible since 90%? of the time is spend in retrieving > data from a remote oracle server over a slow line. I think you're trying to optimise the wrong thing :) Assuming you can't upgrade that slow line, how about running a compressed

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Werner Smit
>>According to my math your final database size should be on the order of 100Meg? >> >>That means at 200 minutes and 1,000,000 records: >>83 inserts per second >>8333 bytes per second >>Both of these values are terrible. >>#1 What kind of network connection do you have? 100BaseT? >>#2 What

Re: [sqlite] Documentation error in explanation of "IN subquery" (lang_expr.html)

2010-07-14 Thread Richard Hipp
On Wed, Jul 14, 2010 at 8:17 AM, Igor Sereda wrote: > > On page http://www.sqlite.org/lang_expr.html : > > "When a SELECT is the right operand of the IN operator, the IN operator > returns TRUE if the SELECT result contains no NULLs and if the left operand > matches any of the

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
Now I'm confused...how can you be 50% faster if 90% of the time is in retrieving from Oracle? Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Werner Smit Sent: Wed 7/14/2010 11:11 AM To:

Re: [sqlite] EXTERNAL:Re: Sqlite Insert Speed Optimization

2010-07-14 Thread Werner Smit
On 14 July 2010 17:00, Werner Smit wrote: > It DOES sound terrible since 90%? of the time is spend in retrieving > data from a remote oracle server over a slow line. I think you're trying to optimise the wrong thing :) Assuming you can't upgrade that slow line, how about running a compressed

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Eric Smith
Black, Michael (IS) wrote: > Does anybody know how to make the journal file go to a different > location than the database? Apprarently it's not treated as a "temporary" > file. Perhaps it should be?? Seems like you'd have to communicate the journal location to other processes, meaning

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
If you don't know why it's slow you'll be shooting in the dark. And doing compression on a local network link isn't likely to buy you much. Might even hurt. In other words, is it latency or bandwidth? Give 8K/sec I'm guessing it's latency unless you're running a 64KBit line. Are you THAT

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
If you could set the journcal location BEFORE you open the database that wouldn't be such a bad thing. Giving us the ability to do this would allow for the flexibility when needed with appropriate warnings about how to recover. In particular, if you only access the database through your own

[sqlite] Case insensitive join available?

2010-07-14 Thread Peng Yu
Hi, I'm wondering if there is a syntax to do case insensitive join. Of course, I could generate two new tables, with the both joining columns converted to lower case (or upper case). But I'd like a simpler solution. #!/usr/bin/env bash rm -f main.db sqlite3 main.db

Re: [sqlite] Case insensitive join available?

2010-07-14 Thread Jay A. Kreibich
On Wed, Jul 14, 2010 at 11:36:06AM -0500, Peng Yu scratched on the wall: > Hi, > > I'm wondering if there is a syntax to do case insensitive join. Of > course, I could generate two new tables, with the both joining columns > converted to lower case (or upper case). But I'd like a simpler >

Re: [sqlite] Case insensitive join available?

2010-07-14 Thread Black, Michael (IS)
Add "collate nocase" to your queries. sqlite> select * from A inner join B on A.a=B.a collate nocase; a b c a d e -- -- -- -- -- -- a 4 7 A 4 7

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Werner Smit
If you don't know why it's slow you'll be shooting in the dark. And doing compression on a local network link isn't likely to buy you much. Might even hurt. In other words, is it latency or bandwidth? Give 8K/sec I'm guessing it's latency unless you're running a 64KBit line. Are you THAT

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Eric Smith
Werner Smit wrote: > After taking out count(*) and adding a few pragma's and saving 6000 > records rather than 500 at a time I've got it down to 34 minutes. > If I build in on local drive it takes 28 minutes.(with chunks of 500) Why not do an apples-to-apples test and commit the same number

Re: [sqlite] Compiling as part of MFC C++ project

2010-07-14 Thread GHCS Software
Thanks. That took care of it. I'm revamping a Windows app that I wrote 6 or 7 years ago and that was the last time I used Visual C++ for much of anything. I just wasn't sure about mixing C and C++ code in a single build. Doug Gordon *GHCS Software* From: Jim

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
Yuck...do you know what bandwidth you have? Looks kind of like a T1 line to me. That should allow 100Meg to come across in about 13 minutes if it's not being used for anything else. Unless you're monitoring time usage in your application how do you know where your time is being spent? If

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Richard Hipp
On Wed, Jul 14, 2010 at 12:31 PM, Black, Michael (IS) < michael.bla...@ngc.com> wrote: > If you could set the journcal location BEFORE you open the database that > wouldn't be such a bad thing. Giving us the ability to do this would allow > for the flexibility when needed with appropriate

Re: [sqlite] Documentation error in explanation of "IN subquery" (lang_expr.html)

2010-07-14 Thread Simon Slavin
On 14 Jul 2010, at 5:13pm, Richard Hipp wrote: > Improvements to the IN operator documentation can be found here: > > http://www.sqlite.org/draft/lang_expr.html#in_op I find that table difficult to understand: you have some mutually exclusive columns. Could it be replaced by this

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
Was that a facetious remark??? Rather than "here's a function/pragma that allows you to put the journal file where you want -- but BE CAREFUL BECAUSE..." Writing you own VFS is not for the casual user... I was trying just to find where the journal filename was created but there's no

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Richard Hipp
On Wed, Jul 14, 2010 at 1:34 PM, Black, Michael (IS) wrote: > Was that a facetious remark??? > > Rather than "here's a function/pragma that allows you to put the journal > file where you want -- but BE CAREFUL BECAUSE..." > > Writing you own VFS is not for the casual

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Simon Slavin
On 14 Jul 2010, at 5:02pm, Black, Michael (IS) wrote: > Does anybody know how to make the journal file go to a different location > than the database? Apprarently it's not treated as a "temporary" file. > Perhaps it should be?? It's essential not to treat the journal file as a temporary

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
It sounds like you've been drinking Microsoft's kool-aid -- don't let the user do what they need to do...it's too dangerous. Just try and print a directory list from your file browser in Windows for example (not that it's dangerous..but it's pretty obvious someone might want to do it).

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Richard Hipp
On Wed, Jul 14, 2010 at 1:51 PM, Richard Hipp wrote: > > > On Wed, Jul 14, 2010 at 1:34 PM, Black, Michael (IS) < > michael.bla...@ngc.com> wrote: > >> Was that a facetious remark??? >> >> Rather than "here's a function/pragma that allows you to put the journal >> file where you

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Black, Michael (IS)
Jolly good...just the noose we need :-) I also was thinking of memory mode for the journal. That's probably the best solution for this kinda of case. Though I think the original poster was wanting a recovery mechanism. Michael D. Black Senior Scientist Northrop Grumman Mission Systems

Re: [sqlite] Documentation error in explanation of "IN subquery" (lang_expr.html)

2010-07-14 Thread Dan Kennedy
On Jul 15, 2010, at 12:25 AM, Simon Slavin wrote: > > On 14 Jul 2010, at 5:13pm, Richard Hipp wrote: > >> Improvements to the IN operator documentation can be found here: >> >>http://www.sqlite.org/draft/lang_expr.html#in_op > > I find that table difficult to understand: you have some

Re: [sqlite] Documentation error in explanation of "IN subquery" (lang_expr.html)

2010-07-14 Thread Richard Hipp
On Wed, Jul 14, 2010 at 1:25 PM, Simon Slavin wrote: > By the way, can someone explain why this rule, equivalent to line 4 of the > table, is there: > > elseif the right op contains NULL, then IN = NULL > > By the time we've got there we already know that the left

Re: [sqlite] Documentation error in explanation of "IN subquery" (lang_expr.html)

2010-07-14 Thread Simon Slavin
On 14 Jul 2010, at 7:22pm, Richard Hipp wrote: > On Wed, Jul 14, 2010 at 1:25 PM, Simon Slavin wrote: > >> By the way, can someone explain why this rule, equivalent to line 4 of the >> table, is there: >> >> elseif the right op contains NULL, then IN = NULL > >

Re: [sqlite] using test_intarray

2010-07-14 Thread Igor Sereda
Simon, I would agree in other cases, however, in our app, the schema is dynamic and depends on the user data. Normally, we have about 200 tables with two or three columns each, with indexes almost on each table. Queries with lots-of-joins are also constructed dynamically, and we found SQLite to

Re: [sqlite] using test_intarray

2010-07-14 Thread Simon Slavin
On 14 Jul 2010, at 8:22pm, Igor Sereda wrote: > I would agree in other cases, however, in our app, the schema is dynamic and > depends on the user data. Normally, we have about 200 tables with two or > three columns each, with indexes almost on each table. Queries with > lots-of-joins are also

Re: [sqlite] Sqlite Insert Speed Optimization

2010-07-14 Thread Jay A. Kreibich
On Wed, Jul 14, 2010 at 12:34:05PM -0500, Black, Michael (IS) scratched on the wall: > Was that a facetious remark??? > > Rather than "here's a function/pragma that allows you to put the > journal file where you want -- but BE CAREFUL BECAUSE..." > > Writing you own VFS is not for the casual