Loading...

h2-database@googlegroups.com

[Prev] Thread [Next]  |  [Prev] Date [Next]

Re: Slow inserts after table has approx 1 billion records Noel Grandin Wed Apr 04 06:08:54 2012

I've created a test-case for this, but I'm not very motivated to chase it down, since it'll take 9 hours for a single run.
Sorry :-(

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to [EMAIL PROTECTED]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

/*
 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License, Version
 * 1.0, and under the Eclipse Public License, Version 1.0
 * (http://h2database.com/html/license.html). Initial Developer: H2 Group
 */
package org.h2.test.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.test.TestBase;

/**
 * Tests for limits of number of rows in table.
 */
public class TestExtremelyLargeTable extends TestBase {

    /**
     * Run just this test.
     * 
     * @param a ignored
     */
    public static void main(String... a) throws Exception {
        TestBase.createCaller().init().test();
    }

    public void test() throws SQLException {
        if (config.memory) {
            return;
        }
        testLargeSubquery();
        deleteDb("extremelyLargeTable");
    }

    private void testLargeSubquery() throws SQLException {
        // increase the page size and cache, disable logging to speed this test 
up
        String url = 
getURL("extremelyLargeTable;LOG=0;CACHE_SIZE=65536;LOCK_MODE=0;UNDO_LOG=0", 
true);
        
        // insert more than 2<<32 rows worth of data to make sure
        // we catch any 32-bit problems.
        deleteDb("extremelyLargeTable");
        Connection conn = getConnection(url, getUser(), getPassword());
        final Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE LARGE(b BOOLEAN)"); // smallest possible row 
size
        final int BATCH_SIZE = 1024 * 1024;

        // first, insert 2<<32 rows of data, this part takes 9 hours on a 
decent SSD drive
        long time1 = System.currentTimeMillis();
        for (long i = 0; i < (1 << 12); i++) {
            stat.execute("INSERT INTO LARGE SELECT 0 FROM SYSTEM_RANGE(1, " + 
BATCH_SIZE + ")");
            long time2 = System.currentTimeMillis();
            System.out.println("i: " + i + " time=" + (time2-time1)/1000);
            time1 = time2;
        }
        // then, insert some more
        for (long i = 0; i < 10; i++) {
            ResultSet rs = stat.executeQuery("SELECT COUNT(*) FROM LARGE");
            rs.next();
            long count = rs.getLong(1);
            System.out.println("count: " + count);
            stat.execute("INSERT INTO LARGE SELECT X FROM SYSTEM_RANGE(1, " + 
BATCH_SIZE + ")");
            long time2 = System.currentTimeMillis();
            System.out.println("i: " + i + " time=" + (time2-time1)/1000);
            time1 = time2;
        }
        conn.close();
    }
}