package stress; import java.io.File; import java.io.FileDescriptor; import java.io.FileInputStream; import java.io.FileOutputStream; import java.net.URL; import java.net.URLClassLoader; import java.sql.*; import java.util.ArrayList; import java.util.Properties; public class TestSQLStatements { static final String stresdata_tb = "data"; static final String createtbl_prefix = "CREATE TABLE "+stresdata_tb+" (\n" + " id int(11) NOT NULL default '0',\n" + " t int(11) NOT NULL default '0',\n"; static final String fielddef = " int(11) NOT NULL default '0',\n"; static final String createtbl_suffix = " PRIMARY KEY (id,t)\n" + ") TYPE=MyISAM;"; public static void main(String[] args) throws Exception { if(args.length<1) { System.err.println(" [nf [nid [ntests [nbatches]]]]"); System.exit(1); } int ai=0; String filename = args[ai++]; boolean doPS = Boolean.valueOf(args.length > ai ? args[ai++] : "true").booleanValue(); int NF = Integer.parseInt(args.length > ai ? args[ai++] : "200"); //different fields int N_ID = Integer.parseInt(args.length > ai ? args[ai++] : "50"); //different ids int NTBB = Integer.parseInt(args.length > ai ? args[ai++] : "10"); //tests for each id, per batch int NB = Integer.parseInt(args.length > ai ? args[ai++] : "5"); //batches int NUMTESTS = NB * NTBB; //------ Properties props = new Properties(); props.load(new FileInputStream(filename)); props.store(new FileOutputStream(FileDescriptor.err), "config file: "+filename); System.err.println("------------"); String user = (String)props.remove("user"); String pwd = (String)props.remove("pwd"); String path = (String)props.remove("path"); String url = (String)props.remove("url"); String driverclass = (String)props.remove("driverclass"); String spaths = (String)props.remove("paths"); File[] paths = null; if(spaths!=null) { String[] sa = spaths.split(","); paths = new File[sa.length]; for (int i = 0; i < sa.length; i++) { paths[i] = new File(sa[i]); } } props.store(new FileOutputStream(FileDescriptor.out), "actual parameters"); System.out.println("------------"); Connection conn = null; try { ArrayList ulist = new ArrayList(); File f = new File(path.replace('/', File.separatorChar)); System.out.println(f + " : " + f.exists()); ulist.add(f.toURL()); if (paths != null) { for (int i = 0; i < paths.length; i++) { ulist.add(paths[i].toURL()); } } URL[] ua = (URL[])ulist.toArray(new URL[ulist.size()]); for (int i = 0; i < ua.length; i++) { System.out.println("url classpath: " + ua[i]); } URLClassLoader uc = new URLClassLoader(ua); Driver d = (Driver)uc.loadClass(driverclass).newInstance(); props.setProperty("user", user); if (pwd != null) props.setProperty("password", pwd); conn = d.connect(url, props); //------------------------------- prepareDB(conn, NF); //testGetMetadataAndClose(conn); workIt(conn, doPS, NF, N_ID, NUMTESTS); workItBatch(conn, doPS, NF, N_ID, NTBB, NB); //------------------------------- } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (Exception e) { } } } } static void prepareDB(Connection conn, int nfields) throws Exception { Statement st = conn.createStatement(); try { st.execute("drop table "+stresdata_tb); } catch (Exception e) { System.out.println(e); } try { StringBuffer sb = new StringBuffer(createtbl_prefix); for (int i = 1; i <= nfields; i++) { sb.append(" field_").append(i).append("_abcdefghij").append(fielddef); } sb.append(createtbl_suffix); String sql = sb.toString(); System.out.println(sql); st.execute(sql); } catch (Exception e) { System.out.println(e); } } static void testGetMetadataAndClose(Connection conn) throws Exception { long t1 = 0; long t2 = 0; int N = 1000000; for (int i = 1; i <= N; i++) { conn.getMetaData(); } t1 = System.currentTimeMillis(); for (int i = 1; i <= N; i++) { conn.getMetaData(); } t2 = System.currentTimeMillis(); System.out.println("getMetaData avg: " + 1000.0 * (t2 - t1) / N); for (int i = 1; i <= N; i++) { conn.isClosed(); } t1 = System.currentTimeMillis(); for (int i = 1; i <= N; i++) { conn.isClosed(); } t2 = System.currentTimeMillis(); System.out.println("isClosed avg: " + 1000.0 * (t2 - t1) / N); } //==== isolated methods, for optimizeit tracking only ===== private static final int insertByStatement(Statement st, String sql) throws Exception { return st.executeUpdate(sql.toString()); } private static final int insertByPreparedStatement(PreparedStatement pst) throws Exception { return pst.executeUpdate(); } private static final int updateByStatement(Statement st, String sql) throws Exception { return st.executeUpdate(sql.toString()); } private static final int updateByPreparedStatement(PreparedStatement pst) throws Exception { return pst.executeUpdate(); } static void workIt(Connection conn, boolean doPS, int NF, int N_ID, int NUMTESTS) throws Exception { StringBuffer insertStart = new StringBuffer("INSERT INTO "+stresdata_tb+" (id,t,"); StringBuffer prepInsert2 = new StringBuffer("?,?,"); StringBuffer prepUpd1 = new StringBuffer("UPDATE "+stresdata_tb+" SET "); for (int i = 1; i <= NF; i++) { insertStart.append("field_").append(i).append("_abcdefghij"); prepInsert2.append('?'); prepUpd1.append("field_").append(i).append("_abcdefghij=?"); if (i < NF) { insertStart.append(','); prepInsert2.append(','); prepUpd1.append(','); } } insertStart.append(") VALUES ("); StringBuffer fullPrepInsert = new StringBuffer(insertStart.toString()).append(prepInsert2).append(')'); System.out.println(fullPrepInsert.toString()); StringBuffer fullPrepUpd = new StringBuffer(prepUpd1.toString()).append(" WHERE (id=? AND t=?)"); System.out.println(fullPrepUpd.toString()); //System.out.println("pst_i: "+fullPrepInsert.toString().length()); //System.out.println("pst_u: "+fullPrepUpd.toString().length()); PreparedStatement pst_i = null; PreparedStatement pst_u = null; if(doPS) { pst_i = conn.prepareStatement(fullPrepInsert.toString()); //"insert into Data (id, t, f1, f2, f3, f4, f5) values(?,?,?,?,?,?,?)"); pst_u = conn.prepareStatement(fullPrepUpd.toString()); //"update Data set f1=?, f2=?, f3=?, f4=?, f5=? where (id=? AND t=?)"); } Statement st = conn.createStatement(); //--------- int N = N_ID * NUMTESTS; StringBuffer sql_i = new StringBuffer(insertStart.toString()); int mark = sql_i.length(); StringBuffer sql_u = new StringBuffer(); long t1 = 0; long t2 = 0; int ans = st.executeUpdate("delete from "+stresdata_tb); System.out.println("Delete: " + ans); System.out.print("Waiting"); System.out.print(", 3"); Thread.sleep(1000); System.out.print(", 2"); Thread.sleep(1000); System.out.print(", 1"); Thread.sleep(1000); System.out.println(" go!"); //----- t1 = System.currentTimeMillis(); for (int id = 1; id <= N_ID; id++) { System.out.print("."); for (int i = 1; i <= NUMTESTS; i++) { int t = i * 1000; //st sql_i.setLength(mark); sql_i.append(id).append(','); sql_i.append(t).append(','); for (int k = 1; k <= NF; k++) { sql_i.append(k); if (k < NF) sql_i.append(','); } sql_i.append(')'); insertByStatement(st, sql_i.toString()); } } t2 = System.currentTimeMillis(); System.out.println("st insert avg: " + 1000.0 * (t2 - t1) / N); t1 = System.currentTimeMillis(); for (int id = 1; id <= N_ID; id++) { System.out.print("."); for (int i = 1; i <= NUMTESTS; i++) { int t = i * 1000; sql_u.setLength(0); sql_u.append("UPDATE "+stresdata_tb+" SET "); for (int k = 1; k <= NF; k++) { sql_u.append("field_").append(k).append("_abcdefghij=").append(-k); if (k < NF) sql_u.append(','); } sql_u.append(" WHERE (id=").append(id); sql_u.append(" AND t=").append(t); sql_u.append(')'); updateByStatement(st, sql_u.toString()); } } t2 = System.currentTimeMillis(); System.out.println("st update avg: " + 1000.0 * (t2 - t1) / N); //----- if(doPS) { t1 = System.currentTimeMillis(); for (int id = 1; id <= N_ID; id++) { System.out.print("."); for (int i = 1; i <= NUMTESTS; i++) { int t = i * 1000; //PreparedStatement pst_i = conn.prepareStatement(fullPrepInsert.toString()); //"insert into Data (id, t, f1, f2, f3, f4, f5) values(?,?,?,?,?,?,?)"); pst_i.setInt(1, -id); pst_i.setInt(2, t); for (int k = 1; k <= NF; k++) pst_i.setInt(2 + k, k); insertByPreparedStatement(pst_i); //pst_i.close(); } } t2 = System.currentTimeMillis(); System.out.println("pst insert avg: " + 1000.0 * (t2 - t1) / N); t1 = System.currentTimeMillis(); for (int id = 1; id <= N_ID; id++) { System.out.print("."); for (int i = 1; i <= NUMTESTS; i++) { int t = i * 1000; //PreparedStatement pst_u = conn.prepareStatement(fullPrepUpd.toString()); //"update Data set f1=?, f2=?, f3=?, f4=?, f5=? where (id=? AND t=?)"); for (int k = 1; k <= NF; k++) pst_u.setInt(k, -k); pst_u.setInt(NF + 1, -id); pst_u.setInt(NF + 2, t); updateByPreparedStatement(pst_u); //pst_u.close(); } } t2 = System.currentTimeMillis(); System.out.println("pst update avg: " + 1000.0 * (t2 - t1) / N); } System.out.println("done!"); } //-------------------------- static void workItBatch(Connection conn, boolean doPS, int NF, int N_ID, int NB, int NTBB) throws Exception { StringBuffer insertStart = new StringBuffer("INSERT INTO "+stresdata_tb+" (id,t,"); StringBuffer prepInsert2 = new StringBuffer("?,?,"); StringBuffer prepUpd1 = new StringBuffer("UPDATE "+stresdata_tb+" SET "); for (int i = 1; i <= NF; i++) { insertStart.append("field_").append(i).append("_abcdefghij"); prepInsert2.append('?'); prepUpd1.append("field_").append(i).append("_abcdefghij=?"); if (i < NF) { insertStart.append(','); prepInsert2.append(','); prepUpd1.append(','); } } insertStart.append(") VALUES ("); StringBuffer fullPrepInsert = new StringBuffer(insertStart.toString()).append(prepInsert2).append(')'); System.out.println(fullPrepInsert.toString()); StringBuffer fullPrepUpd = new StringBuffer(prepUpd1.toString()).append(" WHERE (id=? AND t=?)"); System.out.println(fullPrepUpd.toString()); PreparedStatement pst_i = null; PreparedStatement pst_u = null; if(doPS) { pst_i = conn.prepareStatement(fullPrepInsert.toString()); //"insert into Data (id, t, f1, f2, f3, f4, f5) values(?,?,?,?,?,?,?)"); pst_u = conn.prepareStatement(fullPrepUpd.toString()); //"update Data set f1=?, f2=?, f3=?, f4=?, f5=? where (id=? AND t=?)"); } Statement st = conn.createStatement(); //--------- int N = N_ID * NB * NTBB; StringBuffer sql_i = new StringBuffer(insertStart.toString()); int mark = sql_i.length(); StringBuffer sql_u = new StringBuffer(); long t1 = 0; long t2 = 0; int ans = st.executeUpdate("delete from "+stresdata_tb); System.out.println("Delete: " + ans); System.out.print("Waiting, 5"); Thread.sleep(1000); System.out.print(", 4"); Thread.sleep(1000); System.out.print(", 3"); Thread.sleep(1000); System.out.print(", 2"); Thread.sleep(1000); System.out.print(", 1"); Thread.sleep(1000); System.out.println(" go!"); conn.setAutoCommit(false); //----- t1 = System.currentTimeMillis(); for (int id = 1; id <= N_ID; id++) { System.out.print("."); for (int b = 1; b <= NB; b++) { for (int i = 1; i <= NTBB; i++) { int t = (b * NTBB + i) * 1000; //st sql_i.setLength(mark); sql_i.append(id).append(','); sql_i.append(t).append(','); for (int k = 1; k <= NF; k++) { sql_i.append(k); if (k < NF) sql_i.append(','); } sql_i.append(')'); st.addBatch(sql_i.toString()); } st.executeBatch(); conn.commit(); } } t2 = System.currentTimeMillis(); System.out.println("st batch insert avg: " + 1000.0 * (t2 - t1) / N); t1 = System.currentTimeMillis(); for (int id = 1; id <= N_ID; id++) { System.out.print("."); for (int b = 1; b <= NB; b++) { for (int i = 1; i <= NTBB; i++) { int t = (b * NTBB + i) * 1000; sql_u.setLength(0); sql_u.append("UPDATE "+stresdata_tb+" SET "); for (int k = 1; k <= NF; k++) { sql_u.append("field_").append(k).append("_abcdefghij=").append(-k); if (k < NF) sql_u.append(','); } sql_u.append(" WHERE (id=").append(id); sql_u.append(" AND t=").append(t); sql_u.append(')'); st.addBatch(sql_u.toString()); } st.executeBatch(); conn.commit(); } } t2 = System.currentTimeMillis(); System.out.println("st batch update avg: " + 1000.0 * (t2 - t1) / N); //----- if(doPS) { t1 = System.currentTimeMillis(); for (int id = 1; id <= N_ID; id++) { System.out.print("."); for (int b = 1; b <= NB; b++) { for (int i = 1; i <= NTBB; i++) { int t = (b * NTBB + i) * 1000; //ps pst_i.setInt(1, -id); pst_i.setInt(2, t); for (int k = 1; k <= NF; k++) pst_i.setInt(2 + k, k); pst_i.addBatch(); } pst_i.executeBatch(); conn.commit(); } } t2 = System.currentTimeMillis(); System.out.println("pst batch insert avg: " + 1000.0 * (t2 - t1) / N); t1 = System.currentTimeMillis(); for (int id = 1; id <= N_ID; id++) { System.out.print("."); for (int b = 1; b <= NB; b++) { for (int i = 1; i <= NTBB; i++) { int t = (b * NTBB + i) * 1000; for (int k = 1; k <= NF; k++) pst_u.setInt(k, -k); pst_u.setInt(NF + 1, -id); pst_u.setInt(NF + 2, t); pst_u.addBatch(); } pst_u.executeBatch(); conn.commit(); } } t2 = System.currentTimeMillis(); System.out.println("pst batch update avg: " + 1000.0 * (t2 - t1) / N); } //------- conn.setAutoCommit(true); System.out.println("done!"); } }