Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

Pavel Ivanov schreef:
>> Currently this means adding
>> the new columns to my C-structures, writing access functions, and
>> recompiling. I don't want to do that, because this means my appl *must*
>> be replaced on every database change, and I'd like to be able to
>> run different versions of it in the wild. I was hoping to make it
>> more dynamic and adaptive to database layout changes.
> 
> Sorry, Ron, but I don't get it. You're saying that adding more columns
> means adding more fields into your structure and making your
> application to know about those fields, and that means you need to
> recompile and it's a bad thing. But if you're working with SQLite's
> in-memory database then you have to create all tables in that database
> at startup, so you need an SQL statement in your application
> containing all columns you use, so again adding a column you need to
> recompile, right? Or if you generate your CREATE TABLE statement
> dynamically from postgresql then how does your code know about new
> fields added? How does it work with it? Are your newly added columns
> used only from sql coming directly from clients?
> Maybe you just need to re-think your database schema so that you need
> only one select statement per network request and so your requirements
> to sql engine speed would be lowered by the order of magnitude?
> 

Pavel,

You are right, and I'll look into rewriting a bigger part of our application.

Thanks,
Ron


> Pavel
> 
> On Sun, Oct 18, 2009 at 2:23 PM, Ron Arts  wrote:
>> P Kishor schreef:
>>> On Sun, Oct 18, 2009 at 10:37 AM, Ron Arts  wrote:
 Very true Simon,

 this has been the fastest way so far and I can do around
 35 selects/second this way, using prepared statements
 (on my machine at least), but I need more speed.

 That's why I want to skip the SQL processing entirely
 and write a C function that reaches directly into the
 internal memory structures to gets my record from there.
>>> I might have missed the discussion, but... why don't you ditch SQLite
>>> and use something like Berkeley DB? Sounds to me you need a hash db
>>> instead of an rdbms, especially since you have no need for SQL.
>>>
>> Well, that's what I use at the moment (I use glib2 g_hash_table()),
>> but now the requirement to accept SQL queries for that database
>> has come up. And I don't want to write my own SQL parser..
>>
>> Next I tried to add a virtual table driver to SQLite for my database.
>> That worked, but because the application is evolving, columns
>> get added/changed on a regular basis. Currently this means adding
>> the new columns to my C-structures, writing access functions, and
>> recompiling. I don't want to do that, because this means my appl *must*
>> be replaced on every database change, and I'd like to be able to
>> run different versions of it in the wild. I was hoping to make it
>> more dynamic and adaptive to database layout changes.
>>
>> Thanks,
>> Ron
>>
>>
 thanks,
 Ron

 Simon Slavin schreef:
> On 18 Oct 2009, at 8:37am, Ron Arts wrote:
>
>> Is there a way to bypass the virtual machine altogether and reach
>> directly
>> into the btree and just retrieve one record by it's oid (primary
>> integer key),
>> and return it in a form that would allow taking out the column
>> values by name?
> The primary integer key column can always be referred to as the
> special name ROWID, even if you have assigned it a column name of your
> own.  So you can do
>
> SELECT ROWID,myCol1,myCol2 FROM myTable
>
> as long as you don't explicitly declare a primary integer key column
> and then change the values in it.
>
> 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

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Pavel Ivanov]

> Currently this means adding
> the new columns to my C-structures, writing access functions, and
> recompiling. I don't want to do that, because this means my appl *must*
> be replaced on every database change, and I'd like to be able to
> run different versions of it in the wild. I was hoping to make it
> more dynamic and adaptive to database layout changes.

Sorry, Ron, but I don't get it. You're saying that adding more columns
means adding more fields into your structure and making your
application to know about those fields, and that means you need to
recompile and it's a bad thing. But if you're working with SQLite's
in-memory database then you have to create all tables in that database
at startup, so you need an SQL statement in your application
containing all columns you use, so again adding a column you need to
recompile, right? Or if you generate your CREATE TABLE statement
dynamically from postgresql then how does your code know about new
fields added? How does it work with it? Are your newly added columns
used only from sql coming directly from clients?
Maybe you just need to re-think your database schema so that you need
only one select statement per network request and so your requirements
to sql engine speed would be lowered by the order of magnitude?

Pavel

On Sun, Oct 18, 2009 at 2:23 PM, Ron Arts  wrote:
> P Kishor schreef:
>> On Sun, Oct 18, 2009 at 10:37 AM, Ron Arts  wrote:
>>> Very true Simon,
>>>
>>> this has been the fastest way so far and I can do around
>>> 35 selects/second this way, using prepared statements
>>> (on my machine at least), but I need more speed.
>>>
>>> That's why I want to skip the SQL processing entirely
>>> and write a C function that reaches directly into the
>>> internal memory structures to gets my record from there.
>>
>> I might have missed the discussion, but... why don't you ditch SQLite
>> and use something like Berkeley DB? Sounds to me you need a hash db
>> instead of an rdbms, especially since you have no need for SQL.
>>
>
> Well, that's what I use at the moment (I use glib2 g_hash_table()),
> but now the requirement to accept SQL queries for that database
> has come up. And I don't want to write my own SQL parser..
>
> Next I tried to add a virtual table driver to SQLite for my database.
> That worked, but because the application is evolving, columns
> get added/changed on a regular basis. Currently this means adding
> the new columns to my C-structures, writing access functions, and
> recompiling. I don't want to do that, because this means my appl *must*
> be replaced on every database change, and I'd like to be able to
> run different versions of it in the wild. I was hoping to make it
> more dynamic and adaptive to database layout changes.
>
> Thanks,
> Ron
>
>
>>
>>> thanks,
>>> Ron
>>>
>>> Simon Slavin schreef:
 On 18 Oct 2009, at 8:37am, Ron Arts wrote:

> Is there a way to bypass the virtual machine altogether and reach
> directly
> into the btree and just retrieve one record by it's oid (primary
> integer key),
> and return it in a form that would allow taking out the column
> values by name?
 The primary integer key column can always be referred to as the
 special name ROWID, even if you have assigned it a column name of your
 own.  So you can do

 SELECT ROWID,myCol1,myCol2 FROM myTable

 as long as you don't explicitly declare a primary integer key column
 and then change the values in it.

 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 in-memory database far too slow in my use case

[Olaf Schmidt]

"Ron Arts"  schrieb im
Newsbeitrag news:4adac5c1.5010...@arts-betel.org...

> Then my program opens a socket, and starts accepting connections,
> those connections are long lasting, and send messages that need
> a fast reply. Many of the messages result in messages being send
> to all other clients. The messages require on average 10 lookups
> in the memory db, each by oid.

Is the "socket-listener-thread" already decoupled from the
thread which hosts your sqlite-connection-handle?

If not done already, you should try it (that will not speedup
the sqlite-performance, but the overall-performance of your
"broadcasting-dispatcher-app").
Additionally you should decouple the "sqlite-thread" also from
the "reply-sender-threads" (placing the sqlite-query-results
in some structures, where the sender-threads are able to find
them).

That would ensure, that the sqlite-engine can always run
fullspeed, not waiting for potentially "slow, or blocking
socket-transfers".

In such a design you could also try another thing, which
maybe speeds up your selects - meaning, maybe "oID-
aggregation" can help.

If you receive in your socket-listener-thread  approx.
5 requests per second (and nothing will intermit this
receiver-thread now, since sqlite-queries run elsewhere) ...
then we talk about 50 incoming messages per milisecond.
Now, since the sqlite-thread is running elsewhere already
... why not aggregate the incoming oIDs in a comma-
separated list (in a simple charbuffer, shared with the
sqlite-thread - and flagged with a "next-job-descriptor").

Each 1 msec (to keep the latency low), you should end
gathering oIDs in such a "next-job" charbuffer and set
the finalized-flag in the job-descriptor-structure (after
that you could start gathering oIDs in your listener-thread
on a different charbuf-allocation immediately).

