Re: [sqlite] Sqlite Insert Speed Optimization

[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 user...
>  
> I was trying just to find where the journal filename was created but
> there's no "db-journal" string in either the .h or .c file.
>  
> It would be trivial to add a function to set it via the C interface.

  This is a polite way of saying, "This a *really* Bad Idea.  There is
  a big gun over there.  Have fun shooting yourself in the foot.  Can
  we take pictures?"

  If you know enough about the whole system to understand the full
  implications of moving the journal file, chances are you know enough
  about the whole system to make the VFS modifications without serious 
  thought.  If you don't, you should likely think twice about hacking
  up a fundamental transaction behavior and dismissing years and years
  of practical knowledge and experience. 

  Adding a function would be trivial... which means there are really
  good reasons why it isn't there.




  There is a really big gun right here (3.6.23.1):

$ grep -n \"-journal\" sqlite3.c
35406:memcpy(&pPager->zJournal[nPathname], "-journal", 8);

  You're not finding "db-journal" because the "db" comes from your
  application.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using test_intarray

[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 constructed dynamically, and we found SQLite to be
> pretty effective in selecting optimal join algorithm based on ANALYZE
> results.

Fair enough.  You seem to be dynamically generating your schema, which is 
unusual for a SQL user.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using test_intarray

[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 be
pretty effective in selecting optimal join algorithm based on ANALYZE
results.

Likewise, there are reasons for needing "40 params", or otherwise pass "IN
(int array)" in a WHERE clause; for example, to verify that a given set of,
say, 40 entities would be returned from a query that returns 100 000
entities -- without running the query itself. (This can be solved by
creating a temporary table, inserting entity ids there and joining that
table with the query - however, we're looking for a more efficient way.)

Thanks!
Igor



Simon Slavin-3 wrote:
> 
> 
> 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 your schema.  You'll probably speed up your
> entire system and save a lot of very complicated programming by merging
> some tables and/or making some separate columns of a table into a
> collection of keyed properties.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/binding-an-IN-tp29135222p29165733.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
> 
> NULL should be interpreted as "unknown".  Hence in:
> 
>  5 IN (1,2,3,4,NULL,6,7)
> 
> we do *not* definitely know that 5 is not in the RHS.

Wow.  I'd never have guessed that.  Thanks to both Dan and Richard for fast 
answers.  That makes both the table and the test-set I suggested far more easy 
to understand.  Perhaps that explanation could be included in the explanation 
of 'IN'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 op is not NULL.
>  Why does the handling of a NULL right op require a special case like that ?
>  We definitely know that the left op is not IN it.
>

NULL should be interpreted as "unknown".  Hence in:

  5 IN (1,2,3,4,NULL,6,7)

we do *not* definitely know that 5 is not in the RHS.  5 may or may not be
in the RHS depending on what the value of the NULL is.  Since we don't know
if 5 is in the RHS, the result of the expression is also unknown, or in
SQL-speak: NULL.




>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 mutually  
> exclusive columns.  Could it be replaced by this sequence of four  
> tests and an 'otherwise' ?
>
>   If the right op is the empty list, then IN = false
> else  if the left op is NULL, thenIN = NULL
> else  if the right op contains left op, then  IN = true
> else  if the right op contains NULL, then IN = NULL
> otherwise IN = false
>
> Also NOT true = false, NOT false = true, NOT NULL = NULL.
>   
> That should match the source code, so it should be better  
> documentation.
>
> By the way, can someone explain why this rule, equivalent to line 4  
> of the table, is there:
>
> else  if the right op contains NULL, then IN = NULL
>
> By the time we've got there we already know that the left op is not  
> NULL.  Why does the handling of a NULL right op require a special  
> case like that ?  We definitely know that the left op is not IN it.

Thread here:

   http://www.mail-archive.com/sqlite-users@sqlite.org/msg34989.html

He can be a tricky guy, NULL.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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
 



From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Wed 7/14/2010 1:12 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL: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 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 "db-journal" string in either the .h or .c file.
>>
>> It would be trivial to add a function to set it via the C interface.
>>
>
> Putting the rollback journal in any directory other than the same directory
> as the database file is an dangerous thing to do, because it risks being
> unable to locate the rollback journal after a crash, resulting in database
> corruption.  Hence, we have no intention of supporting such a feature.  If
> you really need it badly enough, you can write your own VFS to make it
> happen.  Yes, writing your own VFS is hard to do.  But this serves to
> discourage people from doing it, which is what we want.
>
>
Well, I'm wrong.  Turns out we are going to help you hang yourself after
all:  I forgot about these features:

 PRAGMA journal_mode=MEMORY;
 PRAGMA journal_mode=OFF;

Set one of those and you get no disk I/O from reading or writing the journal
file.  And you will corrupt your database on a crash.  On your own head be
it.


>
> --
> -
> D. Richard Hipp
> d...@sqlite.org
>



--
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 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 "db-journal" string in either the .h or .c file.
>>
>> It would be trivial to add a function to set it via the C interface.
>>
>
> Putting the rollback journal in any directory other than the same directory
> as the database file is an dangerous thing to do, because it risks being
> unable to locate the rollback journal after a crash, resulting in database
> corruption.  Hence, we have no intention of supporting such a feature.  If
> you really need it badly enough, you can write your own VFS to make it
> happen.  Yes, writing your own VFS is hard to do.  But this serves to
> discourage people from doing it, which is what we want.
>
>
Well, I'm wrong.  Turns out we are going to help you hang yourself after
all:  I forgot about these features:

 PRAGMA journal_mode=MEMORY;
 PRAGMA journal_mode=OFF;

Set one of those and you get no disk I/O from reading or writing the journal
file.  And you will corrupt your database on a crash.  On your own head be
it.


>
> --
> -
> D. Richard Hipp
> d...@sqlite.org
>



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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).
 
Software should ALWAYS allow for an expert mode.  Where we can do dangerous 
things when we need to (for example keeping your journal on local file storage 
when your database is on network storage).
 
Just like it's stated that it's dangerous to use network storage but you allow 
it nonetheless.  That is MUCH more likely to happen that someone trying to 
store the journal elsewhere.  So how do you justity allowing that to occur?  
Seems you're being a bit non-orthogonal.
 
If you really needed to recover such a database you just have to move the 
journal file to the database directory.  
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Wed 7/14/2010 12:51 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL: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 user...
>
> I was trying just to find where the journal filename was created but
> there's no "db-journal" string in either the .h or .c file.
>
> It would be trivial to add a function to set it via the C interface.
>

Putting the rollback journal in any directory other than the same directory
as the database file is an dangerous thing to do, because it risks being
unable to locate the rollback journal after a crash, resulting in database
corruption.  Hence, we have no intention of supporting such a feature.  If
you really need it badly enough, you can write your own VFS to make it
happen.  Yes, writing your own VFS is hard to do.  But this serves to
discourage people from doing it, which is what we want.


--
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 file (e.g put it in 
/tmp for a Unix environment).  Temporary directories are wiped out at boot 
time, whereas a SQLite journal file is used after a crash and reboot to recover 
your database to a sane and usable state.

And I agree with other people in this thread: it is pointless for you to mess 
about trying to optimize the operation of SQLite at this stage because your 
bottleneck to performance is the speed of your network link.  You might somehow 
achieve a 5% improvement in speed by endless messing with SQLite whereas 
getting a faster path to your remote storage might get you a 50% improvement.  
Take a look at your network toplogy, the priority settings on your switches, 
etc..

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 user...
>
> I was trying just to find where the journal filename was created but
> there's no "db-journal" string in either the .h or .c file.
>
> It would be trivial to add a function to set it via the C interface.
>

Putting the rollback journal in any directory other than the same directory
as the database file is an dangerous thing to do, because it risks being
unable to locate the rollback journal after a crash, resulting in database
corruption.  Hence, we have no intention of supporting such a feature.  If
you really need it badly enough, you can write your own VFS to make it
happen.  Yes, writing your own VFS is hard to do.  But this serves to
discourage people from doing it, which is what we want.


-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 
"db-journal" string in either the .h or .c file.
 
It would be trivial to add a function to set it via the C interface.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Wed 7/14/2010 12:12 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL: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 warnings about how to
> recover.
>

You can write your own VFS that places the journal file wherever you want.



--
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 sequence of four tests and an 
'otherwise' ?

If the right op is the empty list, then IN = false
elseif the left op is NULL, thenIN = NULL
elseif the right op contains left op, then  IN = true
elseif the right op contains NULL, then IN = NULL
otherwise   IN = false

Also NOT true = false, NOT false = true, NOT NULL = NULL.

That should match the source code, so it should be better documentation.

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 op is not NULL.  Why 
does the handling of a NULL right op require a special case like that ?  We 
definitely know that the left op is not IN it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 warnings about how to
> recover.
>

You can write your own VFS that places the journal file wherever you want.



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 the T1 line is 
busy that will slow you down more and if you're not measuring both Oracle and 
Sqlite time independently how do you know??
 
I would optimize your local write first to see what the optimum transaction 
size is and other options you can set.  Then use those options on your network 
write.
 
And have you considered trying 3.7.0 and use WAL?  Seems like your application 
might benefit quite a bit from that.
 
You could also split your app in 2 (this would work witih CLAR too).  Have your 
Oracle app write to a named pipe and your CLAR/SQlite read the data from the 
name pipe.  Then your Oracle app can trot off to retrieve another record whild 
your DB app writes to your database.  This is a pretty simple way to do 
multitasking without writing code and would get rid of some your latency.
 
I assume you've got a test data file with all your SQL statements in it that 
you can test with?  That's the best way to optimize your local database writing 
options.
 
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:43 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL: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 slow???

What's your ping time latency look like? 


Ping Latency :

Pinging allpay [196.11.183.3] with 32 bytes of data:

Reply from 196.11.183.3: bytes=32 time=115ms TTL=250
Reply from 196.11.183.3: bytes=32 time=175ms TTL=250
..
Reply from 196.11.183.3: bytes=32 time=228ms TTL=250
Reply from 196.11.183.3: bytes=32 time=264ms TTL=250

Ping statistics for 196.11.183.3:
Packets: Sent = 8, Received = 8, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 74ms, Maximum = 264ms, Average = 178ms





DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

You need to use the project properties to set the file as a C file and 
to not use precompiled headers

On 7/13/2010 5:47 PM, GHCS Software wrote:

> > What do I need to do to get sqlite3.c to compile in a MFC C++ project
> > (Visual C++)? If I just add it to the project, I end up getting a
> > compile error something like: "Unexpected end of file while searching
> > for pre-compiled header directive".
> >
> > Do I need to rename it "sqlite3.cpp" and put the include of
> > at the top? Or what??
> >
>   



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 of 
records per batch in each test?  The idea was to vary only one thing 
(the mount point) and keep all other variables constant.  

250ms ping times, wow.  SQLite write speeds will be better if the NFS 
server is on the same planet as the client.  

-- 
Eric A. Smith

Software is like entropy.  It is difficult to grasp, weighs nothing,
and obeys the Second Law of Thermodynamics, i.e., it always increases.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 slow???
 
What's your ping time latency look like?  
 

Ping Latency : 

Pinging allpay [196.11.183.3] with 32 bytes of data:

Reply from 196.11.183.3: bytes=32 time=115ms TTL=250
Reply from 196.11.183.3: bytes=32 time=175ms TTL=250
..
Reply from 196.11.183.3: bytes=32 time=228ms TTL=250
Reply from 196.11.183.3: bytes=32 time=264ms TTL=250

Ping statistics for 196.11.183.3:
Packets: Sent = 8, Received = 8, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 74ms, Maximum = 264ms, Average = 178ms





DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Case insensitive join available?

[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
b   5   8   B   5   8
sqlite> select * from A, B where A.a=B.a collate nocase;
select * from A, B where A.a=B.a collate nocase;
a   b   c   a   d   e
--  --  --  --  --  --
a   4   7   A   4   7
b   5   8   B   5   8
sqlite> select * from A join B on A.a=B.a collate nocase;
select * from A join B on A.a=B.a collate nocase;
a   b   c   a   d   e
--  --  --  --  --  --
a   4   7   A   4   7
b   5   8   B   5   8
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Peng Yu
Sent: Wed 7/14/2010 11:36 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:[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] Case insensitive join available?

[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
> solution.
> 
> #!/usr/bin/env bash
> 
> rm -f main.db
> sqlite3 main.db < 
> create table A (a text, b integer, c integer);
> create table B (a text, d integer, e integer);
> insert into A values('a', 4, 7);
> insert into A values('b', 5, 8);
> insert into A values('c', 6, 9);
> insert into B values('A', 4, 7);
> insert into B values('B', 5, 8);
> insert into B values('D', 6, 9);
> 
> .mode column
> .headers on
> .echo on
> select * from A, B where A.a=B.a;
> select * from A inner join B on A.a=B.a;
> select * from A join B on A.a=B.a;

  Replace "A.a=B.a" with "A.a COLLATE NOCASE = B.a"

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[Werner Smit]

Mike said:

>>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 are you writing to?
>>#3 How fast does this run if you write to your local machine?

>>Michael D. Black

>It DOES sound terrible since 90%? of the time is spend in retrieving
>data from a remote oracle server over a slow line. The problem is that
>the time spend saving to sqlite is still more than I used to spend on
>saving to my previous file system.


>Can you answer #3 though?

It's nearly 50% faster.

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


Mike said:

Now I'm confused...how can you be 50% faster if 90% of the time is in
retrieving from Oracle?

Werner with a list of processing speeds to clarify matters.

Date   Strt  End   Minutes recs Version Filename
2010-05-03 15:42 16:49 66.7 F  421710   4.4.0.6 Due
2010-05-24 01:12 01:30 17.9 F  419604   CLARDue
2010-05-27 11:07 13:01113.5 F  419604   4.4.0.7 Due
2010-06-03 18:54 19:42 47.8 F  419604   4.4.0.9 Due
2010-06-04 17:24 18:09 45.5 B  419604   4.4.0.9 Due
2010-06-07 11:08 13:59170.5 U  419604   4.4.0.8 Due
2010-06-17 09:13 09:29 16.2 B  419604   CLARDue
2010-06-28 13:51 14:07 15.9 F  419604   CLARDue
2010-06-28 15:12 15:37 25.4 B  419604   CLARDue
2010-06-29 15:57 16:21 24.1 B  419604   CLARDue
2010-06-30 11:39 12:17 37.8 F  418620   4.4.1.2 Due
2010-07-08 14:38 15:28 49.4 F  418620   4.4.1.2 Due
2010-07-13 13:09 13:52 42.2 U  418620   4.4.1.2 Due
2010-07-13 18:02 18:31 28.6 F  418620   4.4.1.2 Due (local drive)
2010-07-14 13:27 14:15 47.8 B  418620   4.4.1.2 Due
2010-07-14 15:28 16:03 34.6 F  418620   4.4.1.2 Due

I hope above lines stay in correct columns.
As you can see I started with building these records in 66 minutes.
Where previous version "CLAR" takes between 15 and 24 minutes

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)

I went with a finecomb thru the code and could not find any other
showstoppers like "count"
Maybe I should dump to a txt file and see what the speed is like?

ps. The last entry (34 minutes) was done with blocks of 6000 rather than
blocks of 500.




DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Case insensitive join available?

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

[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 application 
there shouldn't be a problem.  Only if you try to access the database with some 
other "unaware" application.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Eric Smith
Sent: Wed 7/14/2010 11:24 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL: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 you'd have to write the name of the journal file
into the main db, in the header or something.  I think sqlite doesn't
do that at the moment, which means you'd have to change the file
format, which sqlite devs are loath to do.

--
Eric A. Smith

Worthless.
-- Sir George Bidell Airy, KCB, MA, LLD, DCL, FRS, FRAS
   (Astronomer Royal of Great Britain), estimating for the
   Chancellor of the Exchequer the potential value of the
   "analytical engine" invented by Charles Babbage, September
   15, 1842.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 slow???
 
What's your ping time latency look like?  
 
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:23 AM
To: General Discussion of SQLite Database
Subject: 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 ssh tunnel between the oracle server and the client,
port-forwarding 1521/tcp over it and changing your tnsnames to
point at the client machine?

Paul.
___

Wow! That sound fascinating!
But since my client is in the banking environment they have strict
procedures about network control.
I will research this option, but target date for implementation would
possible be middle 2020.

I'd however like to test this with a local server - is there a faq
somewhere about compressed ssh tunneling on oracle ports?
Is it open source?
Have you done it yourself?
What was speed improvement?

Werner



DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 you'd have to write the name of the journal file 
into the main db, in the header or something.  I think sqlite doesn't 
do that at the moment, which means you'd have to change the file 
format, which sqlite devs are loath to do.

-- 
Eric A. Smith

Worthless.
-- Sir George Bidell Airy, KCB, MA, LLD, DCL, FRS, FRAS
   (Astronomer Royal of Great Britain), estimating for the
   Chancellor of the Exchequer the potential value of the
   "analytical engine" invented by Charles Babbage, September
   15, 1842.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 ssh tunnel between the oracle server and the client,
port-forwarding 1521/tcp over it and changing your tnsnames to
point at the client machine?

Paul.
___

Wow! That sound fascinating!
But since my client is in the banking environment they have strict
procedures about network control. 
I will research this option, but target date for implementation would
possible be middle 2020.

I'd however like to test this with a local server - is there a faq
somewhere about compressed ssh tunneling on oracle ports?
Is it open source?
Have you done it yourself?
What was speed improvement?

Werner



DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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: General Discussion of SQLite Database
Subject: EXTERNAL: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 kind of server are you writing to?
>>#3 How fast does this run if you write to your local machine?

>>Michael D. Black

>It DOES sound terrible since 90%? of the time is spend in retrieving
>data from a remote oracle server over a slow line. The problem is that
>the time spend saving to sqlite is still more than I used to spend on
>saving to my previous file system.


>Can you answer #3 though?

It's nearly 50% faster.

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



DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 values in the SELECT result."
>
> The part "SELECT result contains no NULLs" does not seem to hold true (and
> for the better!):
>

Improvements to the IN operator documentation can be found here:

 http://www.sqlite.org/draft/lang_expr.html#in_op

Note that in the process of making these documentation improvements, we
discovered a bug in the NULL handling of the IN operator:


http://www.sqlite.org/src/tktview/80e031a00f45dca877ed92b225209cfa09280f4f

We are working to fix this bug prior to the 3.7.0 release.



>
> sqlite> SELECT 'selected' WHERE 1 IN (SELECT null UNION SELECT 1);
> selected
>
> Cheers,
> Igor
> --
> View this message in context:
> http://old.nabble.com/Documentation-error-in-explanation-of-%22IN-subquery%22-%28lang_expr.html%29-tp29161289p29161289.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 server are you writing to?
>>#3 How fast does this run if you write to your local machine?

>>Michael D. Black

>It DOES sound terrible since 90%? of the time is spend in retrieving
>data from a remote oracle server over a slow line. The problem is that
>the time spend saving to sqlite is still more than I used to spend on
>saving to my previous file system.


>Can you answer #3 though?

It's nearly 50% faster.

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


DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 ssh tunnel between the oracle server and the client,
port-forwarding 1521/tcp over it and changing your tnsnames to
point at the client machine?

Paul.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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
 



From: sqlite-users-boun...@sqlite.org on behalf of Werner Smit
Sent: Wed 7/14/2010 10:59 AM
To: General Discussion of SQLite Database
Subject: 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 are you writing to?
>#3 How fast does this run if you write to your local machine?

>Michael D. Black

It DOES sound terrible since 90%? of the time is spend in retrieving
data from a remote oracle server over a slow line. The problem is that
the time spend saving to sqlite is still more than I used to spend on
saving to my previous file system.


DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 server are you writing to?
>#3 How fast does this run if you write to your local machine?
 
>Michael D. Black

It DOES sound terrible since 90%? of the time is spend in retrieving
data from a remote oracle server over a slow line. The problem is that
the time spend saving to sqlite is still more than I used to spend on
saving to my previous file system.


DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using test_intarray

[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 your schema.  You'll probably speed up your entire system 
and save a lot of very complicated programming by merging some tables and/or 
making some separate columns of a table into a collection of keyed properties.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 are you writing to?
#3 How fast does this run if you write to your local machine?
 
 
 
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 10:06 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL: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 can be considerably faster to
DROP the indexes before importing all that >data, then remake them
afterwards.  Of course you may already be doing this.
>
>Simon.


Nope, no index only a primary key..
CREATE TABLE Due
  (Pin  Integer
  ,IDNumber Char(13)
  ,Name VarChar(35)
  ,PayPeriodInteger
  ,PaypointCode VarChar(6)
  ,RegionCode   VarChar(6)
  ,ProxyPin Integer
  ,PRIMARY KEY (Pin)
  );

The table I'm using for test purpose look like above.



DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] using test_intarray

[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 tables in our DB, and
queries with lots of joins, so we quite depend on ANALYZE results and the
optimizer. 

For example, where we now have a query

  SELECT ...lots-of-joins... WHERE ...lots-of-exprs... AND someColumn IN
(?,?,?,? ...40 params... )

we'd use either "AND someColumn IN vtab" or another JOIN with vtab.

I'm currently running some experiments and looking at EXPLAIN outputs, but
the results are inconclusive. 

Any hints from developers would be helpful.

Thanks!
Igor




Richard Hipp-3 wrote:
> 
> Have you looked at the "test_intarray" code.
> 
> http://www.sqlite.org/src/artifact/489edb9068bb926583445cb02589344961054207
> 
> On Sun, Jul 11, 2010 at 9:42 PM, Sam Carleton
> wrote:
> 
>> Is there any way to bind to this query?
>>
>> SELECT * FROM table WHERE tableId IN ( ? );
>>
>> Where ? should be 1,2,3,4
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> 
> 
> 
> -- 
> -
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/binding-an-IN-tp29135222p29163181.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 ?  It can be considerably faster to
DROP the indexes before importing all that >data, then remake them
afterwards.  Of course you may already be doing this.
>
>Simon.


Nope, no index only a primary key..
CREATE TABLE Due
  (Pin  Integer
  ,IDNumber Char(13)
  ,Name VarChar(35)
  ,PayPeriodInteger
  ,PaypointCode VarChar(6)
  ,RegionCode   VarChar(6)
  ,ProxyPin Integer
  ,PRIMARY KEY (Pin)
  );

The table I'm using for test purpose look like above.



DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[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 products (product, rank) values ('foo', 2.2)
insert into products (product, rank) values ('foo2', 2.00)


select typeof(rank) from products

integer
integer
real

So it would appear that if the numeric value to be inserted can be coerced
to INTEGER without loss, it will in fact become an INTEGER, otherwise it
stay what it was, REAL.


TEST:
select  cast(rank as integer), rank from products where cast(rank as
integer) <> rank

1  |2.2


So, if the OP executes the TEST query above, it should discover any value
with something other than  zero(s) to the right of the decimal point.

And you can convert that test into a check constraint to prevent non-integer
values from being inserted into the table:


CREATE  TABLE "main"."PROD" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT
NULL  UNIQUE , "product" TEXT,
 "rank" INTEGER check (cast(rank as INTEGER) =rank) )

Regards
Tim Romano
Swarthmore PA



On Wed, Jul 14, 2010 at 10:19 AM, Bogdan Ureche  wrote:

> >
> >
> > 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 the Delphi grid component that Bogdan uses.
> >
> >
> This is the default behavior in SQLite Expert but can be overridden. If you
> store floating-point values in columns declared as INTEGER, you might want
> to change the default type mappings to INTEGER -> Float (or WideString) and
> you will see the floating-point values correctly in the grid.
>
> Bogdan Ureche
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[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 the Delphi grid component that Bogdan uses.
>
>
This is the default behavior in SQLite Expert but can be overridden. If you
store floating-point values in columns declared as INTEGER, you might want
to change the default type mappings to INTEGER -> Float (or WideString) and
you will see the floating-point values correctly in the grid.

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[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 !
> 
> 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 the Delphi grid component that Bogdan uses.
> 
> I see that as a unevitable drawback of SQLite flexibility about 
> types.  A generic tool like Expert needs to stick to some set of rules 
> about types, which isn't the case for an ad hoc application, where you 
> have full lattitude to manage varied types inside a column and apply 
> complex processing/display rules, possibly far beyond what SQL would allow.
> 
> 
> --
>Jean-Christophe Deschamps
> eMail: j...@q-e-d.org
> SnailsTo:   1308, route du Vicot
>40230 Saint Jean de Marsacq
>  France
> GSM: +33 (0)6 15 10 19 29
> Home:+33 (0)5 58 77 71 79
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
Riccardo Cohen
Architecte du Logiciel
http://www.architectedulogiciel.fr
+33 (0)6.09.83.64.49
Membre du réseau http://www.reflexe-conseil-centre.org


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[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 accepts data that are not exactly in declared 
> format (other usual example is a string that is bigger than the field), 
> but in my case I must admit that it does not help...
>> 

In an earlier message:

>>> I have a simple database:
>>> 
>>> CREATE TABLE data (irisid char(9),postid varchar(20),basket integer);

I think that at the level of detail you've got to it's worth knowing that 
SQLite doesn't even have a VARCHAR type.  Take a look at this:

http://www.sqlite.org/datatype3.html

Everything is turned into one of those five types.  So there's no limit to the 
lengths of TEXT values, and numbers are either REAL or INTEGER.  Your own table 
declaration is seen as defining both irisid and postid as TEXT columns.

If you need to limit the length of values in a text column, you can do it 
manually using a TRIGGER or a CHECK constraint, but generally programmers seem 
to do it in their software, so the value is short before it's presented to the 
database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[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 
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 the Delphi grid component that Bogdan uses.

I see that as a unevitable drawback of SQLite flexibility about 
types.  A generic tool like Expert needs to stick to some set of rules 
about types, which isn't the case for an ad hoc application, where you 
have full lattitude to manage varied types inside a column and apply 
complex processing/display rules, possibly far beyond what SQL would allow.


--
   Jean-Christophe Deschamps
eMail: j...@q-e-d.org
SnailsTo:   1308, route du Vicot
   40230 Saint Jean de Marsacq
 France
GSM: +33 (0)6 15 10 19 29
Home:+33 (0)5 58 77 71 79

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[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 usual example is a string that is bigger than the field), 
but in my case I must admit that it does not help...

Thanks a lot for your quick and efficient help.




Max Vlasov wrote:
> 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 values
> 
> Max
> 
> On Wed, Jul 14, 2010 at 2:43 PM, Riccardo Cohen
> wrote:
> 
>> 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 (postid);
>>
>> created by my c#.net program using sqlite ado component from
>> http://sqlite.phxsoftware.com/ v1.0.65.0 (cannot say which sqlite version)
>>
>> This works perfectly until I need to use sum. I use SQLiteExpertPersonal
>> 3.0.19 to do some selects :
>>
>> NO SUM :
>> 
>> select irisid,basket from data where irisid in ('37203') and postid
>> in ('A_02_001_0001') group by irisid
>>
>> irisid  basket
>> 37203   696
>>
>> DO THE SUM OF THIS ITEM :
>> 
>> select irisid,sum(basket) from data where irisid in ('37203') and
>> postid in ('A_02_001_0001')
>>
>> irisid  sum(basket)
>> 37203   695.81315226
>>
>> same problem if multiple rows, with or without group by, with '='
>> instead of 'in'... same also with min() and max(), there is no null, and
>> no float (all ints) in this record (or any other)
>>
>> I cannot understand why this approximation. I tried to build a new small
>> database in sqlite expert and cannot reproduce the problem.
>> I cannot send you the database it takes 1,5 Gb (15M records).
>> I found nothing when googling for this problem.
>>
>> I would like to know if there is a simple explanation, before trying to
>> rebuild my huge database with some tests.
>> thanks a lot for your help.
>>
>> --
>> Riccardo Cohen
>> Architecte du Logiciel
>> http://www.architectedulogiciel.fr
>> +33 (0)6.09.83.64.49
>> Membre du réseau http://www.reflexe-conseil-centre.org
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
Riccardo Cohen
Architecte du Logiciel
http://www.architectedulogiciel.fr
+33 (0)6.09.83.64.49
Membre du réseau http://www.reflexe-conseil-centre.org


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 can be considerably faster to DROP the 
indexes before importing all that data, then remake them afterwards.  Of course 
you may already be doing this.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[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 pages don't make it to disk, the
> old
> > pages will be present, with the old, and valid checksums.
>
> You are assuming the checksums are stored in the page they checksum.  That
> would only detect corruption of that page.  You could have pages that store
> the checksums of numerous other pages, so both the checksum page and the
> data page would have to fail to make it to disk.  Yes, there are scenarios
> where you could still get old apparently valid pages, but those are harder
> to happen.
>

It seems there are several level of checking possible:

- checksum on the page itself lets you detect some errors, with no extra I/O
- checksum pages for a group of pages lets you detect missing writes within
the group, with some extra I/O
- checksum of all checksum pages lets you detect missing writes for an
entire commit, with even more extra I/O

How much extra I/O depends on the size of the db, page size, and how much
memory is available for caching checksum pages.

Scott mentioned that a detection system without the ability to correct might
not be useful, but I think it is useful.  Not as good as correction of
course, but useful because:

- it might prevent the application program from issuing a bogus error
message like "the row you asked for isn't in the database"; lots of time
could be spent in the weeds chasing down a misleading error

- some applications might have backup copies of the database; they could
display an error message and revert to a backup

Jim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 seem to hold true (and
for the better!):

sqlite> SELECT 'selected' WHERE 1 IN (SELECT null UNION SELECT 1);
selected

Cheers,
Igor
-- 
View this message in context: 
http://old.nabble.com/Documentation-error-in-explanation-of-%22IN-subquery%22-%28lang_expr.html%29-tp29161289p29161289.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 source you can do what you want (ain't that nice?)
 
 
Also, try 
PRAGAM locking_mode=EXCLUSIVE;
That might save you some time too.
 
 
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 6:33 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL: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:

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

I have considered that!
I do allow the users to do a clean build or an update.
Clean build is usually once a week - I could build that on local drive
BUT the problem is if the copy fail and leave them with incomplete file
I'm in trouble.
To manage this and make sure all is well is not an easy task.

I did check and the sqlite speed on local drive seem to be much faster.
Would it be possible to tell sqlite to save journal file to local drive
and merge with server file
on transaction completion?

Another option - with much more work would be to create a db on local
drive,
fill it with X records and start a seperate thread to merge this with
network drive while my program is busy fetching the next X record batch.

But if at all possible I'd like to not make too many changes to the
current program.
I like the kiss scenario. "keep it short and simple"




DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

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

Max

On Wed, Jul 14, 2010 at 2:43 PM, Riccardo Cohen
wrote:

> 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 (postid);
>
> created by my c#.net program using sqlite ado component from
> http://sqlite.phxsoftware.com/ v1.0.65.0 (cannot say which sqlite version)
>
> This works perfectly until I need to use sum. I use SQLiteExpertPersonal
> 3.0.19 to do some selects :
>
> NO SUM :
> 
> select irisid,basket from data where irisid in ('37203') and postid
> in ('A_02_001_0001') group by irisid
>
> irisid  basket
> 37203   696
>
> DO THE SUM OF THIS ITEM :
> 
> select irisid,sum(basket) from data where irisid in ('37203') and
> postid in ('A_02_001_0001')
>
> irisid  sum(basket)
> 37203   695.81315226
>
> same problem if multiple rows, with or without group by, with '='
> instead of 'in'... same also with min() and max(), there is no null, and
> no float (all ints) in this record (or any other)
>
> I cannot understand why this approximation. I tried to build a new small
> database in sqlite expert and cannot reproduce the problem.
> I cannot send you the database it takes 1,5 Gb (15M records).
> I found nothing when googling for this problem.
>
> I would like to know if there is a simple explanation, before trying to
> rebuild my huge database with some tests.
> thanks a lot for your help.
>
> --
> Riccardo Cohen
> Architecte du Logiciel
> http://www.architectedulogiciel.fr
> +33 (0)6.09.83.64.49
> Membre du réseau http://www.reflexe-conseil-centre.org
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

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

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

I have considered that! 
I do allow the users to do a clean build or an update.
Clean build is usually once a week - I could build that on local drive
BUT the problem is if the copy fail and leave them with incomplete file
I'm in trouble.
To manage this and make sure all is well is not an easy task.

I did check and the sqlite speed on local drive seem to be much faster.
Would it be possible to tell sqlite to save journal file to local drive
and merge with server file
on transaction completion?

Another option - with much more work would be to create a db on local
drive,
fill it with X records and start a seperate thread to merge this with
network drive while my program is busy fetching the next X record batch.

But if at all possible I'd like to not make too many changes to the
current program. 
I like the kiss scenario. "keep it short and simple"




DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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() method has been renamed to xShmUnmap().  A new snapshot
> with these changes is now available in the usual place:
>
>
I also see that the order of two methods (Lock and map) was changed. No
problem, just checking is it correct

struct sqlite3_io_methods {

(201007091257)

  int (*xShmOpen)(sqlite3_file*);
  int (*xShmLock)(sqlite3_file*, int offset, int n, int flags);
  int (*xShmMap)(sqlite3_file*, int iPage, int pgsz, int, void volatile**);

(201007140820)

  int (*xShmMap)(sqlite3_file*, int iPg, int pgsz, int, void volatile**);
  int (*xShmLock)(sqlite3_file*, int offset, int n, int flags);

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 20-30% slower.
2. I'm reading data from an remote oracle server
3. I'm saving to a network drive.
4. If my program seems to hang for longer than about 3-5 minutes the
user tends to kill it.


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.




DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] error in sum function

[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 (postid);

created by my c#.net program using sqlite ado component from 
http://sqlite.phxsoftware.com/ v1.0.65.0 (cannot say which sqlite version)

This works perfectly until I need to use sum. I use SQLiteExpertPersonal 
3.0.19 to do some selects :

NO SUM :

select irisid,basket from data where irisid in ('37203') and postid 
in ('A_02_001_0001') group by irisid

irisid  basket
37203   696

DO THE SUM OF THIS ITEM :

select irisid,sum(basket) from data where irisid in ('37203') and 
postid in ('A_02_001_0001')

irisid  sum(basket)
37203   695.81315226

same problem if multiple rows, with or without group by, with '=' 
instead of 'in'... same also with min() and max(), there is no null, and 
no float (all ints) in this record (or any other)

I cannot understand why this approximation. I tried to build a new small 
database in sqlite expert and cannot reproduce the problem.
I cannot send you the database it takes 1,5 Gb (15M records).
I found nothing when googling for this problem.

I would like to know if there is a simple explanation, before trying to 
rebuild my huge database with some tests.
thanks a lot for your help.

-- 
Riccardo Cohen
Architecte du Logiciel
http://www.architectedulogiciel.fr
+33 (0)6.09.83.64.49
Membre du réseau http://www.reflexe-conseil-centre.org


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[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 (about 500 inserts at a time) with a 
> begin/end transaction.
> After these 500 i take a few seconds to read more data so sqlite 
> should have time to do any housekeeping it might need.
>

Wrap more into a transaction. 500 is too small of a percentage of a
million.

John


John,

I was wondering if that's really so.  Wouldn't the marginal speed
improvement be quite small?  Is the percentage of the final rowcount
really a criterion?


I did find that the speed improvement was quite small, and to the user
it appears as if the machine is unresponsive.





DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 changes is now available in the usual place:

 http://www.sqlite.org/draft/download.html

The target release date for version 3.7.0 has slipped until 2010-07-22  
(a slip of one week).  We were going to try to revise WAL so that it  
worked as a read-only database if write permission to key files was  
not available.  But that was going to turn out to be a big, error- 
prone mess, and so we decided to back off and simply make it a  
limitation of WAL that a database could not be read or written in WAL  
mode if write permissions were lacking.  Additional explanation at

 http://www.sqlite.org/draft/wal.html#readonly

The documentation has also been enhanced (in a subsection immediately  
following the section on read-only databases linked above) to explain  
our decision to implement shared-memory using a mmapped file in the  
same directory as the original database.  Comments and criticism of  
this decision are encouraged.

Please evaluate the snapshot and provide feedback on this mailing  
list.  The 2010-07-22 release date target assumes no more major  
problems.  But we would rather encounter (and fix) a major problem  
before the release rather than afterwards.  We will slip the release  
again if necessary.  Your beta testing is *very* important.  Thanks!

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users