testcachesize.java

hsqldb是100％java实现的数据库,是一个开放源代码的JAVA数据库 l 具有标准的SQL语法和JAVA接口 l HSQLDB可以自由使用和分发 l 非常简洁和快速的

        int       i;
        PreparedStatement ps =
            cConnection.prepareStatement("INSERT INTO zip VALUES(?)");

        for (i = 0; i <= smallrows; i++) {
            ps.setInt(1, i);
            ps.execute();
        }

        ps.close();
        sStatement.execute("SET REFERENTIAL_INTEGRITY " + this.refIntegrity);

        ps = cConnection.prepareStatement(
            "INSERT INTO test (firstname,lastname,zip,filler) VALUES (?,?,?,?)");

        ps.setString(1, "Julia");
        ps.setString(2, "Clancy");

        for (i = 0; i < bigrows; i++) {
            ps.setInt(3, nextIntRandom(randomgen, smallrows));

            {

                // small rows
                long nextrandom   = randomgen.nextLong();
                int  randomlength = (int) nextrandom & 0x7f;

                if (randomlength > filler.length()) {
                    randomlength = filler.length();
                }

                String varfiller = filler.substring(0, randomlength);

                ps.setString(4, nextrandom + varfiller);
            }

/*
            {
                // big rows
                long nextrandom   = randomgen.nextLong();
                int  randomlength = (int) nextrandom & 0x7ff;

                if (randomlength > filler.length() * 20) {
                    randomlength = filler.length() * 20;
                }

                StringBuffer sb = new StringBuffer(0xff);

                for (int j = 0; j < 20; j++) {
                    sb.append(filler);
                }

                String varfiller = sb.substring(0, randomlength);

                ps.setString(4, nextrandom + varfiller);
            }
*/
            ps.execute();

            if (reportProgress && (i + 1) % 10000 == 0) {
                System.out.println("insert " + (i + 1) + " : "
                                   + sw.elapsedTime());
            }

            // delete and add 4000 rows to introduce fragmentation
            if (deleteWhileInsert && i != 0
                    && i % deleteWhileInsertInterval == 0) {
                sStatement.execute("CALL IDENTITY();");

                ResultSet rs = sStatement.getResultSet();

                rs.next();

                int lastId = rs.getInt(1);

                sStatement.execute(
                    "SELECT * INTO TEMP tempt FROM test WHERE id > "
                    + (lastId - 4000));
                sStatement.execute("DELETE FROM test WHERE id > "
                                   + (lastId - 4000));
                sStatement.execute("INSERT INTO test SELECT * FROM tempt");
                sStatement.execute("DROP TABLE tempt");
            }
        }

        ps.close();