The sqlite-thread should look for new, flagged as "ready to
proceed" charbuffers on its own, and start its work in a more
"aggregated fashion" then - and maybe the engine-overhead
gets a bit reduced, if sqlite now performs *one* (larger)
select (only each 1 msec), but returning more than only
one single record in its step-loop then.
i.e. per:
Select * from Table Where oID In YourGathered_IDList

Just an idea - I've not yet tested here, if the throughput
would be better this way instead of performing single-record-
selects only ... you probably lose the advantage of the
precompiled "single-record-statement", but could gain
over all, as soon as you reach the step-loop, which does
then more than just one record with probably less overhead
overall.

Maybe that worth' a try.

Olaf



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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Simon Slavin]

On 18 Oct 2009, at 7:23pm, Ron Arts wrote:

> because the application is evolving, columns
> get added/changed on a regular basis. Currently this means adding
> the new columns to my C-structures, writing access functions, and
> recompiling. I don't want to do that, because this means my appl  
> *must*
> be replaced on every database change, and I'd like to be able to
> run different versions of it in the wild. I was hoping to make it
> more dynamic and adaptive to database layout changes.

Then what you need is a flexible system that can cope with schema and  
index changes.  And that's SQLite.  Whatever you write yourself, if it  
has to have the same flexibility as SQLIte does, won't be much faster  
than SQLite.

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Kees Nuyt]

On Sun, 18 Oct 2009 17:37:57 +0200, 
Ron Arts  wrote:

>Very true Simon,
>
>this has been the fastest way so far and I can do around
>35 selects/second this way, using prepared statements
>(on my machine at least), but I need more speed.
>
>That's why I want to skip the SQL processing entirely
>and write a C function that reaches directly into the
>internal memory structures to gets my record from there.

You will have to implement BTree code to walk the index
pages. You'll have a hard time to make your code more
efficient than the SQLite BTree code.

Luckily the BTree code and the Virtual machine interpreter
are in the SQLite source repository, which is public domain.
EXPLAIN SELECT colX FROM tableY WHERE tableY.id = your key;
yields the VM instructions.

So, yes, you can do it.

Note: the SQLite BTree code may change without notice,
because it isn't a public API.

In your case I'd either go for a hash table, without SQL, or
solve the speed problem with hardware.

>thanks,
>Ron
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

P Kishor schreef:
> On Sun, Oct 18, 2009 at 10:37 AM, Ron Arts  wrote:
>> Very true Simon,
>>
>> this has been the fastest way so far and I can do around
>> 35 selects/second this way, using prepared statements
>> (on my machine at least), but I need more speed.
>>
>> That's why I want to skip the SQL processing entirely
>> and write a C function that reaches directly into the
>> internal memory structures to gets my record from there.
> 
> I might have missed the discussion, but... why don't you ditch SQLite
> and use something like Berkeley DB? Sounds to me you need a hash db
> instead of an rdbms, especially since you have no need for SQL.
> 

Well, that's what I use at the moment (I use glib2 g_hash_table()),
but now the requirement to accept SQL queries for that database
has come up. And I don't want to write my own SQL parser..

Next I tried to add a virtual table driver to SQLite for my database.
That worked, but because the application is evolving, columns
get added/changed on a regular basis. Currently this means adding
the new columns to my C-structures, writing access functions, and
recompiling. I don't want to do that, because this means my appl *must*
be replaced on every database change, and I'd like to be able to
run different versions of it in the wild. I was hoping to make it
more dynamic and adaptive to database layout changes.

Thanks,
Ron


> 
>> thanks,
>> Ron
>>
>> Simon Slavin schreef:
>>> On 18 Oct 2009, at 8:37am, Ron Arts wrote:
>>>
 Is there a way to bypass the virtual machine altogether and reach
 directly
 into the btree and just retrieve one record by it's oid (primary
 integer key),
 and return it in a form that would allow taking out the column
 values by name?
>>> The primary integer key column can always be referred to as the
>>> special name ROWID, even if you have assigned it a column name of your
>>> own.  So you can do
>>>
>>> SELECT ROWID,myCol1,myCol2 FROM myTable
>>>
>>> as long as you don't explicitly declare a primary integer key column
>>> and then change the values in it.
>>>
>>> Simon.
> 
> 
> 
> 
> 

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[P Kishor]

On Sun, Oct 18, 2009 at 10:37 AM, Ron Arts  wrote:
> Very true Simon,
>
> this has been the fastest way so far and I can do around
> 35 selects/second this way, using prepared statements
> (on my machine at least), but I need more speed.
>
> That's why I want to skip the SQL processing entirely
> and write a C function that reaches directly into the
> internal memory structures to gets my record from there.

I might have missed the discussion, but... why don't you ditch SQLite
and use something like Berkeley DB? Sounds to me you need a hash db
instead of an rdbms, especially since you have no need for SQL.


>
> thanks,
> Ron
>
> Simon Slavin schreef:
>> On 18 Oct 2009, at 8:37am, Ron Arts wrote:
>>
>>> Is there a way to bypass the virtual machine altogether and reach
>>> directly
>>> into the btree and just retrieve one record by it's oid (primary
>>> integer key),
>>> and return it in a form that would allow taking out the column
>>> values by name?
>>
>> The primary integer key column can always be referred to as the
>> special name ROWID, even if you have assigned it a column name of your
>> own.  So you can do
>>
>> SELECT ROWID,myCol1,myCol2 FROM myTable
>>
>> as long as you don't explicitly declare a primary integer key column
>> and then change the values in it.
>>
>> Simon.





-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Simon Slavin]

On 18 Oct 2009, at 4:37pm, Ron Arts wrote:

> I want to skip the SQL processing entirely
> and write a C function that reaches directly into the
> internal memory structures to gets my record from there.

I assume that you've already tested the fastest way of doing this that  
the standard library allows: prebinding using _prepare, etc..

You could download the source code for SQLite, take a look at how the  
SELECT command is implemented, and write your own customised code  
which knows exactly which fields and index you want and how your table  
is structured.  I have no idea what the results would look like or  
whether the developer forum would be a better place to discuss it.   
Start by using EXPLAIN to look at the code generated from your SELECT  
command.




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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

Very true Simon,

this has been the fastest way so far and I can do around
35 selects/second this way, using prepared statements
(on my machine at least), but I need more speed.

That's why I want to skip the SQL processing entirely
and write a C function that reaches directly into the
internal memory structures to gets my record from there.

thanks,
Ron

Simon Slavin schreef:
> On 18 Oct 2009, at 8:37am, Ron Arts wrote:
> 
>> Is there a way to bypass the virtual machine altogether and reach  
>> directly
>> into the btree and just retrieve one record by it's oid (primary  
>> integer key),
>> and return it in a form that would allow taking out the column  
>> values by name?
> 
> The primary integer key column can always be referred to as the  
> special name ROWID, even if you have assigned it a column name of your  
> own.  So you can do
> 
> SELECT ROWID,myCol1,myCol2 FROM myTable
> 
> as long as you don't explicitly declare a primary integer key column  
> and then change the values in it.
> 
> 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 in-memory database far too slow in my use case

[Simon Slavin]

On 18 Oct 2009, at 8:37am, Ron Arts wrote:

> Is there a way to bypass the virtual machine altogether and reach  
> directly
> into the btree and just retrieve one record by it's oid (primary  
> integer key),
> and return it in a form that would allow taking out the column  
> values by name?

The primary integer key column can always be referred to as the  
special name ROWID, even if you have assigned it a column name of your  
own.  So you can do

SELECT ROWID,myCol1,myCol2 FROM myTable

as long as you don't explicitly declare a primary integer key column  
and then change the values in it.

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

