Wednesday, 9 December 2009

PLSQL commit every X records and Coherence putAll every X records

Note for self:

Was using this in Write-Behind Caching Demo with Oracle Coherence using PLSQL Bulk Binds.

When using PLSQL to bulk insert code as follows to control the commit time.

-- control number of records to insert before calling COMMIT
NUM_OF_RECORDS_TO_INSERT constant number := 10000;

IF (mod(l_count, NUM_OF_RECORDS_TO_INSERT) = 0) THEN
FORALL i IN mid.FIRST..mid.LAST
insert into messages (message_id, message_type, message)
values (mid(i), mt(i), m(i));
END IF;

When loading many cache records ensure we only call putAll to control the amount of records to insert into the cache in one hit.
final private static int BATCH_SIZE = 1000;

Map buffer = new HashMap();

long start = System.currentTimeMillis();

for (int i = 1; i <= records; i++)
{
Message message = new Message
(new BigDecimal(i),
"M",
String.format("Message %s from test client", i));
buffer.put(String.valueOf(i), message);

if ((i % BATCH_SIZE) == 0)
{
messageCache.putAll(buffer);
buffer.clear();
}
}

if (!buffer.isEmpty())
{
messageCache.putAll(buffer);
buffer.clear();
}

No comments: