Re: [sqlite] Sqlite Insert Speed Optimization

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

Re: [sqlite] using test_intarray

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] using test_intarray

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] Documentation error in explanation of "IN subquery" (lang_expr.html)

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] Documentation error in explanation of "IN subquery" (lang_expr.html)

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)

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] Sqlite Insert Speed Optimization

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] Sqlite Insert Speed Optimization

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

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

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

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

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] Documentation error in explanation of "IN subquery" (lang_expr.html)

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

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] Sqlite Insert Speed Optimization

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] Compiling as part of MFC C++ project

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

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] Sqlite Insert Speed Optimization

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] Case insensitive join available?

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] Case insensitive join available?

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 >

[sqlite] Case insensitive join available?

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] Sqlite Insert Speed Optimization

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

Re: [sqlite] Sqlite Insert Speed Optimization

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

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] EXTERNAL:Re: Sqlite Insert Speed Optimization

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

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] Documentation error in explanation of "IN subquery" (lang_expr.html)

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

>>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] EXTERNAL:Re: Sqlite Insert Speed Optimization

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

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

>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] using test_intarray

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

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

[sqlite] using test_intarray

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] Sqlite Insert Speed Optimization

>>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 ?

Re: [sqlite] error in sum function

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] error in sum function

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

> > > 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

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

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

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

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] Sqlite Insert Speed Optimization

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] Database corruption on Linux ext3

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

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

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] EXTERNAL:Re: Sqlite Insert Speed Optimization

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

Re: [sqlite] error in sum function

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] Sqlite Insert Speed Optimization

-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] New 3.7.0 snapshot - release estimated for July 22

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

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] Sqlite Insert Speed Optimization

> 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

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

[sqlite] error in sum function

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

-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] New 3.7.0 snapshot - release estimated for July 22

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