If your goal is to handle 1 million inserts/updates per second, and a hard transaction to disk takes 1/60th of a second, you need at least 16666 insert/updates per transaction.
Do your testing with a transaction size of 20,000 and see what kind of performance you get. I'd probably set it higher, like to 100,000 transactions if you have a continuous stream of data coming in at this rate. You get no benefit from one insert/update per commit, unless you are acking the transaction back to the source and it has retry/resend capabilities. In that case, you need a battery backed-up disk controller, and still will probably benefit from a large transaction size. I sort of doubt you have retry/resend abilities though, because for a data stream coming in at 192 MBit/sec (3 doubles = 192 bits), any kind of latency for error recovery might put you in a situation where you could never catch back up. Also, if you use "insert or replace", you can avoid the select, speeding up your app.r Jim On 3/6/09, Nuzzi <nu...@smokemytool.com> wrote: > > > > ken-33 wrote: >> >> >> >> look at the sql syntax for insert or replace for sqlite. >> >> Also you goal to handle 1 million per minute is probably going to be >> dependant upon your hardware. >> >> For instance throughput greatly increases with disk striping. >> >> Also the faster the RPM of the drive the more transactions can be >> processed. Code it up and find out! >> >> > > I have actually coded it up. The way I am currently doing it is sending the > data to a function (the data is 3 doubles) and in that function doing a > SELECT to get the data currently in the DB, then updating the data, then > UPDATE or INSERT. The SQL calls are compiled statements with binds, etc. > It is woefully slow. I was kind of hoping that maybe I was missing a step > or just unfamiliar with the best techniques. I know that when adding a lot > of data with the BEGIN and END TRANSACTION things speed up considerably, but > I can't use it in my case (I don't believe) because the UPDATE depends upon > the data in the SELECT. > > John > > -- > View this message in context: > http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22380539.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
