Inserting into two tables with minimal overhead

Question

I have a new application that will be inserting a lot of data, and must be able to handle many inserts per second. The database schema consists of two tables, a HEADER and DETAIL table. The HEADER table contains some minimal information, the DETAIL table has the bulk of the data that will not be updated.

The HEADER record has a PK constraint on the ID field. The ID value is created by a sequence generator on insert. The DETAIL.HEADER_ID field has FK constraint on the HEADER.ID field.

My question is which option will perform the fastest inserts? This will be running on Weblogic 11g, Oracle 11g. Looking at upwards of 1000+ inserts/sec.

1) Use JDBC's Statement.RETURN_GENERATED_KEYS option on the HEADER insert, get the generated key, then insert the DETAIL record.

2) First call sequence.nextval, then insert the HEADER record, then insert the DETAIL record.

3) Somehow grab a block of sequence ids, insert all HEADER records, then insert all DETAIL records. (how can I grab a block of sequence ids?).

Answer 1

1)

I suggest to use HILO algorithm to generate all IDs. Use different sequence for each table. With HILO alg use allocation size for about 1000. These means that select nextval will be executed once for each 1000 records.

2)

Use jdbc batch insert with size that is multiple of HILO allocation size. For example 2000, 3000 etc.

3)

Check if your DB can handle so many records in one transaction. If no, you probably should commit tx after each batch insert.

--

I used these pattern to migrate 300 mln records with on-fly java processing from one system to another. In some tables insert speed was above 5000 records / second.

Ad 1) - implementing HILO

public interface IdentifierGenerator {

    int getNextId();
}

HiloGenerator:

public class HiLoSequenceGenerator implements IdentifierGenerator {

    private String sequenceName;
    private int allocationSize;

    private IdentifierDao dao;

    private boolean initialized;
    private int min;
    private int max;
    private int next;

    public HiLoSequenceGenerator() {
    }

    public HiLoSequenceGenerator(String sequenceName, int allocationSize, IdentifierDao dao) {
        this.sequenceName = sequenceName;
        this.allocationSize = allocationSize;
        this.dao = dao;
    }

    @Override
    public int getNextId() {
        return next();
    }

    private synchronized int next() {

        if (!initialized) {
            alloc();
            initialized = true;
        }

        if (next > max) {
            alloc();
        }

        return next++;
    }

    private void alloc() {

        // fetch unique number from sequence
        int unique = dao.getUniqueId(sequenceName);

        // prepare buffer (pool of identifiers)
        min = unique * allocationSize;
        max = min + allocationSize - 1;
        next = min;

        if (log.isTraceEnabled()) {
            log.trace("allocated id buffer based on " + sequenceName + ": [" + min + " - " + max + "]");
        }
    }

Here you can see how HILO works with allocation size 3 and mockito unit test:

public class HiLoSequenceGeneratorTest {

    @Test
    public void test() {

        // given
        IdentifierDao dao = mock(IdentifierDao.class);
        when(dao.getUniqueId(anyString())).thenReturn(3, 4, 10, 11);

        HiLoSequenceGenerator gen = new HiLoSequenceGenerator("seq", 3, dao);

        // 3:  9, 10, 11
        // 4: 12, 13, 14
        //10: 30, 31, 32
        //11: 33, 34, 35

        // when - then
        assertEquals(9, gen.getNextId());        // hit db
        assertEquals(10, gen.getNextId());
        assertEquals(11, gen.getNextId());
        assertEquals(12, gen.getNextId());        // hit db
        assertEquals(13, gen.getNextId());
        assertEquals(14, gen.getNextId());
        assertEquals(30, gen.getNextId());        // hit db
        assertEquals(31, gen.getNextId());
        assertEquals(32, gen.getNextId());
        assertEquals(33, gen.getNextId());        // hit db
        assertEquals(34, gen.getNextId());
        assertEquals(35, gen.getNextId());
    }

}