Re: [sqlite] LevelDB benchmark

2011-07-27 Thread Stephan Wehner 
On Wed, Jul 27, 2011 at 7:00 PM, Simon Slavin  wrote:
>
> On 28 Jul 2011, at 2:53am, Stephan Wehner wrote:
>
>> On Wed, Jul 27, 2011 at 6:44 PM, Simon Slavin  wrote:
>>>
>>> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
>>>
 There are some benchmark's at
 http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html

 I don't have anything to point to, but I thought sqlite3 does better
 than stated there.
>>>
>>> i looked through their source code, trying to see if they defined 
>>> transactions. But I couldn't even find an INSERT command.
>>
>> Well, LevelDB is much simpler than sqlite3: it's a key-value store.
>
> Okay, but if they include their source code for testing SQLite I should be 
> able to find the word 'INSERT' somewhere in it, right ?
>

Sorry, I misunderstood -- S

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



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

2011-07-27 Thread Martin Gadbois 
On Wed, Jul 27, 2011 at 9:22 PM, Stephan Wehner wrote:

> There are some benchmark's at
> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>
> I don't have anything to point to, but I thought sqlite3 does better
> than stated there.
>
> In particular, 26,900 sequential writes per second and 420 random writes
> per second from section "1. Baseline Performance" look suspicious.
>
> What you say?
>
>
I wish they compared  BerkerlyDB too 

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

Re: [sqlite] LevelDB benchmark

2011-07-27 Thread Simon Slavin 

On 28 Jul 2011, at 3:01am, David Garfield wrote:

> They used REPLACE.  See
> http://code.google.com/p/leveldb/source/browse/trunk/doc/bench/db_bench_sqlite3.cc#492
> 
> They used explicit transactions, and tested with both single REPLACE
> transactions and 1000 REPLACE transactions.  Section 1A would be the
> single REPLACE transactions, while 2B is the batches.

Ah.  Thanks.  That explains it.

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

Re: [sqlite] LevelDB benchmark

2011-07-27 Thread David Garfield 
They used REPLACE.  See
http://code.google.com/p/leveldb/source/browse/trunk/doc/bench/db_bench_sqlite3.cc#492

They used explicit transactions, and tested with both single REPLACE
transactions and 1000 REPLACE transactions.  Section 1A would be the
single REPLACE transactions, while 2B is the batches.

--David Garfield

Simon Slavin writes:
> 
> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
> 
> > There are some benchmark's at 
> > http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
> > 
> > I don't have anything to point to, but I thought sqlite3 does better 
> > than stated there.
> 
> i looked through their source code, trying to see if they defined 
> transactions.  But I couldn't even find an INSERT command.
> 
> Simon.
> ___
> 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] LevelDB benchmark

2011-07-27 Thread Simon Slavin 

On 28 Jul 2011, at 2:53am, Stephan Wehner wrote:

> On Wed, Jul 27, 2011 at 6:44 PM, Simon Slavin  wrote:
>> 
>> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
>> 
>>> There are some benchmark's at
>>> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>>> 
>>> I don't have anything to point to, but I thought sqlite3 does better
>>> than stated there.
>> 
>> i looked through their source code, trying to see if they defined 
>> transactions. But I couldn't even find an INSERT command.
> 
> Well, LevelDB is much simpler than sqlite3: it's a key-value store.

Okay, but if they include their source code for testing SQLite I should be able 
to find the word 'INSERT' somewhere in it, right ?

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

Re: [sqlite] LevelDB benchmark

2011-07-27 Thread Stephan Wehner 
On Wed, Jul 27, 2011 at 6:44 PM, Simon Slavin  wrote:
>
> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
>
>> There are some benchmark's at
>> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>>
>> I don't have anything to point to, but I thought sqlite3 does better
>> than stated there.
>
> i looked through their source code, trying to see if they defined 
> transactions.  But I couldn't even find an INSERT command.
>

Well, LevelDB is much simpler than sqlite3: it's a key-value store.

Stephan


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



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LevelDB benchmark

2011-07-27 Thread J Decker 
On Wed, Jul 27, 2011 at 6:22 PM, Stephan Wehner  wrote:
> There are some benchmark's at
> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
>
> I don't have anything to point to, but I thought sqlite3 does better
> than stated there.
>
> In particular, 26,900 sequential writes per second and 420 random writes
> per second from section "1. Baseline Performance" look suspicious.
>

Wow, that's a bad mark for sqlite; I dunno it's somewhat misleading,
because I do know that if I use sqlite as a logging database, and
stream data to it it's kinda slow, and works better if I bunch up
inserts with multiple value sets.  But, enabling transactions, and
doing the same thing, write speed goes way up.  And now with WAL
journal, it might affect that speed test also in auto transact mode
especially

> What you say?
>
> Stephan
> ___
> 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] LevelDB benchmark

2011-07-27 Thread Simon Slavin 

On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:

