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: [email protected] on behalf of Igor Sereda
Sent: Thu 5/27/2010 3:27 AM
To: [email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
