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