//            sStatement.execute("INSERT INTO test SELECT * FROM temptest;");
//            sStatement.execute("DROP TABLE temptest;");
//            sStatement.execute(ddl7);
        long time = sw.elapsedTime();
        long rate = ((long) i * 1000) / (time + 1);

        storeResult("insert", i, time, rate);
        System.out.println("insert time for " + i + " rows -- " + time
                           + " ms -- " + rate + " tps");
    }

    private void fillUpMultiTable(String filler,
                                  Random randomgen) throws SQLException {

        StopWatch sw = new StopWatch();
        int       i;
        PreparedStatement ps = cConnection.prepareStatement(
            "INSERT INTO test2 (id1, id2, firstname,lastname,zip,filler) VALUES (?,?,?,?,?,?)");

        ps.setString(3, "Julia");
        ps.setString(4, "Clancy");

        int id1 = 0;

        for (i = 0; i < bigrows; i++) {
            int id2 = nextIntRandom(randomgen, Integer.MAX_VALUE);

            if (i % 1000 == 0) {
                id1 = nextIntRandom(randomgen, Integer.MAX_VALUE);
            }

            ps.setInt(1, id1);
            ps.setInt(2, id2);
            ps.setInt(5, nextIntRandom(randomgen, smallrows));

            long nextrandom   = randomgen.nextLong();
            int  randomlength = (int) nextrandom & 0x7f;

            if (randomlength > filler.length()) {
                randomlength = filler.length();
            }

            String varfiller = filler.substring(0, randomlength);

            ps.setString(6, nextrandom + varfiller);

            try {
                ps.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            if (reportProgress && (i + 1) % 10000 == 0) {
                System.out.println("insert " + (i + 1) + " : "
                                   + sw.elapsedTime());
            }
        }

        ps.close();
        System.out.println("total multi key rows inserted: " + i);
        System.out.println("insert time: " + sw.elapsedTime() + " rps: "
                           + (i * 1000 / (sw.elapsedTime() + 1)));
    }

    protected void tearDown() {

        try {
            writer.write("\n</table>\n");
            writer.close();
        } catch (Exception e) {}
    }

    protected void checkResults() {

        try {
            StopWatch sw = new StopWatch();
            ResultSet rs;

            cConnection = DriverManager.getConnection(url + filepath, user,
                    password);

            long time = sw.elapsedTime();

            storeResult("reopen", 0, time, 0);
            System.out.println("database reopen time -- " + time + " ms");
            sw.zero();

            sStatement = cConnection.createStatement();

//            sStatement.execute("SET WRITE_DELAY " + writeDelay);
            checkSelects();
            checkUpdates();
            sw.zero();

            if (shutdown) {
                sStatement.execute("SHUTDOWN");

                time = sw.elapsedTime();

                storeResult("shutdown", 0, time, 0);
                System.out.println("shutdown time  -- " + time + " ms");
            }

            cConnection.close();

//            System.out.println("database close time  -- " + sw.elapsedTime() + " ms");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    void selectZip() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "SELECT TOP 1 firstname,lastname,zip,filler FROM test WHERE zip = ?");

            for (; i < bigops; i++) {
                ps.setInt(1, nextIntRandom(randomgen, smallrows));
                ps.execute();

                if ((i + 1) == 100 && sw.elapsedTime() > 50000) {
                    slow = true;
                }

                if (reportProgress && (i + 1) % 10000 == 0
                        || (slow && (i + 1) % 100 == 0)) {
                    System.out.println("Select " + (i + 1) + " : "
                                       + sw.elapsedTime() + " rps: "
                                       + (i * 1000 / (sw.elapsedTime() + 1)));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = ((long) i * 1000) / (time + 1);

        storeResult("select random zip", i, time, rate);
        System.out.println("select time for random zip " + i + " rows  -- "
                           + time + " ms -- " + rate + " tps");
    }

    void selectID() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "SELECT firstname,lastname,zip,filler FROM test WHERE id = ?");

            for (i = 0; i < bigops; i++) {
                ps.setInt(1, nextIntRandom(randomgen, bigrows - 1));
                ps.execute();

                if (reportProgress && (i + 1) % 10000 == 0
                        || (slow && (i + 1) % 100 == 0)) {
                    System.out.println("Select " + (i + 1) + " : "
                                       + (sw.elapsedTime() + 1));
                }
            }

            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = ((long) i * 1000) / (time + 1);

        storeResult("select random id", i, time, rate);
        System.out.println("select time for random id " + i + " rows  -- "
                           + time + " ms -- " + rate + " tps");
    }

    void selectZipTable() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "SELECT zip FROM zip WHERE zip = ?");

            for (i = 0; i < bigops; i++) {
                ps.setInt(1, nextIntRandom(randomgen, smallrows - 1));
                ps.execute();

                if (reportProgress && (i + 1) % 10000 == 0
                        || (slow && (i + 1) % 100 == 0)) {
                    System.out.println("Select " + (i + 1) + " : "
                                       + (sw.elapsedTime() + 1));
                }
            }

            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = ((long) i * 1000) / (time + 1);

        storeResult("select random zip (zip table)", i, time, rate);
        System.out.println("select time for random zip from zip table " + i
                           + " rows  -- " + time + " ms -- " + rate + " tps");
    }

    private void countTestID() {

        try {
            StopWatch sw = new StopWatch();

            // the tests use different indexes
            // use primary index
            sStatement.execute("SELECT count(*) from TEST where id > -1");

            ResultSet rs = sStatement.getResultSet();

            rs.next();