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

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

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

> 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

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

"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

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

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 >

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

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

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

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

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

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

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

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

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

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

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

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?

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

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

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

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

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

> 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

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

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

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

, 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

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

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

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

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 >

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

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

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

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

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

> 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

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

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

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

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

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

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

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

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

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

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

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

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

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

"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

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

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

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

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,

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

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

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

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

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

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

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

-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

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

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

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

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

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

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

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

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

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.