*
*
*/
public void testFillUp() {
StopWatch sw = new StopWatch();
int smallrows = 0xfff;
double value = 0;
String ddl1 = "DROP TABLE test IF EXISTS;"
+ "DROP TABLE zip IF EXISTS;";
String ddl2 = "CREATE TABLE zip( zip INT IDENTITY );";
String ddl3 = "CREATE " + (cachedTable ? "CACHED "
: "") + "TABLE test( id INT IDENTITY,"
+ " firstname VARCHAR(128), "
+ " lastname VARCHAR(128), "
+ " zip SMALLINT, "
+ " longfield BIGINT, "
+ " doublefield DOUBLE, "
+ " bigdecimalfield DECIMAL(19), "
+ " bigdecimal2field DECIMAL(20,4), "
+ " datefield DATE, "
+ " filler VARCHAR(128)); ";
// adding extra index will slow down inserts a bit
String ddl4 = "CREATE INDEX idx1 ON TEST (lastname);";
// adding this index will slow down inserts a lot
String ddl5 = "CREATE INDEX idx2 ON TEST (zip);";
// adding this index will slow down inserts a lot
String ddl6 = "CREATE INDEX idx3 ON TEST (longfield);";
// adding this index will slow down inserts a lot
String ddl7 = "CREATE INDEX idx4 ON TEST (bigdecimalfield);";
String ddl8 = "CREATE INDEX idx5 ON TEST (bigdecimal2field);";
String ddl9 = "CREATE INDEX idx6 ON TEST (datefield);";
// referential integrity checks will slow down inserts a bit
String ddl26 =
"ALTER TABLE test add constraint c1 FOREIGN KEY (zip) REFERENCES zip(zip);";
String filler = "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ";
try {
System.out.println("Connecting");
sw.zero();
cConnection = null;
sStatement = null;
cConnection = DriverManager.getConnection(url, user, password);
System.out.println("connected: " + sw.elapsedTime());
sw.zero();
sStatement = cConnection.createStatement();
java.util.Random randomgen = new java.util.Random();
sStatement.execute(ddl1);
sStatement.execute(ddl2);
sStatement.execute(ddl3);
System.out.println("test table with no index");
if (indexLastName) {
sStatement.execute(ddl4);
System.out.println("create index on lastname");
}
if (indexZip) {
sStatement.execute(ddl5);
System.out.println("create index on zip");
}
if (addForeignKey) {
sStatement.execute(ddl26);
System.out.println("add foreign key");
}
//
sStatement.execute(ddl6);
sStatement.execute(ddl7);
sStatement.execute(ddl8);
sStatement.execute(ddl9);
int i;
for (i = 0; i <= smallrows; i++) {
sStatement.execute("INSERT INTO zip VALUES(null);");
}
PreparedStatement ps = cConnection.prepareStatement(
"INSERT INTO test (firstname,lastname,zip,longfield,doublefield,bigdecimalfield,bigdecimal2field,datefield, filler) VALUES (?,?,?,?,?,?,?,?, ?)");
ps.setString(1, "Julia ");
ps.setString(2, "Clancy");
for (i = 0; i < bigrows; i++) {
ps.setInt(3, nextIntRandom(randomgen, smallrows));
int nextrandom = nextIntRandom(randomgen, filler.length());
int randomlength = nextIntRandom(randomgen, filler.length());
ps.setLong(4, randomgen.nextLong());
ps.setDouble(5, randomgen.nextDouble());
ps.setBigDecimal(6, new BigDecimal(randomgen.nextLong()));
ps.setBigDecimal(7, new BigDecimal(randomgen.nextDouble()));
ps.setDate(8, new java.sql.Date(nextIntRandom(randomgen, 1000)
* 24L * 3600 * 1000));
String varfiller = filler.substring(0, randomlength);
ps.setString(9, 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;");
}
}
// sStatement.execute("INSERT INTO test SELECT * FROM temptest;");
// sStatement.execute("DROP TABLE temptest;");
// sStatement.execute(ddl7);
System.out.println("Total insert: " + i);
System.out.println("Insert time: " + sw.elapsedTime() + " rps: "
+ (i * 1000 / sw.elapsedTime()));
sw.zero();
if (!network) {
sStatement.execute("SHUTDOWN");
}
cConnection.close();
System.out.println("Shutdown Time: " + sw.elapsedTime());
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}