/* * Copyright (c) 1997-1999 Inprise Corporation. All Rights Reserved. * * This SOURCE CODE FILE, which has been provided by Inprise as part * of an Inprise product for use ONLY by licensed users of the product, * includes CONFIDENTIAL and PROPRIETARY information of Inprise. * * USE OF THIS SOFTWARE IS GOVERNED BY THE TERMS AND CONDITIONS * OF THE LICENSE STATEMENT AND LIMITED WARRANTY FURNISHED WITH * THE PRODUCT. * * IN PARTICULAR, YOU WILL INDEMNIFY AND HOLD INPRISE, ITS RELATED * COMPANIES AND ITS SUPPLIERS, HARMLESS FROM AND AGAINST ANY CLAIMS * OR LIABILITIES ARISING OUT OF THE USE, REPRODUCTION, OR DISTRIBUTION * OF YOUR PROGRAMS, INCLUDING ANY CLAIMS OR LIABILITIES ARISING OUT OF * OR RESULTING FROM THE USE, MODIFICATION, OR DISTRIBUTION OF PROGRAMS * OR FILES CREATED FROM, BASED ON, AND/OR DERIVED FROM THIS SOURCE * CODE FILE. */ /* * Copyright (c) 1997-1999 Inprise Corporation. All Rights Reserved. * * This SOURCE CODE FILE, which has been provided by Inprise as part * of an Inprise product for use ONLY by licensed users of the product, * includes CONFIDENTIAL and PROPRIETARY information of Inprise. * * USE OF THIS SOFTWARE IS GOVERNED BY THE TERMS AND CONDITIONS * OF THE LICENSE STATEMENT AND LIMITED WARRANTY FURNISHED WITH * THE PRODUCT. * * IN PARTICULAR, YOU WILL INDEMNIFY AND HOLD INPRISE, ITS RELATED * COMPANIES AND ITS SUPPLIERS, HARMLESS FROM AND AGAINST ANY CLAIMS * OR LIABILITIES ARISING OUT OF THE USE, REPRODUCTION, OR DISTRIBUTION * OF YOUR PROGRAMS, INCLUDING ANY CLAIMS OR LIABILITIES ARISING OUT OF * OR RESULTING FROM THE USE, MODIFICATION, OR DISTRIBUTION OF PROGRAMS * OR FILES CREATED FROM, BASED ON, AND/OR DERIVED FROM THIS SOURCE * CODE FILE. */ package com.borland.samples.dx.bench; import com.borland.dx.dataset.Variant; import java.util.TooManyListenersException; import com.borland.jbcl.util.*; import com.borland.jbcl.model.*; import com.borland.datastore.*; import com.borland.dx.dataset.*; import com.borland.dx.sql.dataset.*; import com.borland.dx.sql.metadata.MetaData; import java.io.*; import java.sql.*; import java.math.BigDecimal; /** Basic test suite used to compare performance of DataExpress components vs raw JDBC calls for retrieval, update, delete, and insert of data. To achieve optimal performance using DataExpress or raw JDBC, set the fast flag to true and the resetPending flag to false. To customize for a particular driver/server see JDBCDriver class in this file. This class can be extended to deal with driver differences. Notes for improving DataExpress performance (techniques used by fast and the noResetStatus flags): Data retrieval: 1) Disable metadata discovery mechanisms for fetch operations. To disable the meta data discovery mechanisms: a) set StorageDataSet.MetaDataUpdate property to MetaDataUpdate.NONE. b) Set the StorageDataSet.TableName property to the table name. c) Set Column.RowId property for the columns that uniquely and efficiently identify a row. For small result set queries this can make a big performance improvement. Note that QueryDataSet only performs the meta data discovery operations the first time a query is run. 2) Set the LoadOption property on Query/ProcedureDataSet to Load.ASYNCHRONOUS or Load.AS_NEEDED. You can also set this property to Load.UNCACHED if you will be reading the data one time in sequential order. 3) For large result sets, using a DataStore can improve performance and save a lot of memory with its caching/persistence support. Data inserts/deletes/updates: 1) For updates and deletes, set the Resolver property to a QueryResolver. set the UpdateMode property of this QueryResolver to UpdateMode.KEY_COLUMNS. This weakens the Optimistic concurrency used, but reduces the number of parameters set for an update/delete operation. 2) For each call to Database.saveChanges() calls are made to disable/enable a JDBC drivers autocommit mode. If your application calls database.saveChanges() with the useTransactions parameter set to false, then these calls will not be made and the transaction will not be commited. 3) By disabling the resetPendingStatus flag in the Database.saveChanges() method, further performance benefits can be achieved. With this disabled, DataExpress will not clear the RowStatus state for all inserted/deleted/updated rows. This is only desirable if you will not be calling saveChanges() with new edits on the DataSet without calling refresh first. 4) Statement caching. By default, DataExpress will cache prepared statements for both queries and stored procedures if java.sql.Connection.getMetaData().getMaxStatements() returns a value > 10. You can force statement caching in JBuilder 3.0 by calling Database.setCacheStatements(true). The prepared statements that are cached are not closed until on of the following happens: a) Some provider related property like the query property is changed. b) A DataSet component is garbage collected (statement closed in a finalize() method. c) QueryDataSet.closeStatement(), ProcedureDataSet.closeStatement(), QueryProvider.closeStatement() or ProcedureProvider.closeStatement() are called. These are new to JBuilder 3.0 Note that if transactions are disabled, your application must call database.commit() or connection.commit(). Benchmark Questions and answers: 1) Does the JDBCFetchRows test retrieve just because that's what the DataExpress test does? Yes. But note that DataExpress does not have to retrieve all rows up front either. The test could be modified to use different QueryDataSet load options that retrieve rows on demand or asynchrounously. 2) Why does JDBCFetchRows perform a commit when the fast option is enabled? Depending on the isolation level of your connection, the transaction should released to free up any locks that may be held. */ public class JDBCBenchMark { static String tempDir = "c:/temp"; // Directory used for DataStore data files public static void main(String args[]) { new JDBCBenchMark().runTest(args); } public final void runTest(String args[]) { batchSize = 50; // number of rows to operate on per batch operation. batchCount = 5; // number of batch operations to perform on a batch size batch. /* fast/cacheStatments/noResetStatus optimizations. */ fast = true; // take measures for optimal performance. cacheStatements = false; // JDBC test cases will cache statments. Disabled by default // because not as valid with optimistic concurrency insert/delete/update // operations and there is a resource overhead for holding onto statements. // Probably a more valid optimization for fetch operations. noResetStatus = true ; // Reset RowStatus so secondary saveChanges can be performed // without refetching a fresh copy of the data. garbageCollect = true; // force garbage collection inbetween each timing. verbose = false; // verbose output useDataStore = false; // Use a dataStore component for storage. sleepInterval = 0; // sleep interval inbetween each timing. (To quiesce the system.) JDBCDriver driver; if (args == null || args.length < 1) driver = new JDBCDriver(); else if (args[0].equalsIgnoreCase("interbase")) driver = new JDBCDriver(); else if (args[0].equalsIgnoreCase("mssql")) driver = new MSSQLDriver(); else if (args[0].equalsIgnoreCase("db2")) driver = new DB2Driver(); else if (args[0].equalsIgnoreCase("sybase")) driver = new SybaseSQLDriver(); else if (args[0].equalsIgnoreCase("datastore")) driver = new DataStoreDriver(); else driver = new JDBCDriver(); driver.connect(); database = driver.database; connection = driver.connection; reportProperties(driver, args); init(driver); // Shows that there is minimal overhead for accessing a dataSet the way // the jdbc* tests do. // dataSetAccessOverhead(); System.out.println("\n*** JDBC insert/update/delete"); jdbcInsertRows(batchCount); jdbcUpdateRows(); jdbcDeleteRows(); report(); // Quiesce the system before starting the DataExpress tests. // sleep(1000); init(driver); System.out.println("*** insert/update/delete"); // First call to saveChanges() is slower due to meta data calls made // against java.sql.DatabaseMetaData such as getIdentifierQuoteString() // and getMaxStatements(). Note that this is a one time cost for the // life time of the Database component. // insertRows(); updateRows(); deleteRows(); report(); // Add rows back in, but empty timings out since jdbcInsertRows already timed. // Note that use only batch count of 1 so queries are done for batchSize // rows. // jdbcInsertRows(1); try { timings.empty(); } catch (Exception ex) { fail(ex); } System.out.println("\n*** JDBC fetch tests"); jdbcFetchRows(); report(); System.out.println("\n*** DataExpress fetch tests"); fetchRows(); refetchRows(); fetchRowsUncached(); fetchRowsMetaDataUpdateNone(); report(); System.out.println("*** JDBCBenchMark test end"); System.exit(1); } private final void fetchRows() { try { for (int index = 0; index < batchCount; ++index) { genDataSet.close(); genDataSet.setQuery(new QueryDescriptor(database, sqlQuery)); genDataSet.setMetaDataUpdate(MetaDataUpdate.ALL); start(); // java.sql.DriverManager.setLogStream(System.out); genDataSet.open(); if (fast) connection.commit(); // java.sql.DriverManager.setLogStream(null); complete(); record("fetchRows"); } } catch (Exception ex) { fail(ex); } } private final void refetchRows() { try { for (int index = 0; index < batchCount; ++index) { genDataSet.close(); genDataSet.setQuery(new QueryDescriptor(database, sqlQuery)); genDataSet.setMetaDataUpdate(MetaDataUpdate.ALL); genDataSet.open(); start(); // java.sql.DriverManager.setLogStream(System.out); genDataSet.executeQuery(); if (fast) connection.commit(); // java.sql.DriverManager.setLogStream(null); complete(); record("refetchRows"); } } catch (Exception ex) { fail(ex); } } private final void fetchRowsMetaDataUpdateNone() { try { for (int index = 0; index < batchCount; ++index) { genDataSet.close(); genDataSet.setQuery(new QueryDescriptor(database, sqlQuery)); genDataSet.setMetaDataUpdate(MetaDataUpdate.NONE); start(); // java.sql.DriverManager.setLogStream(System.out); genDataSet.open(); if (fast) connection.commit(); // java.sql.DriverManager.setLogStream(null); complete(); record("fetchRowsMetaDataUpdateNone"); } } catch (Exception ex) { fail(ex); } } private final void fetchRowsUncached() { try { for (int index = 0; index < batchCount; ++index) { genDataSet.close(); genDataSet.setQuery(new QueryDescriptor(database, sqlQuery, null, true, Load.UNCACHED)); genDataSet.setMetaDataUpdate(MetaDataUpdate.NONE); start(); // java.sql.DriverManager.setLogStream(System.out); genDataSet.open(); while (genDataSet.next()) ; if (fast) connection.commit(); // java.sql.DriverManager.setLogStream(null); complete(); record("fetchRowsUncached"); } } catch (Exception ex) { fail(ex); } } private final void jdbcFetchRows() { try { for (int index = 0; index < batchCount; ++index) { if (!cacheStatements) start(); // java.sql.DriverManager.setLogStream(System.out); PreparedStatement statement = connection.prepareStatement(sqlQuery); if (cacheStatements) start(); ResultSet result = statement.executeQuery(); while(result.next()) { result.getInt(1); result.wasNull(); result.getInt(2); result.wasNull(); result.getString(3); result.wasNull(); result.getString(4); result.wasNull(); result.getString(5); result.wasNull(); result.getDouble(6); result.wasNull(); result.getInt(7); result.wasNull(); result.getInt(8); result.wasNull(); result.getInt(9); result.wasNull(); result.getString(10); result.wasNull(); result.getString(11); result.wasNull(); result.getString(12); result.wasNull(); result.getString(13); result.wasNull(); } if (fast) connection.commit(); if (cacheStatements) complete(); statement.close(); // java.sql.DriverManager.setLogStream(null); if (!cacheStatements) complete(); record("jdbcFetchRows"); } } catch (Exception ex) { fail(ex); } } private final void insertRows() { try { for (int index = 0; index < batchCount; ++index) { // Pass in starting key value for next batch. // generateData(batchSize*index); // java.sql.DriverManager.setLogStream(System.out); start(); if (fast) { database.saveChanges(new DataSet[] {genDataSet}, false, true, noResetStatus); connection.commit(); } else database.saveChanges(genDataSet); // java.sql.DriverManager.setLogStream(null); complete(); record("insertRows"); } } catch (Exception ex) { fail(ex); } } private final void deleteRows() { try { genDataSet.close(); genDataSet.setQuery(new QueryDescriptor(database, sqlQuery)); genDataSet.open(); for (int index = 0; index < batchCount; ++index) { for (int row = 0; row < batchSize; ++row) { genDataSet.deleteRow(); } start(); // java.sql.DriverManager.setLogStream(System.out); if (fast) { database.saveChanges(new DataSet[] {genDataSet}, false, true, noResetStatus); connection.commit(); } else database.saveChanges(genDataSet); // java.sql.DriverManager.setLogStream(null); complete(); // This takes the cost of updating status out of saveChanges call assuming // that the genDataSet would not be edited after the call to saveChanges. If // genDataSet were to be edited after the saveChanges, then resetChanges // should be set to false so that this cost is included in the timing. // if (fast && !noResetStatus) genDataSet.resetPendingStatus(true); record("deleteRows"); } } catch (Exception ex) { fail(ex); } } private final void dataSetAccessOverhead() { try { start(); genDataSet.first(); while (genDataSet.inBounds()) { genDataSet.getInt(0); genDataSet.getInt(1); genDataSet.getString(2); genDataSet.getString(3); genDataSet.getString(4); genDataSet.getDouble(5); genDataSet.getInt(6); genDataSet.getInt(7); genDataSet.getInt(8); genDataSet.getString(9); genDataSet.getString(10); genDataSet.getString(11); genDataSet.getString(12); genDataSet.next(); } complete(); record("dataSetAccessOverhead"); } catch (Exception ex) { fail(ex); } } private final void jdbcInsertRows(int batchCount) { try { for (int index = 0; index < batchCount; ++index) { // Pass in starting key value for next batch. // generateData(index*batchSize); // java.sql.DriverManager.setLogStream(System.out); if (!cacheStatements) start(); String insertString = "INSERT INTO "+table+" (" + int1+", "+ int2+", "+ string3+", "+ string4+", "+ string5+", "+ double6+", "+ int7+", "+ int8+", "+ int9+", "+ string10+", "+ string11+", "+ string12+", "+ string13+") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement insertStatement = connection.prepareStatement(insertString); if (cacheStatements) start(); genDataSet.first(); while (genDataSet.inBounds()) { insertStatement.setInt(1, genDataSet.getInt(0)); insertStatement.setInt(2, genDataSet.getInt(1)); insertStatement.setString(3, genDataSet.getString(2)); insertStatement.setString(4, genDataSet.getString(3)); insertStatement.setString(5, genDataSet.getString(4)); insertStatement.setDouble(6, genDataSet.getDouble(5)); insertStatement.setInt(7, genDataSet.getInt(6)); insertStatement.setInt(8, genDataSet.getInt(7)); insertStatement.setInt(9, genDataSet.getInt(8)); insertStatement.setString(10, genDataSet.getString(9)); insertStatement.setString(11, genDataSet.getString(10)); insertStatement.setString(12, genDataSet.getString(11)); insertStatement.setString(13, genDataSet.getString(12)); insertStatement.executeUpdate(); genDataSet.next(); } if (fast) connection.commit(); if (cacheStatements) complete(); insertStatement.close(); if (!cacheStatements) complete(); // java.sql.DriverManager.setLogStream(null); record("jdbcInsertRows"); } } catch (Exception ex) { fail(ex); } } private final void jdbcUpdateRows() { try { genDataSet.close(); genDataSet.setQuery(new QueryDescriptor(database, sqlQuery)); genDataSet.open(); for (int index = 0; index < batchCount; ++index) { genDataSet.goToRow(index*batchSize); if (!cacheStatements) start(); // java.sql.DriverManager.setLogStream(System.out); // System.out.println("============================================="); String updateString = "UPDATE "+table+" SET INT2 = ? WHERE " + int1+" = ?"; if (!fast) updateString += " AND "+ int2+" = ? AND "+ string3+" = ? AND "+ string4+" = ? AND "+ string5+" = ? AND "+ double6+" = ? AND "+ int7+" = ? AND "+ int8+" = ? AND "+ int9+" = ? AND "+ string10+" = ? AND "+ string11+" = ? AND "+ string12+" = ? AND "+ string13+" = ?"; PreparedStatement updateStatement = connection.prepareStatement(updateString); if (cacheStatements) start(); for (int row = 0; row < batchSize; ++row) { // System.out.println("int1: "+genDataSet.getInt(0)); updateStatement.setInt(1, genDataSet.getInt(1)+5000); updateStatement.setInt(2, genDataSet.getInt(0)); if (!fast) { updateStatement.setInt(3, genDataSet.getInt(1)); updateStatement.setString(4, genDataSet.getString(2)); updateStatement.setString(5, genDataSet.getString(3)); updateStatement.setString(6, genDataSet.getString(4)); updateStatement.setDouble(7, genDataSet.getDouble(5)); updateStatement.setInt(8, genDataSet.getInt(6)); updateStatement.setInt(9, genDataSet.getInt(7)); updateStatement.setInt(10, genDataSet.getInt(8)); updateStatement.setString(11, genDataSet.getString(9)); updateStatement.setString(12, genDataSet.getString(10)); updateStatement.setString(13, genDataSet.getString(11)); updateStatement.setString(14, genDataSet.getString(12)); } updateStatement.executeUpdate(); genDataSet.next(); } if (fast) connection.commit(); if (cacheStatements) complete(); updateStatement.close(); if (!cacheStatements) complete(); // java.sql.DriverManager.setLogStream(null); record("jdbcUpdateRows"); } } catch (Exception ex) { fail(ex); } } private final void jdbcDeleteRows() { try { genDataSet.close(); genDataSet.setQuery(new QueryDescriptor(database, sqlQuery)); genDataSet.open(); for (int index = 0; index < batchCount; ++index) { genDataSet.goToRow(index*batchSize); if (!cacheStatements) start(); String deleteString = "DELETE FROM "+table+" WHERE " +int1+" = ?"; if (!fast) deleteString += " AND "+ int2+" = ? AND "+ string3+" = ? AND "+ string4+" = ? AND "+ string5+" = ? AND "+ double6+" = ? AND "+ int7+" = ? AND "+ int8+" = ? AND "+ int9+" = ? AND "+ string10+" = ? AND "+ string11+" = ? AND "+ string12+" = ? AND "+ string13+" = ?"; // System.out.println("deleteString: "+deleteString); // java.sql.DriverManager.setLogStream(System.out); PreparedStatement deleteStatement = connection.prepareStatement(deleteString); if (cacheStatements) start(); for (int row = 0; row < batchSize; ++row) { deleteStatement.setInt(1, genDataSet.getInt(0)); if (!fast) { deleteStatement.setInt(2, genDataSet.getInt(1)); deleteStatement.setString(3, genDataSet.getString(2)); deleteStatement.setString(4, genDataSet.getString(3)); deleteStatement.setString(5, genDataSet.getString(4)); deleteStatement.setDouble(6, genDataSet.getDouble(5)); deleteStatement.setInt(7, genDataSet.getInt(6)); deleteStatement.setInt(8, genDataSet.getInt(7)); deleteStatement.setInt(9, genDataSet.getInt(8)); deleteStatement.setString(10, genDataSet.getString(9)); deleteStatement.setString(11, genDataSet.getString(10)); deleteStatement.setString(12, genDataSet.getString(11)); deleteStatement.setString(13, genDataSet.getString(12)); } deleteStatement.executeUpdate(); genDataSet.next(); } if (fast) connection.commit(); if (cacheStatements) complete(); deleteStatement.close(); if (!cacheStatements) complete(); // java.sql.DriverManager.setLogStream(null); record("jdbcDeleteRows"); } } catch (Exception ex) { fail(ex); } } private final void updateRows() { try { genDataSet.close(); genDataSet.setQuery(new QueryDescriptor(database, sqlQuery)); genDataSet.open(); // System.out.println("updateRows: "+genDataSet.getRowCount()); for (int index = 0; index < batchCount; ++index) { genDataSet.goToRow(index*batchSize); for (int row = 0; row < batchSize; ++row) { genDataSet.setInt(1, genDataSet.getInt(1)+5000); genDataSet.next(); } start(); if (fast) { // java.sql.DriverManager.setLogStream(System.out); database.saveChanges(new DataSet[] {genDataSet}, false, true, noResetStatus); connection.commit(); // java.sql.DriverManager.setLogStream(null); } else { database.saveChanges(genDataSet); } complete(); // This takes the cost of updating status out of saveChanges call assuming // that the genDataSet would not be edited after the call to saveChanges. If // genDataSet were to be edited after the saveChanges, then resetChanges // should be set to false so that this cost is included in the timing. // if (fast && !noResetStatus) genDataSet.resetPendingStatus(true); record("updateRows"); } } catch (Exception ex) { fail(ex); } } // Pass in starting key value for next batch. // private final void generateData(int row) { try { if (useDataStore) { if (store != null) ((DataStore)store).close(); DataStore dataStore = new DataStore(); dataStore.setFileName(tempDir+"/bench"); new File(tempDir+"/bench.jds").delete(); if (fast) dataStore.setSaveMode(0); dataStore.create(); store = dataStore; } genDataSet = new QueryDataSet(); genDataSet.setStore(store); genDataSet.setStoreName(table); genDataSet.setTableName(table); if (fast) { QueryResolver resolver = new QueryResolver(); resolver.setUpdateMode(UpdateMode.KEY_COLUMNS); genDataSet.setResolver(resolver); } genDataSet.addColumn(int1, int1, Variant.INT); genDataSet.getColumn(0).setRowId(true); genDataSet.addColumn(int2, int2, Variant.INT); genDataSet.addColumn(string3, string3, Variant.STRING); genDataSet.addColumn(string4, string4, Variant.STRING); genDataSet.addColumn(string5, string3, Variant.STRING); genDataSet.addColumn(double6, double6, Variant.DOUBLE); genDataSet.addColumn(int7, int7, Variant.INT); genDataSet.addColumn(int8, int8, Variant.INT); genDataSet.addColumn(int9, int9, Variant.INT); genDataSet.addColumn(string10, string10, Variant.STRING); genDataSet.addColumn(string11, string11, Variant.STRING); genDataSet.addColumn(string12, string12, Variant.STRING); genDataSet.addColumn(string13, string13, Variant.STRING); genDataSet.open(); DataRow dataRow = new DataRow(genDataSet); int lastRow = row + batchSize; for (; row < lastRow; ++row) { dataRow.setInt(int1, row); dataRow.setInt(int2, makeInt(row)); dataRow.setString(string3, makeFirstName(row)); dataRow.setString(string4, makeLastName(row)); dataRow.setString(string5, makePhone(row)); dataRow.setDouble(double6, makeDouble(row)); dataRow.setInt(int7, makeInt(row)); dataRow.setInt(int8, makeInt(row)); dataRow.setInt(int9, makeInt(row)); dataRow.setString(string10, makePhone(row)); dataRow.setString(string11, makeLastName(row)); dataRow.setString(string12, makeFirstName(row)); dataRow.setString(string13, makePhone(row)); genDataSet.addRow(dataRow); } } catch(Exception ex) { ex.printStackTrace(); } } private final void init(JDBCDriver driver) { try { start(); // Pass in starting key value for next batch. // generateData(0); // some SQL DDL can't be part of multi-statement transactions // database.setAutoCommit(true); driver.createBenchTable(genDataSet); if (fast) { try { database.setAutoCommit(false); } catch(Exception ex) { fail(ex); } } else { try { database.setAutoCommit(true); } catch(Exception ex) { fail(ex); } } timings = new TableDataSet(); timings.addColumn("test", Variant.STRING); timings.addColumn("time", Variant.LONG); timings.open(); complete(); record("createTables"); } catch (Exception ex) { fail(ex); } } static String[] phones = { "(408) 555-6598", "(415) 666-7893", "(234) 757-8923", "(407) 655-6998", "(115) 664-78493", "(233) 750-8323", "(407) 653-6998", "(115) 264-78493", "(233) 758-9323", }; private final String makePhone(int row) { int index = ((row % phones.length) % phones.length) + (row % phones.length); if (index >= phones.length) index -= phones.length; return phones[index]; } private final static int[] ints = { 0, 0x7FFF, 0x7F, 0x7FFFffff, 0x8000, 0x80000000, 1 , -1}; private final int makeInt(int row) { return ints[row%ints.length]; } private final static String[] lastNames = { "Nelson", "Young", "Lambert", "Johnson", "Forest", "Weston", "Lee", "Hall", "Young", "Papadopoulos", "Fisher", "Bennet", "De Souza", "Baldwin", "Reeves", "Stansbury", "Phong", "Ramanathan", "Steadman", "Nordstrom", "Leung", "O'Brien", "Burbank", "Sutherland", "Bishop", "MacDonald", "Williams", "Bender", "Cook", "Brown", "Ichida", "Page", "Parker", "Yamamoto", "Ferrari", "Yanowski", "Glon", "Johnson", "Green", "Osborne", "Montgomery", "Guckenheimer", // This one is useful for partial locate tests becuase it // starts with another Montgomery entry above. "Mont" }; private final String makeLastName(int row) { return lastNames[row % lastNames.length]; } static String[] firstNames = { "Robert", "Bruce", "Kim", "Leslie", "Phil", "K. J.", "Terri", "Stewart", "Katherine", "Chris", "Pete", "Ann", "Roger", "Janet", "Roger", "Willie", "Leslie", "Ashok", "Walter", "Carol", "Luke", "Sue Anne", "Jennifer M.", "Claudia", "Dana", "Mary S.", "Randy", "Oliver H.", "Kevin", "Kelly", "Yuki", "Mary", "Bill", "Takashi", "Roberto", "Michael", "Jacques", "Scott", "T.J.", "Pierre", "John", "Mark", }; private final String makeFirstName(int row) { int index = ((row / firstNames.length) % firstNames.length) + (row % firstNames.length); if (index >= firstNames.length) index -= firstNames.length; return firstNames[index]; } private final static double[] doubles = { 0.0, 55.78, 99.99, 143.30, 166.29, 32.08, 864.01 }; private final double makeDouble(int row) { return row * (doubles[row % doubles.length]); } private long allocatedMemory() { Runtime runTime = Runtime.getRuntime(); return runTime.totalMemory() - runTime.freeMemory(); } private void start() { duration = 0; memoryConsumed = 0; success = false; if (garbageCollect) { System.gc(); System.runFinalization(); } // startMemory = allocatedMemory(); startTime = System.currentTimeMillis(); } private void complete() { duration = System.currentTimeMillis() - startTime; if (garbageCollect) { System.gc(); System.runFinalization(); } // memoryConsumed = allocatedMemory() - startMemory; } private boolean succeeded() { return success; } private long getDuration() { return duration; } private long getMemoryConsumed() { return memoryConsumed; } private void record(String message) throws Exception { timings.insertRow(true); timings.setString(0, message); timings.setLong(1, getDuration()); if (sleepInterval != 0) Thread.currentThread().sleep(sleepInterval); } private final void sleep(long interval) { try { Thread.currentThread().sleep(interval); } catch(Exception ex) { fail(ex); } } private final void fail(Exception ex) { ex.printStackTrace(); System.exit(1); } private void reportProperties(JDBCDriver driver, String[] args) { String argStr = ""; if (args != null) { for (int i = 0; i < args.length; i++) argStr = argStr + args[i] + " "; } System.out.println("Command Line Args: " + argStr); System.out.println("\nConnection Informaiton"); System.out.println(" Url: " + driver.getUrl()); System.out.println(" User: " + driver.getUser()); System.out.println(" Pwd: " + driver.getPassword()); System.out.println(" Driver: " + driver.getDriver()); System.out.println("\nTest Settings"); System.out.println(" batchSize: " + batchSize); System.out.println(" batchCount: " + batchCount); System.out.println(" fast: " + fast); System.out.println(" cacheStatements: " + cacheStatements); System.out.println(" garbageCollect: " + garbageCollect); System.out.println(" verbose: " + verbose); System.out.println(" useDataStore: " + useDataStore); System.out.println(" sleepInterval: " + sleepInterval); System.out.println(" noResetStatus: " + noResetStatus); } private void report() { try { if (timings.getRowCount() > 0) { timings.first(); String message = ""; int count = 0; double total = 0; double min = -1; double max = 0; while (timings.inBounds()) { if (!message.equals(timings.getString(0))) { if (count > 1) System.out.println("Min/Max/Average after first run for "+message+": "+min+" "+max+" "+(total/count)); System.out.println(""); message = timings.getString(0); System.out.println("First run for "+message+": "+timings.getLong(1)); min = -1; max = 0; count = 0; total = 0; } else { if (verbose) System.out.println("Secondary run for "+message+": "+timings.getLong(1)); ++count; double t = (double) timings.getLong(1); total += t; if (t > max) max = t; if (t < min || min < 0) min = t; } timings.next(); } if (count > 1) System.out.println("Min/Max/Average after first run for "+message+": "+min+" "+max+" "+(total/count)); timings.empty(); } } catch(Exception ex) { ex.printStackTrace(); System.exit(1); } } private long startTime; private long startMemory; private long duration; private long memoryConsumed; private boolean success; static final String table = "JDBCBENCHTABLE"; static final String sqlQuery = "select * from "+table; static final String int1 = "INT1"; static final String int2 = "INT2"; static final String string3 = "STRING3"; static final String string4 = "STRING4"; static final String string5 = "STRING5"; static final String double6 = "DOUBLE6"; static final String int7 = "INT7"; static final String int8 = "INT8"; static final String int9 = "INT9"; static final String string10 = "STRING10"; static final String string11 = "STRING11"; static final String string12 = "STRING12"; static final String string13 = "STRING13"; private TableDataSet timings; private int batchSize; private int batchCount; private boolean cacheStatements; private boolean fast; private boolean noResetStatus; private boolean garbageCollect; private boolean verbose; private boolean useDataStore; private int sleepInterval; private Store store; private Database database; private Connection connection; private QueryDataSet genDataSet; } // Base implementation that defaults to interbase. // // Base implementation that defaults to interbase. // class JDBCDriver { static String tempDir = JDBCBenchMark.tempDir; void connect() { try { database = new Database(); database.setConnection(new ConnectionDescriptor( getUrl(), getUser(), getPassword(), false, getDriver() )); connection = database.getJdbcConnection(); } catch(DataSetException ex) { ex.printStackTrace(); System.exit(1); } } void createBenchTable(StorageDataSet dataSet) throws Exception { deleteTable(dataSet); createTable(dataSet); createIndex(dataSet); } void deleteTable(StorageDataSet dataSet) throws Exception { MetaData metadata = MetaData.getMetaData(database); try { metadata.dropTable(dataSet.getTableName());} catch(Exception ex) { ex.printStackTrace(); database.rollback(); // just ignore. // throw ex; } } void createTable(StorageDataSet dataSet) throws Exception { MetaData metadata = MetaData.getMetaData(database); metadata.createTable(dataSet.getTableName(), dataSet); } void createIndex(StorageDataSet dataSet) throws Exception { String query = "create unique index " + dataSet.getTableName() + "1 on " + dataSet.getTableName() + "(" + JDBCBenchMark.int1 + ")"; database.executeStatement(query); } String getUrl() { return "jdbc:odbc:DataSet Tutorial"; } String getUser() { return "sysdba"; } String getPassword() { return "masterkey"; } String getDriver() { return Database.DEFAULT_DRIVERS; } Database database; Connection connection; } class MSSQLDriver extends JDBCDriver { void createTable(StorageDataSet dataSet) throws Exception { MetaData metadata = MetaData.getMetaData(database); try { Statement stmt; System.out.println("Creating table"); stmt = connection.createStatement(); stmt.execute("CREATE TABLE " + dataSet.getTableName() + " (" + JDBCBenchMark.int1 + " INT NOT NULL, " + JDBCBenchMark.int2 + " INT NOT NULL, " + JDBCBenchMark.string3 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string4 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string5 + " CHAR(30) NOT NULL, " + JDBCBenchMark.double6 + " FLOAT NOT NULL, " + JDBCBenchMark.int7 + " INT NOT NULL, " + JDBCBenchMark.int8 + " INT NOT NULL, " + JDBCBenchMark.int9 + " INT NOT NULL, " + JDBCBenchMark.string10 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string11 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string12 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string13 + " CHAR(30) NOT NULL) "); connection.commit(); stmt.close(); } catch(Exception ex) { ex.printStackTrace(); throw ex; } } String getUrl() { return "jdbc:ff-microsoft://localhost:1433/SRS"; } String getUser() { return "sa"; } String getPassword() { return "letmein"; } String getDriver() { return "connect.microsoft.MicrosoftDriver"; } } class DB2Driver extends JDBCDriver { void createTable(StorageDataSet dataSet) throws Exception { MetaData metadata = MetaData.getMetaData(database); try { Statement stmt; System.out.println("Creating table"); stmt = connection.createStatement(); stmt.execute("CREATE TABLE " + dataSet.getTableName() + " (" + JDBCBenchMark.int1 + " INT NOT NULL, " + JDBCBenchMark.int2 + " INT NOT NULL, " + JDBCBenchMark.string3 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string4 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string5 + " CHAR(30) NOT NULL, " + JDBCBenchMark.double6 + " FLOAT NOT NULL, " + JDBCBenchMark.int7 + " INT NOT NULL, " + JDBCBenchMark.int8 + " INT NOT NULL, " + JDBCBenchMark.int9 + " INT NOT NULL, " + JDBCBenchMark.string10 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string11 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string12 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string13 + " CHAR(30) NOT NULL) "); connection.commit(); stmt.close(); } catch(Exception ex) { ex.printStackTrace(); throw ex; } } String getUrl() { return "jdbc:db2:sample"; } String getUser() { return "db2admin"; } String getPassword() { return "jamesf"; } String getDriver() { return "COM.ibm.db2.jdbc.app.DB2Driver"; } } class SybaseSQLDriver extends JDBCDriver { void createTable(StorageDataSet dataSet) throws Exception { MetaData metadata = MetaData.getMetaData(database); try { Statement stmt; System.out.println("Creating table"); stmt = connection.createStatement(); stmt.execute("CREATE TABLE " + dataSet.getTableName() + " (" + JDBCBenchMark.int1 + " INT NOT NULL, " + JDBCBenchMark.int2 + " INT NOT NULL, " + JDBCBenchMark.string3 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string4 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string5 + " CHAR(30) NOT NULL, " + JDBCBenchMark.double6 + " FLOAT NOT NULL, " + JDBCBenchMark.int7 + " INT NOT NULL, " + JDBCBenchMark.int8 + " INT NOT NULL, " + JDBCBenchMark.int9 + " INT NOT NULL, " + JDBCBenchMark.string10 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string11 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string12 + " CHAR(30) NOT NULL, " + JDBCBenchMark.string13 + " CHAR(30) NOT NULL) "); connection.commit(); stmt.close(); } catch(Exception ex) { ex.printStackTrace(); throw ex; } } String getUrl() { return "jdbc:ff-sybase://STEROID:5000/testdb"; } String getUser() { return "sa"; } String getPassword() { return "jamesf"; } String getDriver() { return "connect.sybase.SybaseDriver"; } } class DataStoreDriver extends JDBCDriver { void connect() { DataStore dataStore = new DataStore(); try { TxManager manager = new TxManager(); // manager.setSoftRecovery(false); dataStore.setTxManager(manager); dataStore.setUserName("foo"); dataStore.setFileName(tempDir+"/benchDataStore.jds"); dataStore.create(); dataStore.close(); } catch(DataSetException ex) { } super.connect(); } void createTable(StorageDataSet dataSet) throws Exception { try { Statement stmt; System.out.println("Creating table"); stmt = connection.createStatement(); stmt.execute("CREATE TABLE " + dataSet.getTableName() + " (" + JDBCBenchMark.int1 + " INT , " + JDBCBenchMark.int2 + " INT , " + JDBCBenchMark.string3 + " CHAR(30) , " + JDBCBenchMark.string4 + " CHAR(30) , " + JDBCBenchMark.string5 + " CHAR(30) , " + JDBCBenchMark.double6 + " DOUBLE , " + JDBCBenchMark.int7 + " INT , " + JDBCBenchMark.int8 + " INT , " + JDBCBenchMark.int9 + " INT , " + JDBCBenchMark.string10 + " CHAR(30) , " + JDBCBenchMark.string11 + " CHAR(30) , " + JDBCBenchMark.string12 + " CHAR(30) , " + JDBCBenchMark.string13 + " CHAR(30) ) "); connection.commit(); stmt.close(); } catch(Exception ex) { ex.printStackTrace(); throw ex; } } void createBenchTable(StorageDataSet dataSet) throws Exception { deleteTable(dataSet); createTable(dataSet); createIndex(dataSet); } void deleteTable(StorageDataSet dataSet) throws Exception { MetaData metadata = MetaData.getMetaData(database); try { metadata.dropTable(dataSet.getTableName());} catch(Exception ex) { ex.printStackTrace(); database.rollback(); // just ignore. // throw ex; } } String getUrl() { return "jdbc:borland:dsLocal:"+tempDir+"/benchDataStore.jds"; } String getUser() { return "Frank"; } String getPassword() { return ""; } String getDriver() { return "com.borland.datastore.jdbc.DataStoreDriver"; } }