Pavel Ivanov schreef:
>> I use the following queries:
>>
>>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
> 
> I'm not sure how SQLite treats this table definition but probably
> because of your ASC it could decide that id shouldn't be a synonym for
> rowid which will make at least inserts slower.
> 
>> But I'm still looking to speed up selects.
> 
> Other conditions that can speed up selects:
> - Depending on relation between size of your database and size of
> database cache selects could work faster if all database is cached. So
> for example in just started application first set of selects will work
> slower than second. But if full database cannot fit into cache then
> different sets of selects will have the same performance on average.
> - If you don't care about changing your database concurrently from
> other processes you can place all your inserts in one transaction or
> in case of your real application just start transaction at the
> beginning and commit/rollback it at the end.
> 
> Tell us if it still doesn't satisfy you.
> 
> 

Pavel,

I formulated a followup question in new thread, and that was silly, so I'll
repeat it here.

Is there a way to bypass the virtual machine altogether and reach directly
into the btree and just retrieve one record by it's oid (primary integer key),
and return it in a form that would allow taking out the column values by name?

I don't care if it's a bit dirty, but I really need to speed up record retrieval
and I know beforehand that 99% of my queries involves just retrieving one 
record by
primary integer key. I also know that the entire database will fit in memory.

I'll tell you the general flow of processing in my program: on startup, it 
copies
a postgresql database into a sqlite memory database so I need to do a lot of 
inserts,
but nod very fast, as postgresql cannot supply the records very fast.
Then my program opens a socket, and starts accepting connections, those 
connections
are long lasting, and send messages that need a fast reply. Many of the 
messages result
in messages being send to all other clients. The messages require on average 10 
lookups
in the memory db, each by oid. Very occasionally I get a message that need more,
and for that I need to use an SQL statement internally. I also receive SQL 
queries
from clients occasionally.

My application needs to scale beyond tens of thousand of clients, and should 
also
communicatie with similar apps running on other machines for high availability
and geographical separation.

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Jay A. Kreibich]

On Mon, Oct 12, 2009 at 07:23:30PM -0400, Pavel Ivanov scratched on the wall:
> > Is their a way to prepare the query and save (compiled form) so that 
> > we can share them between multiple connection?
> 
> Yes, there is: http://sqlite-consortium.com/products/sse.

  I realize this may be a general question, but given that the topic of
  the thread was in-memory databases, I'd just point out that there is
  no such thing as "multiple connections" to an in-memory DB.  Even
  within the same process, you cannot open another database handle to
  an in-memory DB.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Pavel Ivanov]

> Pavel,
>
> does the cache work for memory datsbases too?

Doh, missed the fact that it's a memory database. I believe in-memory
database is in fact just a database cache that never deletes its pages
from memory and never spills them to disk. Although anything about
size of database cache will not be applicable here...
But wrapping all selects into one transaction will work with in-memory
databases too (somewhere in another thread there were even benchmarks
showing the effect of this). And maybe in fact you never even need to
commit or rollback transaction in in-memory database because there's
no other connection which will need to get access to the database and
to the committed data...

BTW, another option for speeding up selects is to recompile SQLite
without multi-threading support and maybe even without some other
stuff that you never use. At least lack of multi-threading support can
make a significant difference in performance.

Pavel

On Sun, Oct 11, 2009 at 12:39 PM, Ron Arts  wrote:
> Pavel Ivanov schreef:
>>> I use the following queries:
>>>
>>>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>>
>> I'm not sure how SQLite treats this table definition but probably
>> because of your ASC it could decide that id shouldn't be a synonym for
>> rowid which will make at least inserts slower.
>>
>>> But I'm still looking to speed up selects.
>>
>> Other conditions that can speed up selects:
>> - Depending on relation between size of your database and size of
>> database cache selects could work faster if all database is cached. So
>> for example in just started application first set of selects will work
>> slower than second. But if full database cannot fit into cache then
>> different sets of selects will have the same performance on average.
>> - If you don't care about changing your database concurrently from
>> other processes you can place all your inserts in one transaction or
>> in case of your real application just start transaction at the
>> beginning and commit/rollback it at the end.
>>
>> Tell us if it still doesn't satisfy you.
>>
>
> Pavel,
>
> does the cache work for memory datsbases too?
>
> Thanks,
> Ron
>
>>
>> Pavel
>>
>> On Sun, Oct 11, 2009 at 4:04 AM, Ron Arts  wrote:
>>> Olaf Schmidt schreef:
 "Ron Arts"  schrieb im
 Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...

> Here's my new benchmark output:
>
> sqlite3 insert 50 records time: 17.19 secs
> sqlite3 select 50 records time: 18.57 secs
> sqlite3 prepared select 50 records time: 3.27 secs
> glib2 hash tables insert 50 records time: 0.38 secs
> glib2 hash tables lookup 50 records time: 0.24 secs
>
> The prepared select indeed speeds up things tremendously,
> a 5-fold increase.
 Now do the same thing (prepared commands) for
 the sqlite3 - inserts too ... wrapped in a transaction.

 Against an InMemory-Table I reach here about
 12 Inserts per second (filling up and inserting
 "mixed values" against a 8-column-table).
 That's on a 1.8GHz  Intel-(mobile)CPU.

 As long as your benchmarked-tabledef (your insert) has not
 much more columns than my above mentioned '8', then
 you should see something like "factor 5" there too.

 With a small two-column-table (a prepared two-column-insert-Cmd)
 I see about 36 inserts per second, somewhat depending
 on the used datatypes (Integers and Doubles work a bit faster of
 course than inserting the same "amount of Columns" as Text-Values).

 Another reason for your bad insert-performance could of
 course be, that you already defined an index on the table
 in question (or in case your ID-Field is defined as
 INTEGER PRIMARY KEY *and* you're filling up
 new IDs in non-consecutive order).
 In that case your current results seem a bit more reasonable.

 If you don't have an index created yet (on your "HashKey-
 ID-Column" ... or if you don't have mapped your ID-Field
 to SQLites RowID yet (per INTEGER PRIMARY KEY)
 then you maybe should try to create one -  if possible, after
 your "main-amount" of fillups was done - that adds some
 additional time to your overall-data-preparation efforts of your
 "table-list" - but will be of benefit for your single-record-lookups,
 based on your "... Where ID = ? ".


>>> Olaf,
>>>
>>> I tried it, and indeed, this speeds up inserts tremendously as well,
>>> but in fact I'm not at all concernced about insert speed, but much more 
>>> about
>>> select speed. I use the following queries:
>>>
>>>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>>>
>>> Then I insert 50 records like this:
>>>
>>>   INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>>>
>>> (with consecutive values for the id value.)
>>>
>>> do you expect the id column now to be mapped to 

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Pavel Ivanov]

> Is their a way to prepare the query and save (compiled form) so that we can 
> share them between multiple connection?

Yes, there is: http://sqlite-consortium.com/products/sse.

Pavel

On Mon, Oct 12, 2009 at 6:28 AM, Naveen Grover -TP <naveen.gro...@igt.in> wrote:
> Is their a way to prepare the query and save (compiled form) so that we can 
> share them between multiple connection?
>
> Regds,
> Naveen
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
> Sent: Sunday, October 11, 2009 1:54 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite in-memory database far too slow in my use case
>
> On Sat, Oct 10, 2009 at 07:24:33PM +0200, Ron Arts scratched on the wall:
>
>> I'm afraid the process of
>> constructing SQL queries / parsing them by sqlite, and
>> interpreting the results in my app, multiple times per
>> event will be too slow.
>
>  There should be no need to construct and parse queries with each
>  interaction.  Assuming the queries are fairly well known, you should
>  be able to prepare them once and then keep using them over and over.
>  This should save a noticeable amount of time.
>
>  Make sure you're using the prepare/bind/step/reset/finalize
>  interfaces, rather than exec or get_table.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> +
> CONFIDENTIALITY NOTICE & DISCLAIMER
>
> The contents of this e-mail are confidential to the ordinary user of the 
> e-mail address to which it was addressed and may also be privileged. If you 
> are not the addressee of this e-mail you may not copy, forward, disclose or 
> otherwise use it or any part of it in any form whatsoever. If you have 
> received this e-mail in error please e-mail the sender by replying to this 
> message. The recipient should check this email and any attachments for the 
> presence of viruses. InterGlobe accepts no liability for any damage caused by 
> any virus transmitted by this email.
> +
> ___
> 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 in-memory database far too slow in my use case

