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 ka...@apache.org on 2010/04/06 17:47:14 UTC
svn commit: r931189 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/types/SQLBinary.java
testing/org/apache/derbyTesting/perf/basic/jdbc/BaseLoad100TestSetup.java
testing/org/apache/derbyTesting/perf/basic/jdbc/HeapScan.java
Author: kahatlen
Date: Tue Apr 6 15:47:14 2010
New Revision: 931189
URL: http://svn.apache.org/viewvc?rev=931189&view=rev
Log:
DERBY-4608: Unnecessary conversion of binary values to strings in SQLBinary.compare()
Use isNull() instead of getString() to check if the values are null,
since we don't need the string representation of the values. Also add
a test case to HeapScan to demonstrate the performance impact.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBinary.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/BaseLoad100TestSetup.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/HeapScan.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBinary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBinary.java?rev=931189&r1=931188&r2=931189&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBinary.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLBinary.java Tue Apr 6 15:47:14 2010
@@ -575,8 +575,7 @@ abstract class SQLBinary
"Some fool passed in a "+ other.getClass().getName() + ", "
+ otherTypeFormatId + " to SQLBinary.compare()");
}
- String otherString = other.getString();
- if (this.getString() == null || otherString == null)
+ if (this.isNull() || other.isNull())
return unknownRV;
}
/* Do the comparison */
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/BaseLoad100TestSetup.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/BaseLoad100TestSetup.java?rev=931189&r1=931188&r2=931189&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/BaseLoad100TestSetup.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/BaseLoad100TestSetup.java Tue Apr 6 15:47:14 2010
@@ -20,6 +20,7 @@
*/
package org.apache.derbyTesting.perf.basic.jdbc;
+import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
@@ -37,6 +38,9 @@ import org.apache.derbyTesting.junit.Cle
* column 5 has unique values, column 6 is set to a constant value,
* column 7 has 1% of rows with known pattern,
* column 8 has constant value,column 9 has values having the same suffix.
+ * One of the constructors allows the data type of the last four columns to be
+ * changed to CHAR(20) FOR BIT DATA.
+ *
* Connection props : autocommit - false, default isolation level- Read Committed.
*
* If any indexes have to be created or any other schema additions need to be made, then
@@ -46,6 +50,7 @@ public class BaseLoad100TestSetup extend
protected int rowsToLoad=10000;
protected String tableName = "BASELOAD100";
+ private boolean binaryData;
/**
*
@@ -82,9 +87,21 @@ public class BaseLoad100TestSetup extend
*/
public BaseLoad100TestSetup(Test test,int rowsToLoad, String tableName)
{
+ this(test, rowsToLoad, tableName, false);
+ }
+
+ /**
+ * @param test name of test
+ * @param rowsToLoad number of rows to insert
+ * @param tableName name of the table to insert the rows into
+ * @param binaryData whether or not c6, ..., c9 should contain binary data
+ */
+ public BaseLoad100TestSetup(
+ Test test, int rowsToLoad, String tableName, boolean binaryData) {
super(test);
this.tableName = tableName;
this.rowsToLoad = rowsToLoad;
+ this.binaryData = binaryData;
}
/**
@@ -112,10 +129,17 @@ public class BaseLoad100TestSetup extend
protected void decorateSQL(Statement s)
throws SQLException
{
- s.execute("CREATE TABLE " +tableName+" ("
- + "i1 INT, i2 INT, i3 INT, i4 INT, i5 INT, "
- + "c6 CHAR(20), c7 CHAR(20), c8 CHAR(20), c9 CHAR(20))");
-
+ StringBuffer ddl = new StringBuffer();
+ ddl.append("CREATE TABLE ").append(tableName);
+ ddl.append("(i1 INT, i2 INT, i3 INT, i4 INT, i5 INT");
+ for (int i = 6; i <= 9; i++) {
+ ddl.append(", c").append(i).append(" CHAR(20)");
+ if (binaryData) {
+ ddl.append(" FOR BIT DATA");
+ }
+ }
+ ddl.append(')');
+ s.execute(ddl.toString());
}
/**
@@ -137,20 +161,36 @@ public class BaseLoad100TestSetup extend
insert.setInt(4, 2);
insert.setInt(5, i);
- insert.setString(6, "01234567890123456789");
+ insert.setObject(6, convertData("01234567890123456789"));
// 1% of rows with a known pattern for like etc.
if ((i % 100) == 34)
- insert.setString(7, "012345javaone6789");
+ insert.setObject(7, convertData("012345javaone6789"));
else
- insert.setString(7, "01234567890123456789");
+ insert.setObject(7, convertData("01234567890123456789"));
- insert.setString(8, "01234567890123456789");
+ insert.setObject(8, convertData("01234567890123456789"));
- insert.setString(9, (i + 1000) + "0123456789012");
+ insert.setObject(9, convertData((i + 1000) + "0123456789012"));
insert.executeUpdate();
}
insert.getConnection().commit();
}
+ /**
+ * Convert a string to a data type appropriate for the columns c6 to c9,
+ * that is, either a {@code String} value or a {@code byte[]} value.
+ *
+ * @param string the string to generate the value from
+ * @return either {@code string}, or a {@code byte[]} value representing
+ * {@code string} if {@code binaryData} is {@code true}
+ * @throws IOException if the string cannot be converted to a byte array
+ */
+ private Object convertData(String string) throws IOException {
+ if (binaryData) {
+ return string.getBytes("US-ASCII");
+ } else {
+ return string;
+ }
+ }
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/HeapScan.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/HeapScan.java?rev=931189&r1=931188&r2=931189&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/HeapScan.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/HeapScan.java Tue Apr 6 15:47:14 2010
@@ -33,20 +33,43 @@ import org.apache.derbyTesting.junit.JDB
public class HeapScan extends JDBCPerfTestCase {
PreparedStatement select = null;
+ private PreparedStatement selectWithPred;
protected static String tableName = "SCANTEST";
protected static int rowcount = 10000;
+ private boolean binaryData;
/**
* @return suite of tests
*/
public static Test suite()
{
+ TestSuite suite = new TestSuite("HeapScanTests");
+ suite.addTest(baseSuite("HeapScan:CHAR", false));
+ suite.addTest(baseSuite("HeapScan:BINARY", true));
+ return suite;
+ }
+
+ /**
+ * Create a suite of all the tests in this class with the appropriate
+ * decorator.
+ *
+ * @param name the name of the returned test suite
+ * @param binaryData whether or not these tests should use binary data
+ * instead of character data
+ * @return a test suite
+ */
+ private static Test baseSuite(String name, boolean binaryData) {
int iterations = 700, repeats = 4;
- TestSuite heapScan = new TestSuite("HeapScanTests");
- heapScan.addTest(new HeapScan("Scan100",iterations,repeats));
- heapScan.addTest(new HeapScan("Scan100GetData",iterations,repeats));
- return new BaseLoad100TestSetup(heapScan,rowcount,tableName);
+ TestSuite heapScan = new TestSuite(name);
+ heapScan.addTest(new HeapScan("Scan100", binaryData,
+ iterations, repeats));
+ heapScan.addTest(new HeapScan("Scan100GetData", binaryData,
+ iterations, repeats));
+ heapScan.addTest(new HeapScan("Scan100WithPredicate", binaryData,
+ iterations, repeats));
+ return new BaseLoad100TestSetup(
+ heapScan, rowcount, tableName, binaryData);
}
/**
@@ -57,7 +80,22 @@ public class HeapScan extends JDBCPerfTe
*/
public HeapScan(String name,int iterations, int repeats)
{
+ this(name, false, iterations, repeats);
+ }
+
+ /**
+ * Scan tests.
+ * @param name test name
+ * @param binaryData whether or not binary data should be used instead
+ * of character data
+ * @param iterations iterations of the test to measure
+ * @param repeats number of times to repeat the test
+ */
+ public HeapScan(String name, boolean binaryData,
+ int iterations, int repeats)
+ {
super(name,iterations,repeats);
+ this.binaryData = binaryData;
}
/**
@@ -66,6 +104,20 @@ public class HeapScan extends JDBCPerfTe
public void setUp() throws Exception {
select = openDefaultConnection().prepareStatement("SELECT * FROM "+tableName);
+
+ // Create a SELECT statement that uses predicates. Also initialize
+ // the predicates with some data of the correct type for this test
+ // (either character data or binary data).
+ selectWithPred = prepareStatement(
+ "SELECT * FROM " + tableName + " WHERE " +
+ "c6=? OR c7=? OR c8=? OR c9=?");
+ Object predicate = "abcdef";
+ if (binaryData) {
+ predicate = ((String) predicate).getBytes("US-ASCII");
+ }
+ for (int i = 1; i <= 4; i++) {
+ selectWithPred.setObject(i, predicate);
+ }
}
@@ -117,10 +169,10 @@ public class HeapScan extends JDBCPerfTe
int i4 = rs.getInt(4);
int i5 = rs.getInt(5);
- String c6 = rs.getString(6);
- String c7 = rs.getString(7);
- String c8 = rs.getString(8);
- String c9 = rs.getString(9);
+ Object c6 = rs.getObject(6);
+ Object c7 = rs.getObject(7);
+ Object c8 = rs.getObject(8);
+ Object c9 = rs.getObject(9);
actualCount++;
}
@@ -130,12 +182,25 @@ public class HeapScan extends JDBCPerfTe
}
/**
+ * Test the performance of a table scan that needs to compare all the
+ * char values in the table with some specified values. Used to test the
+ * performance gains in DERBY-4608.
+ */
+ public void Scan100WithPredicate() throws SQLException {
+ ResultSet rs = selectWithPred.executeQuery();
+ assertFalse("should be empty", rs.next());
+ rs.close();
+ commit();
+ }
+
+ /**
* Cleanup - close resources opened in this test.
**/
public void tearDown() throws Exception {
select.close();
select = null;
+ selectWithPred = null; // will be closed in super.tearDown()
super.tearDown();
}
}