Ok, I just finished writing a test program. It creates an SQLite memory table and inserts 500000 records, then it selects 500000 times on a random key.
After that it uses hash memory tables to do the same thing. Here is the test output: sqlite3 insert 500000 records time: 17.21 secs sqlite3 select 500000 records time: 18.59 secs glib2 hash tables insert 500000 records time: 0.68 secs glib2 hash tables lookup 500000 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 100000 requests i.e. 5379 requests/sec. Using hash tables I can do 100000 requests in .24 seconds meaning around 400000 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