[Naveen Grover -TP]

Is their a way to prepare the query and save (compiled form) so that we can 
share them between multiple connection?

Regds,
Naveen

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: Sunday, October 11, 2009 1:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite in-memory database far too slow in my use case

On Sat, Oct 10, 2009 at 07:24:33PM +0200, Ron Arts scratched on the wall:

> I'm afraid the process of
> constructing SQL queries / parsing them by sqlite, and
> interpreting the results in my app, multiple times per
> event will be too slow.

  There should be no need to construct and parse queries with each
  interaction.  Assuming the queries are fairly well known, you should
  be able to prepare them once and then keep using them over and over.
  This should save a noticeable amount of time.

  Make sure you're using the prepare/bind/step/reset/finalize
  interfaces, rather than exec or get_table.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

+
CONFIDENTIALITY NOTICE & DISCLAIMER

The contents of this e-mail are confidential to the ordinary user of the e-mail 
address to which it was addressed and may also be privileged. If you are not 
the addressee of this e-mail you may not copy, forward, disclose or otherwise 
use it or any part of it in any form whatsoever. If you have received this 
e-mail in error please e-mail the sender by replying to this message. The 
recipient should check this email and any attachments for the presence of 
viruses. InterGlobe accepts no liability for any damage caused by any virus 
transmitted by this email.
+
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Alexey Pechnikov]

Hello!

On Sunday 11 October 2009 22:52:29 Jay A. Kreibich wrote:
>   A bit to my surprise, the difference is even more significant using 
>   prepared statements in a C program.  For a half-million selects over a
>   similar table in a :memory: database, there is a 20% speed-up by
>   wrapping all the selects in a transaction (vs the 10% you're seeing).
>   It's averaging about 4.3 seconds in auto-commit mode, and 3.4 seconds
>   in an explicit transaction.

The tcl programm doing copy of the selected rows. May be your C programm 
doesn't copy the selected data?

The modified tcl test script can show how transaction increase speed of data 
extracting. In this case the test programm perform copy of data too but the 
data doesn't extracting from database table when we use the "select NULL" 
construction. 

The performance increased of ~10% when we extract table data and of 
~3% again.

$ ./test.tcl
insert transaction 50 rows
21233766 microseconds per iteration

select 50 rows
28164019 microseconds per iteration

select without extract 50 rows
26379441 microseconds per iteration

select transaction 50 rows
25749923 microseconds per iteration

select transaction without extract 50 rows
25644248 microseconds per iteration


$ cat ./test.tcl   
#!/usr/bin/tclsh8.5   
package require sqlite3   
sqlite3 db :memory:   
set limit 50  

db eval {create table test(id int primary key, value text)}

puts "insert transaction $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set value "value $i"
db eval {insert into test (value) values ($value)}
}
}
}]

puts "\nselect $limit rows"
puts [time {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where id=$rowid}
}
}]

puts "\nselect without extract $limit rows"
puts [time {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select NULL from test where id=$rowid}
}
}]

puts "\nselect transaction $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where id=$rowid}
}
}
}]

puts "\nselect transaction without extract $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select NULL from test where id=$rowid}
}
}
}]


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Jay A. Kreibich]

On Sun, Oct 11, 2009 at 11:49:57AM +0400, Alexey Pechnikov scratched on the 
wall:
> Hello!
> 
> On Sunday 11 October 2009 00:54:04 Simon Slavin wrote:
> > >  Using transactions speeds up a long series of SELECTs because it
> > >  eliminates the need to re-acquire a read-only file-lock for each
> > >  individual SELECT.
> > >
> > >  Since in-memory databases have no file locks, I'm not sure that is
> > >  relevant to this specific case.
> > 
> > I wasn't sure about that.  It could still be slower
> 
> You can check it very easy.
> 
> In transactions:
> ve...@veter-laptop:/tmp$ ./test.tcl
> 19968119 microseconds per iteration
> 25649514 microseconds per iteration
> 
> Without transactions:
> ve...@veter-laptop:/tmp$ ./test.tcl
> 35586024 microseconds per iteration
> 28630785 microseconds per iteration

  A bit to my surprise, the difference is even more significant using 
  prepared statements in a C program.  For a half-million selects over a
  similar table in a :memory: database, there is a 20% speed-up by
  wrapping all the selects in a transaction (vs the 10% you're seeing).
  It's averaging about 4.3 seconds in auto-commit mode, and 3.4 seconds
  in an explicit transaction.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Ron Arts]

Are there compile time switches which I can use to speed up
selects in memory databases? Will the amalgamated version be faster
than linking the lib at runtime?

Thanks,
Ron

Pavel Ivanov schreef:
>> I use the following queries:
>>
>>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
> 
> I'm not sure how SQLite treats this table definition but probably
> because of your ASC it could decide that id shouldn't be a synonym for
> rowid which will make at least inserts slower.
> 
>> But I'm still looking to speed up selects.
> 
> Other conditions that can speed up selects:
> - Depending on relation between size of your database and size of
> database cache selects could work faster if all database is cached. So
> for example in just started application first set of selects will work
> slower than second. But if full database cannot fit into cache then
> different sets of selects will have the same performance on average.
> - If you don't care about changing your database concurrently from
> other processes you can place all your inserts in one transaction or
> in case of your real application just start transaction at the
> beginning and commit/rollback it at the end.
> 
> Tell us if it still doesn't satisfy you.
> 
> 
> Pavel
> 
> On Sun, Oct 11, 2009 at 4:04 AM, Ron Arts  wrote:
>> Olaf Schmidt schreef:
>>> "Ron Arts"  schrieb im
>>> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
>>>
 Here's my new benchmark output:

 sqlite3 insert 50 records time: 17.19 secs
 sqlite3 select 50 records time: 18.57 secs
 sqlite3 prepared select 50 records time: 3.27 secs
 glib2 hash tables insert 50 records time: 0.38 secs
 glib2 hash tables lookup 50 records time: 0.24 secs

 The prepared select indeed speeds up things tremendously,
 a 5-fold increase.
>>> Now do the same thing (prepared commands) for
>>> the sqlite3 - inserts too ... wrapped in a transaction.
>>>
>>> Against an InMemory-Table I reach here about
>>> 12 Inserts per second (filling up and inserting
>>> "mixed values" against a 8-column-table).
>>> That's on a 1.8GHz  Intel-(mobile)CPU.
>>>
>>> As long as your benchmarked-tabledef (your insert) has not
>>> much more columns than my above mentioned '8', then
>>> you should see something like "factor 5" there too.
>>>
>>> With a small two-column-table (a prepared two-column-insert-Cmd)
>>> I see about 36 inserts per second, somewhat depending
>>> on the used datatypes (Integers and Doubles work a bit faster of
>>> course than inserting the same "amount of Columns" as Text-Values).
>>>
>>> Another reason for your bad insert-performance could of
>>> course be, that you already defined an index on the table
>>> in question (or in case your ID-Field is defined as
>>> INTEGER PRIMARY KEY *and* you're filling up
>>> new IDs in non-consecutive order).
>>> In that case your current results seem a bit more reasonable.
>>>
>>> If you don't have an index created yet (on your "HashKey-
>>> ID-Column" ... or if you don't have mapped your ID-Field
>>> to SQLites RowID yet (per INTEGER PRIMARY KEY)
>>> then you maybe should try to create one -  if possible, after
>>> your "main-amount" of fillups was done - that adds some
>>> additional time to your overall-data-preparation efforts of your
>>> "table-list" - but will be of benefit for your single-record-lookups,
>>> based on your "... Where ID = ? ".
>>>
>>>
>> Olaf,
>>
>> I tried it, and indeed, this speeds up inserts tremendously as well,
>> but in fact I'm not at all concernced about insert speed, but much more about
>> select speed. I use the following queries:
>>
>>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>>
>> Then I insert 50 records like this:
>>
>>   INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>>
>> (with consecutive values for the id value.)
>>
>> do you expect the id column now to be mapped to the Row ID, so
>> this results in the fastest possible way of selecting by id?
>>
>> I now get this:
>>
>> sqlite3 prepared insert in trx 50 records time: 5.08 secs
>> sqlite3 select 50 records time: 19.28 secs
>> sqlite3 prepared select 50 records time: 3.47 secs
>> glib2 hash tables insert 50 records time: 0.37 secs
>> glib2 hash tables lookup 50 records time: 0.25 secs
>>
>> But I'm still looking to speed up selects.
>>
>> Thanks,
>> Ron
>>
>>> Olaf Schmidt
>>>
>>>
>>>
>>> ___
>>> 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Ron Arts]