> There are some benchmark's at 
> http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
> 
> I don't have anything to point to, but I thought sqlite3 does better 
> than stated there.

i looked through their source code, trying to see if they defined transactions. 
 But I couldn't even find an INSERT command.

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

[sqlite] LevelDB benchmark

2011-07-27 Thread Stephan Wehner 
There are some benchmark's at 
http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html

I don't have anything to point to, but I thought sqlite3 does better 
than stated there.

In particular, 26,900 sequential writes per second and 420 random writes 
per second from section "1. Baseline Performance" look suspicious.

What you say?

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

Re: [sqlite] SQLITE_OMIT_UTF16

2011-07-27 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/27/2011 04:16 AM, Baruch Burstein wrote:
> The thing I don't understand is how can this be, because when I search
> sqlite3.c for "SQLITE_OMIT_UTF16", I am told it doesn't exist in this file!

You need a better search tool.  Against the 3.7.7.1 amalgamation I get 46
matches.

$ grep -nw SQLITE_OMIT_UTF16 sqlite3.c
12376:#ifdef SQLITE_OMIT_UTF16
20158:#ifndef SQLITE_OMIT_UTF16
20337:#endif /* SQLITE_OMIT_UTF16 */
20392:#ifndef SQLITE_OMIT_UTF16
20520:#endif /* SQLITE_OMIT_UTF16 */
56433:#ifdef SQLITE_OMIT_UTF16
57140:#ifndef SQLITE_OMIT_UTF16
60959:#ifndef SQLITE_OMIT_UTF16
60969:#endif /* SQLITE_OMIT_UTF16 */
61012:#ifndef SQLITE_OMIT_UTF16
61040:#ifndef SQLITE_OMIT_UTF16
61068:#endif /* SQLITE_OMIT_UTF16 */
61605:#ifndef SQLITE_OMIT_UTF16
61611:#endif /* SQLITE_OMIT_UTF16 */
61679:#ifndef SQLITE_OMIT_UTF16
61704:#ifndef SQLITE_OMIT_UTF16
61709:#endif /* SQLITE_OMIT_UTF16 */
61722:#ifndef SQLITE_OMIT_UTF16
61727:#endif /* SQLITE_OMIT_UTF16 */
61738:#ifndef SQLITE_OMIT_UTF16
61743:#endif /* SQLITE_OMIT_UTF16 */
61754:#ifndef SQLITE_OMIT_UTF16
61759:#endif /* SQLITE_OMIT_UTF16 */
61906:#ifndef SQLITE_OMIT_UTF16
61916:#endif /* SQLITE_OMIT_UTF16 */
62221:#ifndef SQLITE_OMIT_UTF16
63670:#ifndef SQLITE_OMIT_UTF16
81123:#ifndef SQLITE_OMIT_UTF16
87470:#ifdef SQLITE_OMIT_UTF16
89302:#ifndef SQLITE_OMIT_UTF16
89373:#endif /* SQLITE_OMIT_UTF16 */
90385:#ifndef SQLITE_OMIT_UTF16
90464:#endif /* SQLITE_OMIT_UTF16 */
100666:#ifndef SQLITE_OMIT_UTF16
107801:#ifndef SQLITE_OMIT_UTF16
107827:#endif /* SQLITE_OMIT_UTF16 */
108962:#ifndef SQLITE_OMIT_UTF16
109082:#ifndef SQLITE_OMIT_UTF16
109461:#ifndef SQLITE_OMIT_UTF16
109506:#endif /* SQLITE_OMIT_UTF16 */
110222:#ifndef SQLITE_OMIT_UTF16
110258:#endif /* SQLITE_OMIT_UTF16 */
110299:#ifndef SQLITE_OMIT_UTF16
110323:#endif /* SQLITE_OMIT_UTF16 */
110342:#ifndef SQLITE_OMIT_UTF16
110359:#endif /* SQLITE_OMIT_UTF16 */

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4wS2AACgkQmOOfHg372QRm6wCfVVYJVn8VcmOCHWaquKP9oaNI
6yQAoILQx+T1b0mooqY41oxvBSY+9S7W
=k9rY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange result using JOIN

2011-07-27 Thread Sintoni Stefano (GMAIL) 
Many thanks to all those who have given me great tips.

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

[sqlite] sqlite3_soft_heap_limit affecting results? (debugging help request)

2011-07-27 Thread Jan Hudec 
Hello,

At $work we have an Android application that uses sqlite3 (compiled from
amalgamation version 3.7.6.3 with a little tweak to get fdatasync() defined).
In the application we set soft heap limit. Now today we noticed that
something fails when the heap limit is 2MiB, but works fine when we increased
it to 20MiB. And the more curious thing is that in Windows version (built
from same sources) gives correct answers even though the limit is only 1MiB
there.

I will try to debug it tomorrow (and with latest version, too). I just
wanted to ask in advance whether there are some debug traces that I could
enable to see what's going on inside sqlite.

Thanks,
Jan

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

