You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by fu...@apache.org on 2007/03/15 23:27:44 UTC
svn commit: r518785 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer:
README.txt RunOptimizerTest.java query/GenericQuery.java utils/DataUtils.java
Author: fuzzylogic
Date: Thu Mar 15 15:27:42 2007
New Revision: 518785
URL: http://svn.apache.org/viewvc?view=rev&rev=518785
Log:
DERBY-2392: Add verbose option to RunOptimizerTest.
Committed for Manjula Kutty <ma...@gmail.com>
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/README.txt
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunOptimizerTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/README.txt
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/README.txt?view=diff&rev=518785&r1=518784&r2=518785
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/README.txt (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/README.txt Thu Mar 15 15:27:42 2007
@@ -30,15 +30,15 @@
java.sql.Statement and java.sql.PreparedStatement to identify the query preparation and
execution times.
-The test settings are located on the org.apache.derbyTesting.system.langtest.StaticValues
+The test settings are located on the org.apache.derbyTesting.system.optimizer.StaticValues
class. The test creates 64 tables with different column types and populates them with
the a specified number of rows (org.apache.derbyTesting.system.langtest.StaticValues.NUM_OF_ROWS).
PACKAGING:
==========
-The test resides under the org.apache.derbyTesting.system.langtest package. The main
-class to invoke is org.apache.derbyTesting.system.langtest.RunLangTest. All the
-variable settings used in the test are set in the org.apache.derbyTesting.system.langtest.StaticValues
+The test resides under the org.apache.derbyTesting.system.optimizer package. The main
+class to invoke is org.apache.derbyTesting.system.optimizer.RunOptimizerTest. All the
+variable settings used in the test are set in the org.apache.derbyTesting.system.optimizer.StaticValues
class. The query classes reside in the 'query' package while some initializer and generic utility
classes belong to the 'utils' package.
@@ -46,7 +46,7 @@
===========
Usage:
-java org.apache.derbyTesting.system.langtest.RunLangTest -reset|-qlist
+java org.apache.derbyTesting.system.optimizer.RunOptimizerTest -reset|-qlist
-reset = Reset the database and begin run
-qlist = Run only test queries from the 'query.list' file provided
-verbose = Run in the verbose mode to print all the queries being run
@@ -58,12 +58,12 @@
No arguments will run all the default test queries, provided via classes
-Set the 'derby.langtest.mode' to 'client' to run this test using the
+Set the 'derby.optimizertest.mode' to 'client' to run this test using the
DerbyClient against a Derby Network Server running on port 1527
SETTINGS:
=========
-The test settings are located on the org.apache.derbyTesting.system.langtest.StaticValues
+The test settings are located on the org.apache.derbyTesting.system.optimizer.StaticValues
class:
NUM_OF_ROWS=1000; //Total number of rows expected in each table
NUM_OF_TABLES=64; //Total number of tables to be created. This value should not get changed since all the views are depended
@@ -79,7 +79,7 @@
No arguments will run all the default test queries, provided via classes
To run the test using the DerbyClient against a Derby Network Server running on port 1527
-set the 'derby.langtest.mode' System property to 'client' .
+set the 'derby.optimizertest.mode' System property to 'client' .
KNOWN ISSUES:
=============
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunOptimizerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunOptimizerTest.java?view=diff&rev=518785&r1=518784&r2=518785
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunOptimizerTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunOptimizerTest.java Thu Mar 15 15:27:42 2007
@@ -52,8 +52,9 @@
String driverClass=StaticValues.embedClass;
String jdbcurl=StaticValues.embedURL;
boolean reset=false;
+ boolean verbose=false;
try {
- String mode=System.getProperty("derby.langtest.mode");
+ String mode=System.getProperty("derby.optimizertest.mode");
if(mode!=null){
if(mode.equalsIgnoreCase("client")){
driverClass=StaticValues.clientClass;
@@ -71,41 +72,50 @@
}
System.out.println("Running test with url "+jdbcurl);
if(args.length>0){
- if(args[0].equalsIgnoreCase("-reset"))
- reset=true;
- else if(args[0].equalsIgnoreCase("-qlist")){
- QueryList.queryListOnly=true;
- }else{
- printUsage();
- return;
+ for(int i=0;i<args.length;i++){
+ if(args[i].equalsIgnoreCase("-reset"))
+ reset=true;
+ else if(args[i].equalsIgnoreCase("-qlist"))
+ QueryList.queryListOnly=true;
+ else if(args[i].equalsIgnoreCase("-verbose"))
+ verbose=true;
+ else{
+ printUsage();
+ return;
+ }
}
- }
+ }
+
Class.forName(driverClass);
if (reset) {
- System.out.println("Initializing...");
+ if (verbose)
+ System.out.println("Initializing...");
conn = DriverManager.getConnection(jdbcurl);
TestViews.init();
- DataUtils.dropObjects(conn);
- DataUtils.createObjects(conn);
+ DataUtils.dropObjects(conn,verbose);
+ DataUtils.createObjects(conn,verbose);
}else{
conn = DriverManager.getConnection(jdbcurl);
}
- DataUtils.insertData(conn);
+ DataUtils.insertData(conn,verbose);
QueryList.init(conn);
- System.out.println(" List of query scenarios to run: "+QueryList.getQList().size());
+ if (verbose)
+ System.out.println(" List of query scenarios to run: "+QueryList.getQList().size());
for(int i=0;i<QueryList.getQList().size();i++){
- System.out.println("\n______________________________________________________________________\n");
+ if (verbose)
+ System.out.println("\n______________________________________________________________________\n");
GenericQuery gq=(GenericQuery)QueryList.getQList().get(i);
- System.out.println("*** Running query: "+gq.getDescription()+" ***");
+ if (verbose)
+ System.out.println("*** Running query: "+gq.getDescription()+" ***");
conn=null; //conn.close() throws "Invalid transaction state" exception
conn = DriverManager.getConnection(jdbcurl);
gq.setConnection(conn);
- gq.executeQueries(false); //using regular STATEMENTS
+ gq.executeQueries(false,verbose); //using regular STATEMENTS
conn.close();
conn=null; //conn.close() throws "Invalid transaction state" exception
conn = DriverManager.getConnection(jdbcurl);
gq.setConnection(conn);
- gq.executeQueries(true); //using prepared STATEMENTS
+ gq.executeQueries(true,verbose); //using prepared STATEMENTS
}
} catch (ClassNotFoundException cne) {
@@ -129,23 +139,32 @@
}
private static void printResults(){
System.out.println("\n\n========================= R E S U L T S =========================\n");
- for(int i=0;i<QueryList.getQList().size();i++){
+ for(int i=0;i<QueryList.getQList().size();i++){
System.out.println("\n________________________________________________________________________________________________");
GenericQuery gq=(GenericQuery)QueryList.getQList().get(i);
+ if (gq.getPrepStmtRunResults().size()==0){
+ System.out.println("Queries didn't run");
+ System.exit(0);
+ }
+ else{
System.out.println("Timings for Query type: "+gq.getDescription()+"\n");
System.out.println("QueryName\tUsing PreparedStatment\tUsing Statement\tRows Expected");
System.out.println("------------------------------------------------------------------------------");
-
+ System.out.println("Query size: " + gq.getQueries().size());
for(int k=0;k<gq.getQueries().size();k++){
String queryName="QUERY # "+(k+1);
- String [] prepStmtTimes=(String [])gq.getPrepStmtRunResults().get(k);
+ String[] prepStmtTimes=(String [])gq.getPrepStmtRunResults().get(k);
String [] stmtTimes=(String [])gq.getStmtRunResults().get(k);
for(int j=0; j<StaticValues.ITER;j++){
System.out.println(queryName+"\t"+prepStmtTimes[j]+"\t\t"+stmtTimes[j]+"\t"+gq.getRowsExpected(k));
}
+
System.out.println("*************************************************************************");
+
}
System.out.println("\n________________________________________________________________________________________________");
+ }
}
+
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java?view=diff&rev=518785&r1=518784&r2=518785
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java Thu Mar 15 15:27:42 2007
@@ -66,13 +66,15 @@
public String getDescription(){
return description;
}
- public void executeQueries(boolean prepare) throws SQLException{
+ public void executeQueries(boolean prepare,boolean verbose) throws SQLException{
rowsExpected=new int[queries.size()]; //initialize the array with correct size
String query="";
if(prepare){
- System.out.println("=====================> Using java.sql.PreparedStatement <====================");
+ if (verbose)
+ System.out.println("=====================> Using java.sql.PreparedStatement <====================");
}else{
- System.out.println("=====================> Using java.sql.Statement <====================");
+ if (verbose)
+ System.out.println("=====================> Using java.sql.Statement <====================");
}
try{
@@ -108,12 +110,14 @@
rowsReturned++;
}
long time_taken=(System.currentTimeMillis() - start);
- System.out.println("Time required to execute:");
- System.out.println(query);
- System.out.println("Total Rows returned = "+rowsReturned);
+ if (verbose){
+ System.out.println("Time required to execute:");
+ System.out.println(query);
+ System.out.println("Total Rows returned = "+rowsReturned);
- System.out.println("==> "+time_taken+" milliseconds "+" OR "+TestUtils.getTime(time_taken));
- times[i]=TestUtils.getTime(time_taken);
+ System.out.println("==> "+time_taken+" milliseconds "+" OR "+TestUtils.getTime(time_taken));
+ times[i]=TestUtils.getTime(time_taken);
+ }
rs.close();
if(prepare){
pstmt.close();
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java?view=diff&rev=518785&r1=518784&r2=518785
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java Thu Mar 15 15:27:42 2007
@@ -36,9 +36,10 @@
public class DataUtils {
- public static void dropObjects(Connection conn) throws SQLException {
+ public static void dropObjects(Connection conn, boolean verbose) throws SQLException {
Statement stmt = null;
- System.out.println("Dropping existing Tables and Views...");
+ if (verbose)
+ System.out.println("Dropping existing Tables and Views...");
for (int i=0;i<TestViews.dropViews.size();i++){
try{
stmt = conn.createStatement();
@@ -62,13 +63,15 @@
}
}// end for
}
- public static void createObjects(Connection conn) throws SQLException {
+ public static void createObjects(Connection conn,boolean verbose) throws SQLException {
Statement stmt = null;
- System.out.println("Creating Tables...");
+ if (verbose)
+ System.out.println("Creating Tables...");
for (int i = 1; i <= StaticValues.NUM_OF_TABLES; i++) {
try {
String tableName = StaticValues.TABLE_NAME + i;
- System.out.println(" Creating Table - "+tableName);
+ if (verbose)
+ System.out.println(" Creating Table - "+tableName);
stmt = conn.createStatement();
stmt.execute(StaticValues.CREATE_TABLE+ tableName+ StaticValues.TABLE_COLS);
@@ -77,13 +80,14 @@
if (!sqe.getSQLState().equalsIgnoreCase("X0Y32")) {
throw sqe;
} else {
- System.out.println("Table " + StaticValues.TABLE_NAME + i
+ System.out.println("Table " + StaticValues.TABLE_NAME + i
+ " exists");
}
}
}// end for
- System.out.println("Creating Views...");
+ if (verbose)
+ System.out.println("Creating Views...");
for (int i=0;i<TestViews.createViews.size();i++){
try{
stmt = conn.createStatement();
@@ -95,7 +99,7 @@
}
}
- public static void insertData(Connection conn){
+ public static void insertData(Connection conn,boolean verbose){
try{
String commonString = "String value for the ";
String valueForString = commonString + "varchar column ";
@@ -111,13 +115,15 @@
totalRows = rs.getInt(1);
}
if (totalRows >= StaticValues.NUM_OF_ROWS) {
- System.out.println(" InsertData.insert_data() => "
+ if (verbose)
+ System.out.println(" InsertData.insert_data() => "
+ totalRows + " exists in table " + tableName
+ "...");
}else{
if(totalRows>0){
- System.out.println("Dropping existing indexes from table: "
+ if (verbose)
+ System.out.println("Dropping existing indexes from table: "
+ tableName);
try {
stmt.executeUpdate("DROP INDEX " + tableName
@@ -135,7 +141,8 @@
throw sqe;
}
}
- System.out.println("Rows deleted from " + tableName + "= "
+ if (verbose)
+ System.out.println("Rows deleted from " + tableName + "= "
+ stmt.executeUpdate("DELETE FROM " + tableName));
}
PreparedStatement ps = conn
@@ -173,13 +180,15 @@
}
ps.close();
conn.commit();
- System.out.println("Inserted " + (k - 1) + " rows into "
+ if (verbose)
+ System.out.println("Inserted " + (k - 1) + " rows into "
+ tableName + " in "
+ (System.currentTimeMillis() - start)
+ " milliseconds");
conn.setAutoCommit(true);
- System.out.println("Creating indexes for table: "
+ if (verbose)
+ System.out.println("Creating indexes for table: "
+ tableName);
stmt.executeUpdate("CREATE INDEX " + tableName