Pavel Ivanov schreef:
>> I use the following queries:
>>
>>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
> 
> I'm not sure how SQLite treats this table definition but probably
> because of your ASC it could decide that id shouldn't be a synonym for
> rowid which will make at least inserts slower.
> 
>> But I'm still looking to speed up selects.
> 
> Other conditions that can speed up selects:
> - Depending on relation between size of your database and size of
> database cache selects could work faster if all database is cached. So
> for example in just started application first set of selects will work
> slower than second. But if full database cannot fit into cache then
> different sets of selects will have the same performance on average.
> - If you don't care about changing your database concurrently from
> other processes you can place all your inserts in one transaction or
> in case of your real application just start transaction at the
> beginning and commit/rollback it at the end.
> 
> Tell us if it still doesn't satisfy you.
> 

Pavel,

does the cache work for memory datsbases too?

Thanks,
Ron

> 
> Pavel
> 
> On Sun, Oct 11, 2009 at 4:04 AM, Ron Arts  wrote:
>> Olaf Schmidt schreef:
>>> "Ron Arts"  schrieb im
>>> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
>>>
 Here's my new benchmark output:

 sqlite3 insert 50 records time: 17.19 secs
 sqlite3 select 50 records time: 18.57 secs
 sqlite3 prepared select 50 records time: 3.27 secs
 glib2 hash tables insert 50 records time: 0.38 secs
 glib2 hash tables lookup 50 records time: 0.24 secs

 The prepared select indeed speeds up things tremendously,
 a 5-fold increase.
>>> Now do the same thing (prepared commands) for
>>> the sqlite3 - inserts too ... wrapped in a transaction.
>>>
>>> Against an InMemory-Table I reach here about
>>> 12 Inserts per second (filling up and inserting
>>> "mixed values" against a 8-column-table).
>>> That's on a 1.8GHz  Intel-(mobile)CPU.
>>>
>>> As long as your benchmarked-tabledef (your insert) has not
>>> much more columns than my above mentioned '8', then
>>> you should see something like "factor 5" there too.
>>>
>>> With a small two-column-table (a prepared two-column-insert-Cmd)
>>> I see about 36 inserts per second, somewhat depending
>>> on the used datatypes (Integers and Doubles work a bit faster of
>>> course than inserting the same "amount of Columns" as Text-Values).
>>>
>>> Another reason for your bad insert-performance could of
>>> course be, that you already defined an index on the table
>>> in question (or in case your ID-Field is defined as
>>> INTEGER PRIMARY KEY *and* you're filling up
>>> new IDs in non-consecutive order).
>>> In that case your current results seem a bit more reasonable.
>>>
>>> If you don't have an index created yet (on your "HashKey-
>>> ID-Column" ... or if you don't have mapped your ID-Field
>>> to SQLites RowID yet (per INTEGER PRIMARY KEY)
>>> then you maybe should try to create one -  if possible, after
>>> your "main-amount" of fillups was done - that adds some
>>> additional time to your overall-data-preparation efforts of your
>>> "table-list" - but will be of benefit for your single-record-lookups,
>>> based on your "... Where ID = ? ".
>>>
>>>
>> Olaf,
>>
>> I tried it, and indeed, this speeds up inserts tremendously as well,
>> but in fact I'm not at all concernced about insert speed, but much more about
>> select speed. I use the following queries:
>>
>>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>>
>> Then I insert 50 records like this:
>>
>>   INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>>
>> (with consecutive values for the id value.)
>>
>> do you expect the id column now to be mapped to the Row ID, so
>> this results in the fastest possible way of selecting by id?
>>
>> I now get this:
>>
>> sqlite3 prepared insert in trx 50 records time: 5.08 secs
>> sqlite3 select 50 records time: 19.28 secs
>> sqlite3 prepared select 50 records time: 3.47 secs
>> glib2 hash tables insert 50 records time: 0.37 secs
>> glib2 hash tables lookup 50 records time: 0.25 secs
>>
>> But I'm still looking to speed up selects.
>>
>> Thanks,
>> Ron
>>
>>> Olaf Schmidt
>>>
>>>
>>>
>>> ___
>>> 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-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

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Pavel Ivanov]

> I use the following queries:
>
>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)

I'm not sure how SQLite treats this table definition but probably
because of your ASC it could decide that id shouldn't be a synonym for
rowid which will make at least inserts slower.

> But I'm still looking to speed up selects.

Other conditions that can speed up selects:
- Depending on relation between size of your database and size of
database cache selects could work faster if all database is cached. So
for example in just started application first set of selects will work
slower than second. But if full database cannot fit into cache then
different sets of selects will have the same performance on average.
- If you don't care about changing your database concurrently from
other processes you can place all your inserts in one transaction or
in case of your real application just start transaction at the
beginning and commit/rollback it at the end.

Tell us if it still doesn't satisfy you.


Pavel

On Sun, Oct 11, 2009 at 4:04 AM, Ron Arts  wrote:
> Olaf Schmidt schreef:
>> "Ron Arts"  schrieb im
>> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
>>
>>> Here's my new benchmark output:
>>>
>>> sqlite3 insert 50 records time: 17.19 secs
>>> sqlite3 select 50 records time: 18.57 secs
>>> sqlite3 prepared select 50 records time: 3.27 secs
>>> glib2 hash tables insert 50 records time: 0.38 secs
>>> glib2 hash tables lookup 50 records time: 0.24 secs
>>>
>>> The prepared select indeed speeds up things tremendously,
>>> a 5-fold increase.
>>
>> Now do the same thing (prepared commands) for
>> the sqlite3 - inserts too ... wrapped in a transaction.
>>
>> Against an InMemory-Table I reach here about
>> 12 Inserts per second (filling up and inserting
>> "mixed values" against a 8-column-table).
>> That's on a 1.8GHz  Intel-(mobile)CPU.
>>
>> As long as your benchmarked-tabledef (your insert) has not
>> much more columns than my above mentioned '8', then
>> you should see something like "factor 5" there too.
>>
>> With a small two-column-table (a prepared two-column-insert-Cmd)
>> I see about 36 inserts per second, somewhat depending
>> on the used datatypes (Integers and Doubles work a bit faster of
>> course than inserting the same "amount of Columns" as Text-Values).
>>
>> Another reason for your bad insert-performance could of
>> course be, that you already defined an index on the table
>> in question (or in case your ID-Field is defined as
>> INTEGER PRIMARY KEY *and* you're filling up
>> new IDs in non-consecutive order).
>> In that case your current results seem a bit more reasonable.
>>
>> If you don't have an index created yet (on your "HashKey-
>> ID-Column" ... or if you don't have mapped your ID-Field
>> to SQLites RowID yet (per INTEGER PRIMARY KEY)
>> then you maybe should try to create one -  if possible, after
>> your "main-amount" of fillups was done - that adds some
>> additional time to your overall-data-preparation efforts of your
>> "table-list" - but will be of benefit for your single-record-lookups,
>> based on your "... Where ID = ? ".
>>
>>
>
> Olaf,
>
> I tried it, and indeed, this speeds up inserts tremendously as well,
> but in fact I'm not at all concernced about insert speed, but much more about
> select speed. I use the following queries:
>
>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>
> Then I insert 50 records like this:
>
>   INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>
> (with consecutive values for the id value.)
>
> do you expect the id column now to be mapped to the Row ID, so
> this results in the fastest possible way of selecting by id?
>
> I now get this:
>
> sqlite3 prepared insert in trx 50 records time: 5.08 secs
> sqlite3 select 50 records time: 19.28 secs
> sqlite3 prepared select 50 records time: 3.47 secs
> glib2 hash tables insert 50 records time: 0.37 secs
> glib2 hash tables lookup 50 records time: 0.25 secs
>
> But I'm still looking to speed up selects.
>
> Thanks,
> Ron
>
>> Olaf Schmidt
>>
>>
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Olaf Schmidt]