Re: [sqlite] SELECT query first run is VERY slow

2011-07-27 Thread Jay A. Kreibich 
On Tue, Jul 26, 2011 at 05:13:00PM +, Black, Michael (IS) scratched on the 
wall:
> Part of the problem is it seems you can't create an index with rowid:
> 
> 
> 
> 3.7.5
> 
> sqlite> create table t(i int);
> sqlite> create index idx1 on t(i);
> sqlite> create index idx2 on t(i,rowid);
> Error: table t has no column named rowid
> 
> Any particular reason it can't be included in an index?

  Because it is always included as the last column.

   -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] c-api

2011-07-27 Thread John Deal 
Hello Baruch,

You may want to look at sqlite3_exec() (http://www.sqlite.org/c3ref/exec.html).

John

--- On Wed, 7/27/11, Baruch Burstein  wrote:

> From: Baruch Burstein 
> Subject: [sqlite] c-api
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, July 27, 2011, 9:22 AM
> Is there an easier way to get a
> single value (for instance "select
> last_insert_rowid();" ) then prepare -> step ->
> column -> finalize?
> ___
> 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] c-api

2011-07-27 Thread Doug Currie 

On Jul 27, 2011, at 9:22 AM, Baruch Burstein wrote:

> Is there an easier way to get a single value (for instance "select
> last_insert_rowid();" ) then prepare -> step -> column -> finalize?

http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid

e


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

Re: [sqlite] c-api

2011-07-27 Thread Igor Tandetnik 
Baruch Burstein  wrote:
> Is there an easier way to get a single value (for instance "select
> last_insert_rowid();" ) then prepare -> step -> column -> finalize?

For this specific example, there's sqlite3_last_insert_rowid API function.

In general, no, there's no special way to handle SELECT statements that return 
a singleton (one row/one column) resultset. You can always write your own 
helper function, of course.
-- 
Igor Tandetnik

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

[sqlite] c-api

2011-07-27 Thread Baruch Burstein 
Is there an easier way to get a single value (for instance "select
last_insert_rowid();" ) then prepare -> step -> column -> finalize?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_OMIT_UTF16

2011-07-27 Thread Baruch Burstein 
It is a typo in the mail. originally it is a '.'


On Wed, Jul 27, 2011 at 2:53 PM, Simon Slavin  wrote:

>
> On 27 Jul 2011, at 12:16pm, Baruch Burstein wrote:
>
> > when I use
> >
> >> gcc -Os -c -DSQLITE_OMIT_UTF16 sqlite3,c
>
> Is that a comma or stop in the original ?
>
> Simon.
> ___
> 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_OMIT_UTF16

2011-07-27 Thread Simon Slavin 

On 27 Jul 2011, at 12:16pm, Baruch Burstein wrote:

> when I use
> 
>> gcc -Os -c -DSQLITE_OMIT_UTF16 sqlite3,c

Is that a comma or stop in the original ?

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

[sqlite] SQLITE_OMIT_UTF16

2011-07-27 Thread Baruch Burstein 
When I compile the amalgamation with

> gcc -Os -c sqlite3.c

I get a 415 KB sqlite3.o. But when I use

> gcc -Os -c -DSQLITE_OMIT_UTF16 sqlite3,c

I get a 409 KB file.

The thing I don't understand is how can this be, because when I search
sqlite3.c for "SQLITE_OMIT_UTF16", I am told it doesn't exist in this file!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] trying to build from sqlite-3.6.7 on ubuntu 11.04, missing sqlite3.c

2011-07-27 Thread Robert P. J. Day 
On Tue, 26 Jul 2011, Pavel Ivanov wrote:

> >  as part of an embedded system build, i'm building a number of
> > tools for the *host* system, including sqlite-3.6.7 from the
> > tarball (along with a few patches which i will be examining
> > shortly).
>
> Apparently you are building from canonical sources. Why don't you
> use amalgamation? It's much easier to build.

  sadly, i have no control over the current structure, it's part of a
much larger development system i have no authority to start mucking
with. :-(

> If you still have to use canonical sources then maybe you don't have
> TCL installed? It's needed to build sqlite3.c.

  i definitely have tcl installed.  i'll do a little more research
and, if i come up with more detailed info to ask about, i'll post
again later today.

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

[sqlite] Large DB files fragmentation

2011-07-27 Thread LiranR 

Hello!
 I want to build a big DB file (about 16 GB).
 now, The DB will be updated all the time, row by row.
 I know that SQLIte will automatic define the value of the cell by it's
value size.
 I think that this can cause that when the cell is updated from 8 bytes to 4
bytes (or vice versa ) , it will cause fragmentation.
 Is it true?
 And is it a better approach to build the DB file in the beginning in such a
way that every cell will contain the largest value that i use, and later on,
when maybe updated to a smaller cell, it won't make fragmentation? 
-- 
View this message in context: 
http://old.nabble.com/Large-DB-files-fragmentation-tp32146059p32146059.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