So only do N many records in one batch. That's the easiest thing. Forget about the cache and just use responsiveness to adjust how many records you allow at once. Pseudo-code: recnum=0 BEGIN; while more records INSERT.... recnum++ if (recnum % 1000) COMMIT; BEGIN; end COMMIT; Cache will grow to a maximum size and not use any more memory after that. Doesn't matter what you do I don't think other than to adjust the maximum size of it. Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Igor Sereda Sent: Thu 5/27/2010 3:27 AM To: sqlite-users@sqlite.org Subject: [sqlite] Using cache stats to balance transaction size for optimal performance I would like each transaction to be as large as possible, but not too large to cause cache growth or cache spill. We have a stream of incoming data, with each piece of data causing updates in SQLite database. The number of rows inserted/updated for each data record may vary. If I enclose each data record's processing in a separate transaction, there will be too many transactions -- it would be slow. If I enclose too many records processing in a single transaction, the cache may grow or spill to disk -- not wanted either. It would be great if we could dynamically assess how much of the cache is taken up by the transaction. Then, I would issue COMMIT as soon as cache use is over some threshold, like 50%. Pseudocode: while (have data) { BEGIN while (have data && CACHE USE < 50%) { process next piece of data } COMMIT } Is this possible? Any other best practices for optimizing transaction size? Thanks! Igor -- View this message in context: http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28690967.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
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users