"Ron Arts"  schrieb im
Newsbeitrag news:4ad19195.2060...@arts-betel.org...

> I tried it, and indeed, this speeds up inserts tremendously as well,
> but in fact I'm not at all concernced about insert speed, but much more
about
> select speed. I use the following queries:
>
>CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)

Not sure, if an explicit "text-storage-class-hint" for your name
column will help to speed things up a bit more, but try:
CREATE TABLE company(id INTEGER PRIMARY KEY, name TEXT)

> Then I insert 50 records like this:
>
>INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')

You should prepare the statement like this:
INSERT INTO company (id, name) VALUES (?, ?)

And then treat the (now RowID-mapped) Integer ID as an
Integer, not as a String.
Just use the correctly "typed" binding-calls.

And then wrap the Insert-Loop within a transaction.

> But I'm still looking to speed up selects.
With regards to Selects (searching for random "single IDs"), you
probably already reached the maximum (in case you've not done
any mistakes with the Type-Binding).

Olaf



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

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Simon Slavin]

On 11 Oct 2009, at 9:04am, Ron Arts wrote:

>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>
> Then I insert 50 records like this:
>
>   INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>
> (with consecutive values for the id value.)

I think you can remove the single quotes from around your value for  
the id column, because both the column definition and the values  
you're supplying for it are integers rather than strings.  This might  
speed up your INSERT even more.

> more about
> select speed

When testing SELECT speeds to work out how best to use the library,  
include code to take the values that are returned and put them into  
some variables in whatever language you're using.  In some languages,  
using some compilers, and with some optimisations turned on, the  
SELECT command itself executes quickly but extracting the values to  
variables takes longer.

Also, note that if you don't use the values from the variables some  
compilers (e.g. recent versions of gcc) will optimise out the routines  
which are meant to set the values.  So if you're running comparative  
speed tests and getting weird results try using the variables, for  
example printing them to /dev/null.

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

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Ron Arts]

Alexey Pechnikov schreef:
> Hello!
> 
> On Sunday 11 October 2009 12:04:37 Ron Arts wrote:
>>CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>>
>> Then I insert 50 records like this:
>>
>>INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>>
>> (with consecutive values for the id value.)
>>
>> do you expect the id column now to be mapped to the Row ID, so
>> this results in the fastest possible way of selecting by id?
> 
> Yes, the id is alias for rowid in your table. Check it by 
> sqlite3 > explain query plan select name from company where id=1;
>  
>> I now get this:
>>
>> sqlite3 prepared insert in trx 50 records time: 5.08 secs
>> sqlite3 select 50 records time: 19.28 secs
>> sqlite3 prepared select 50 records time: 3.47 secs
>> glib2 hash tables insert 50 records time: 0.37 secs
>> glib2 hash tables lookup 50 records time: 0.25 secs
>>
>> But I'm still looking to speed up selects.
> 
> Hm... I think you may not open more than 8 000 tcp/ip sockets per second
> in common case and so SQLite speed is good enough. Why you write about
> "to handle tens of thousands requests per second"?
> 

Well, in my case there can be tens of thousands of connections open
at the same time, where each connection can last days.
Each connection can spit out multiple messages per second, and each
message need around 5 SQL queries.

Ron


> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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 in-memory database far too slow in my use case (new benchmark inside)

[Alexey Pechnikov]

Hello!

On Sunday 11 October 2009 12:04:37 Ron Arts wrote:
>CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
> 
> Then I insert 50 records like this:
> 
>INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
> 
> (with consecutive values for the id value.)
> 
> do you expect the id column now to be mapped to the Row ID, so
> this results in the fastest possible way of selecting by id?

Yes, the id is alias for rowid in your table. Check it by 
sqlite3 > explain query plan select name from company where id=1;
 
> I now get this:
> 
> sqlite3 prepared insert in trx 50 records time: 5.08 secs
> sqlite3 select 50 records time: 19.28 secs
> sqlite3 prepared select 50 records time: 3.47 secs
> glib2 hash tables insert 50 records time: 0.37 secs
> glib2 hash tables lookup 50 records time: 0.25 secs
> 
> But I'm still looking to speed up selects.

Hm... I think you may not open more than 8 000 tcp/ip sockets per second
in common case and so SQLite speed is good enough. Why you write about
"to handle tens of thousands requests per second"?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Ron Arts]

Olaf Schmidt schreef:
> "Ron Arts"  schrieb im
> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
> 
>> Here's my new benchmark output:
>>
>> sqlite3 insert 50 records time: 17.19 secs
>> sqlite3 select 50 records time: 18.57 secs
>> sqlite3 prepared select 50 records time: 3.27 secs
>> glib2 hash tables insert 50 records time: 0.38 secs
>> glib2 hash tables lookup 50 records time: 0.24 secs
>>
>> The prepared select indeed speeds up things tremendously,
>> a 5-fold increase.
> 
> Now do the same thing (prepared commands) for
> the sqlite3 - inserts too ... wrapped in a transaction.
> 
> Against an InMemory-Table I reach here about
> 12 Inserts per second (filling up and inserting
> "mixed values" against a 8-column-table).
> That's on a 1.8GHz  Intel-(mobile)CPU.
> 
> As long as your benchmarked-tabledef (your insert) has not
> much more columns than my above mentioned '8', then
> you should see something like "factor 5" there too.
> 
> With a small two-column-table (a prepared two-column-insert-Cmd)
> I see about 36 inserts per second, somewhat depending
> on the used datatypes (Integers and Doubles work a bit faster of
> course than inserting the same "amount of Columns" as Text-Values).
> 
> Another reason for your bad insert-performance could of
> course be, that you already defined an index on the table
> in question (or in case your ID-Field is defined as
> INTEGER PRIMARY KEY *and* you're filling up
> new IDs in non-consecutive order).
> In that case your current results seem a bit more reasonable.
> 
> If you don't have an index created yet (on your "HashKey-
> ID-Column" ... or if you don't have mapped your ID-Field
> to SQLites RowID yet (per INTEGER PRIMARY KEY)
> then you maybe should try to create one -  if possible, after
> your "main-amount" of fillups was done - that adds some
> additional time to your overall-data-preparation efforts of your
> "table-list" - but will be of benefit for your single-record-lookups,
> based on your "... Where ID = ? ".
> 
> 

Olaf,

I tried it, and indeed, this speeds up inserts tremendously as well,
but in fact I'm not at all concernced about insert speed, but much more about
select speed. I use the following queries:

   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)

Then I insert 50 records like this:

   INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')

(with consecutive values for the id value.)

do you expect the id column now to be mapped to the Row ID, so
this results in the fastest possible way of selecting by id?

I now get this:

sqlite3 prepared insert in trx 50 records time: 5.08 secs
sqlite3 select 50 records time: 19.28 secs
sqlite3 prepared select 50 records time: 3.47 secs
glib2 hash tables insert 50 records time: 0.37 secs
glib2 hash tables lookup 50 records time: 0.25 secs

But I'm still looking to speed up selects.

Thanks,
Ron

> Olaf Schmidt
> 
> 
> 
> ___
> 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 in-memory database far too slow in my use case

[Alexey Pechnikov]

Hello!

On Sunday 11 October 2009 00:54:04 Simon Slavin wrote:
> >  Using transactions speeds up a long series of SELECTs because it
> >  eliminates the need to re-acquire a read-only file-lock for each
> >  individual SELECT.
> >
> >  Since in-memory databases have no file locks, I'm not sure that is
> >  relevant to this specific case.
> 
> I wasn't sure about that.  It could still be slower

You can check it very easy.

In transactions:
ve...@veter-laptop:/tmp$ ./test.tcl
19968119 microseconds per iteration
25649514 microseconds per iteration

Without transactions:
ve...@veter-laptop:/tmp$ ./test.tcl
35586024 microseconds per iteration
28630785 microseconds per iteration


$ cat ./test.tcl
#!/usr/bin/tclsh8.5

package require sqlite3
sqlite3 db :memory:

set limit 50
db eval {create table test(id int primary key, value text)}

puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set value "value $i"
db eval {insert into test (value) values ($value)}
}
}
}]

puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where rowid=$rowid}
}
}
}]


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Olaf Schmidt]

"Ron Arts"  schrieb im
Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...

> Here's my new benchmark output:
>
> sqlite3 insert 50 records time: 17.19 secs
> sqlite3 select 50 records time: 18.57 secs
> sqlite3 prepared select 50 records time: 3.27 secs
> glib2 hash tables insert 50 records time: 0.38 secs
> glib2 hash tables lookup 50 records time: 0.24 secs
>
> The prepared select indeed speeds up things tremendously,
> a 5-fold increase.

Now do the same thing (prepared commands) for
the sqlite3 - inserts too ... wrapped in a transaction.

Against an InMemory-Table I reach here about
12 Inserts per second (filling up and inserting
"mixed values" against a 8-column-table).
That's on a 1.8GHz  Intel-(mobile)CPU.

As long as your benchmarked-tabledef (your insert) has not
much more columns than my above mentioned '8', then
you should see something like "factor 5" there too.

With a small two-column-table (a prepared two-column-insert-Cmd)
I see about 36 inserts per second, somewhat depending
on the used datatypes (Integers and Doubles work a bit faster of
course than inserting the same "amount of Columns" as Text-Values).

Another reason for your bad insert-performance could of
course be, that you already defined an index on the table
in question (or in case your ID-Field is defined as
INTEGER PRIMARY KEY *and* you're filling up
new IDs in non-consecutive order).
In that case your current results seem a bit more reasonable.

If you don't have an index created yet (on your "HashKey-
ID-Column" ... or if you don't have mapped your ID-Field
to SQLites RowID yet (per INTEGER PRIMARY KEY)
then you maybe should try to create one -  if possible, after
your "main-amount" of fillups was done - that adds some
additional time to your overall-data-preparation efforts of your
"table-list" - but will be of benefit for your single-record-lookups,
based on your "... Where ID = ? ".


Olaf Schmidt



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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Simon Slavin]

On 10 Oct 2009, at 10:57pm, Ron Arts wrote:

> The sqlite3_bind_int immediately gives me an RANGE_ERROR (25).
> Is there some obvious thing I'm doing wrong?

I notice that your _prepare call puts single quotes around the  
variable, whereas you are binding an integer to it.  But that's  
probably not what's causing your problem.

> rc = sqlite3_bind_int(stmt, 1, rand()%count);

rand()%count ?

Just for debugging purposes, split this into two: assign rand()%count  
to an integer variable in one line, then use it in the next line.  Try  
spitting out the variable value to a debugger or console.

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Jay A. Kreibich]

On Sat, Oct 10, 2009 at 11:57:30PM +0200, Ron Arts scratched on the wall:

> I'm expanding my benchmark to test just thaty, but I'm running into a problem.
> Here's my code (well part of it):
> 
>sqlite3_stmt *stmt;
>rc = sqlite3_prepare(db, "select name from company where id = '?'", -1, 
> , NULL);


> The sqlite3_bind_int immediately gives me an RANGE_ERROR (25).
> Is there some obvious thing I'm doing wrong?

  Remove the single quotes.  They aren't required for parameters, even
  if you're binding a text value.

  As written, that statement has a single character string literal and
  no parameters.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

Jay A. Kreibich schreef:
> On Sat, Oct 10, 2009 at 07:24:33PM +0200, Ron Arts scratched on the wall:
> 
>> I'm afraid the process of
>> constructing SQL queries / parsing them by sqlite, and
>> interpreting the results in my app, multiple times per
>> event will be too slow.
> 
>   There should be no need to construct and parse queries with each
>   interaction.  Assuming the queries are fairly well known, you should
>   be able to prepare them once and then keep using them over and over.
>   This should save a noticeable amount of time.
> 
>   Make sure you're using the prepare/bind/step/reset/finalize
>   interfaces, rather than exec or get_table.
> 
>-j
> 

Thanks Jay,

I'm expanding my benchmark to test just thaty, but I'm running into a problem.
Here's my code (well part of it):

   sqlite3_stmt *stmt;
   rc = sqlite3_prepare(db, "select name from company where id = '?'", -1, 
, NULL);
   if (rc != SQLITE_OK) {
 fprintf(stderr, "sqlite3_prepare SQL error: %d\n", rc);
 exit(1);
   }

   for (i=1; i < count; i++) {
 rc = sqlite3_bind_int(stmt, 1, rand()%count);
 if (rc != SQLITE_OK ){
   fprintf(stderr, "sqlite3_bind_int SQL error: %d\n", rc);
   exit(1);
 }
 while (1) {
   rc = sqlite3_step(stmt);
   if (rc == SQLITE_DONE) {
 sqlite3_reset(stmt);
 break;
   }
   if( rc != SQLITE_ROW){
 fprintf(stderr, "sqlite3_step SQL error: %d\n", rc);
 exit(1);
   }
 }
   }
   sqlite3_finalize(stmt);

The sqlite3_bind_int immediately gives me an RANGE_ERROR (25).
Is there some obvious thing I'm doing wrong?

Thanks,
Ron

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Simon Slavin]

On 10 Oct 2009, at 9:27pm, Jay A. Kreibich wrote:

> On Sat, Oct 10, 2009 at 07:38:08PM +0100, Simon Slavin scratched on  
> the wall:
>>
>
>> Don't forget to use transactions, even for when you are just doing
>> SELECTs without changing any data.
>
>  Using transactions speeds up a long series of SELECTs because it
>  eliminates the need to re-acquire a read-only file-lock for each
>  individual SELECT.
>
>  Since in-memory databases have no file locks, I'm not sure that is
>  relevant to this specific case.

I wasn't sure about that.  It could still be slower, even if the  
individual transactions don't eventually result in any calls to file- 
handling routines.  At what point does the code decide whether a lock  
is needed ?  Does it get dropped almost immediately, or does it go  
through multiple levels of calls before some low-level code says "iff  
disk-database then lock" ?  Sorry, I've never looked at the source.

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Jay A. Kreibich]

On Sat, Oct 10, 2009 at 07:38:08PM +0100, Simon Slavin scratched on the wall:
> 
> On 10 Oct 2009, at 7:04pm, Roger Binns wrote:
> 
> > Ron Arts wrote:
> >> So I am wondering if I can drop the glib Hash Tables, and
> >> go sqlite all the way. But I'm afraid the process of
> >> constructing SQL queries / parsing them by sqlite, and
> >> interpreting the results in my app, multiple times per
> >> event will be too slow.
> 
> Don't forget to use transactions, even for when you are just doing  
> SELECTs without changing any data.

  Using transactions speeds up a long series of SELECTs because it
  eliminates the need to re-acquire a read-only file-lock for each
  individual SELECT.

  Since in-memory databases have no file locks, I'm not sure that is
  relevant to this specific case.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Ron Arts wrote:
> Using hash tables I can do 10 requests in .24 seconds
> meaning around 40 req/sec.

If you are just doing simple lookups (eg doing equality on a single column)
then a hash table will always beat going through SQLite.  But if you are
doing more complex queries SQLite will beat the pants off any custom
expression evaluator you have unless you spend a lot of time optimising it.

> Virtual tables are a partial solution and I tried that, but
> as I said, hash tables are pretty static, and I can't recompile
> (and worse: restart) the application every time some programmer
> adds a database column.

I am confused by this.  Are columns added while running or is this a
development time issue?  Why do virtual tables require a recompile or
restart?  (Hint: you can drop and recreate a table.)

> So I assume there is no direct access method for the im-memory
> table structures?

It uses btrees and there is no stable API to them nor is it exposed in any
way.  See http://www.sqlite.org/arch.html

> Or did you mean that when you said using virtual tables?
> Can I add a hashed index on an existing sqlite memory table this way?

The point of virtual tables is to let you provide a SQL API to your
underlying data without exposing in any way how that data is structured or
optimised.  In the architecture diagram above your code provides the blue
"backend" box.

If all you have at the end of the day is a single simple flat list then this
won't provide much benefit.  If you have multiple lists and need to evaluate
expressions over them then virtual tables are great.  If you have an
underlying index then the virtual table BestIndex method works with them, or
you don't return anything and SQLite will iterate over all relevant rows.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrQ7gcACgkQmOOfHg372QSwJACfeOFn8zID4H6veWVxQVXBbxTd
gwYAn1tCdv2tA21Lc5A6oOh7HUhEya5f
=mklT
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Jay A. Kreibich]

On Sat, Oct 10, 2009 at 07:24:33PM +0200, Ron Arts scratched on the wall:

> I'm afraid the process of
> constructing SQL queries / parsing them by sqlite, and
> interpreting the results in my app, multiple times per
> event will be too slow.

  There should be no need to construct and parse queries with each
  interaction.  Assuming the queries are fairly well known, you should
  be able to prepare them once and then keep using them over and over.
  This should save a noticeable amount of time.

  Make sure you're using the prepare/bind/step/reset/finalize
  interfaces, rather than exec or get_table.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

Ok,

I just finished writing a test program. It creates an SQLite memory table
and inserts 50 records, then it selects 50 times on a random key.

After that it uses hash memory tables to do the same thing. Here is the
test output:

sqlite3 insert 50 records time: 17.21 secs
sqlite3 select 50 records time: 18.59 secs

glib2 hash tables insert 50 records time: 0.68 secs
glib2 hash tables lookup 50 records time: 0.24 secs

Considering the fact that I need to do on average 5 lookups
per network request I need 18.59 seconds for 10 requests
i.e. 5379 requests/sec.

Using hash tables I can do 10 requests in .24 seconds
meaning around 40 req/sec.

This is a problem in my case. By the way, the database will
always fit in memory, so swapping is no problem.

Virtual tables are a partial solution and I tried that, but
as I said, hash tables are pretty static, and I can't recompile
(and worse: restart) the application every time some programmer
adds a database column.

So I assume there is no direct access method for the im-memory
table structures? Or a way I can add my own hashing? Or did you
mean that when you said using virtual tables?

Can I add a hashed index on an existing sqlite memory table this way?

Thanks,
Ron

PS: I *am* impressed by sqlite's speed.

Simon Slavin schreef:
> On 10 Oct 2009, at 7:04pm, Roger Binns wrote:
> 
>> Ron Arts wrote:
>>> So I am wondering if I can drop the glib Hash Tables, and
>>> go sqlite all the way. But I'm afraid the process of
>>> constructing SQL queries / parsing them by sqlite, and
>>> interpreting the results in my app, multiple times per
>>> event will be too slow.
> 
> Don't forget to use transactions, even for when you are just doing  
> SELECTs without changing any data.
> 
>> That is pure guesswork.  You will only know by building a  
>> representative
>> benchmark using data substantially similar to what you expect on  
>> machines
>> similar to what you expect in real world usage of the code.  (ie  
>> anyone
>> else's benchmark data isn't too useful to you)
>>
>> Secondly have a look at virtual tables.  They will let you store  
>> data in any
>> way you deem fit while still being able to offer a SQL interface to  
>> them.
> 
> Also, don't assume that holding your table in memory is faster than  
> just using a normal disk file and letting your operating system do  
> caching and swapping as appropriate.  If you have enough data in your  
> entire database (data /including/ indexes) to force paging/swapping  
> then this may actually be faster.
> 
> As Roger wrote, we can't guess much more without having an idea of the  
> size of your database and what kind of SELECT commands you're doing.
> 
> 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 in-memory database far too slow in my use case

[Simon Slavin]

On 10 Oct 2009, at 7:04pm, Roger Binns wrote:

> Ron Arts wrote:
>> So I am wondering if I can drop the glib Hash Tables, and
>> go sqlite all the way. But I'm afraid the process of
>> constructing SQL queries / parsing them by sqlite, and
>> interpreting the results in my app, multiple times per
>> event will be too slow.

Don't forget to use transactions, even for when you are just doing  
SELECTs without changing any data.

> That is pure guesswork.  You will only know by building a  
> representative
> benchmark using data substantially similar to what you expect on  
> machines
> similar to what you expect in real world usage of the code.  (ie  
> anyone
> else's benchmark data isn't too useful to you)
>
> Secondly have a look at virtual tables.  They will let you store  
> data in any
> way you deem fit while still being able to offer a SQL interface to  
> them.

Also, don't assume that holding your table in memory is faster than  
just using a normal disk file and letting your operating system do  
caching and swapping as appropriate.  If you have enough data in your  
entire database (data /including/ indexes) to force paging/swapping  
then this may actually be faster.

As Roger wrote, we can't guess much more without having an idea of the  
size of your database and what kind of SELECT commands you're doing.

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Ron Arts wrote:
> So I am wondering if I can drop the glib Hash Tables, and
> go sqlite all the way. But I'm afraid the process of
> constructing SQL queries / parsing them by sqlite, and
> interpreting the results in my app, multiple times per
> event will be too slow.

That is pure guesswork.  You will only know by building a representative
benchmark using data substantially similar to what you expect on machines
similar to what you expect in real world usage of the code.  (ie anyone
else's benchmark data isn't too useful to you)

Secondly have a look at virtual tables.  They will let you store data in any
way you deem fit while still being able to offer a SQL interface to them.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrQzJEACgkQmOOfHg372QRBJQCfX+k+YiLSyWVZqkOpvT0827vY
N+UAoMLPFXAez4Ncd+0ZjJVEa0KpZW/w
=Cjpl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

Hi,

I am building a libevent based application that must be
able to handle tens of thousands requests per second.

Each request needs multiple database lookups. Almost
all requests do the lookups on the primary key of the tables
only. So far I have been using Hash Tables from the glib2
library. But now I need SQL access to the same data and I don't
want to write my own SQL engine, so I'm thinking of adding
sqlite to the mix (in memory mode only).

I've added a virtual table driver and it does seem to work
but it's not an ideal solution, because C structures are pretty
inflexible (can't add columns without recompiling), and
most of the in-memory data is loaded from an external SQL
database, which routinely changes the database layout.

So I am wondering if I can drop the glib Hash Tables, and
go sqlite all the way. But I'm afraid the process of
constructing SQL queries / parsing them by sqlite, and
interpreting the results in my app, multiple times per
event will be too slow.

So now my question:

- can I implement/add a hash index in sqlite?
- can i access the table directly without the overhead of
   constructing an SQL query/decomposing the result?

Thanks for any hints and tips,
Ron
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users