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 2009/01/02 11:22:04 UTC
svn commit: r730689 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic:
_Suite.java jdbc/SelectDistinctTest.java
Author: kahatlen
Date: Fri Jan 2 02:22:03 2009
New Revision: 730689
URL: http://svn.apache.org/viewvc?rev=730689&view=rev
Log:
DERBY-3981: Improve distribution of hash codes in SQLBinary and SQLChar
Added a performance test which shows the worst-case behaviour of the
current hash function. The test performs SELECT DISTINCT on a CHAR(5)
column and on a CHAR(5) FOR BIT DATA column. The current hash function
maps all the rows in the test to the same bucket and the hash table
used for elimination of duplicates becomes a linked list.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/SelectDistinctTest.java (with props)
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/_Suite.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/_Suite.java?rev=730689&r1=730688&r2=730689&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/_Suite.java Fri Jan 2 02:22:03 2009
@@ -41,6 +41,7 @@
suite.addTest(HeapScan.suite());
suite.addTest(CoveredIdxScan.suite());
suite.addTest(SortTest.suite());
+ suite.addTest(SelectDistinctTest.suite());
return suite;
}
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/SelectDistinctTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/SelectDistinctTest.java?rev=730689&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/SelectDistinctTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/SelectDistinctTest.java Fri Jan 2 02:22:03 2009
@@ -0,0 +1,161 @@
+/*
+ * Derby - Class org.apache.derbyTesting.perf.basic.jdbc.SelectDistinctTest
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
+ * either express or implied. See the License for the specific
+ * language governing permissions and limitations under the License.
+ */
+
+package org.apache.derbyTesting.perf.basic.jdbc;
+
+import java.io.UnsupportedEncodingException;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Statement;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.JDBCPerfTestCase;
+
+/**
+ * Performance tests for SELECT DISTINCT.
+ */
+public class SelectDistinctTest extends JDBCPerfTestCase {
+
+ /** The prepared statement to be used in the test body. */
+ private PreparedStatement ps;
+
+ /**
+ * Name of the test case for CHAR(5), and the name of the table used by
+ * that test.
+ */
+ private static final String TEST_CHAR5 = "selectDistinctChar5";
+
+ /**
+ * The number of distinct rows to be expected in the test for CHAR(5).
+ * Since the table contains all permutations of "ABCDE", there should be
+ * 5! distinct rows.
+ */
+ private static final int EXPECTED_DISTINCT_CHAR5 = 5 * 4 * 3 * 2;
+
+ /**
+ * Name of the test case for CHAR(5) FOR BIT DATA, and the name of the
+ * table used by that test.
+ */
+ private static final String TEST_BINARY5 = "selectDistinctBinary5";
+
+ /**
+ * The number of distinct rows to be expected in the test for CHAR(5) FOR
+ * BIT DATA. Should be the same as in the test for CHAR(5).
+ */
+ private static final int EXPECTED_DISTINCT_BINARY5 =
+ EXPECTED_DISTINCT_CHAR5;
+
+ /**
+ * Create a test case.
+ *
+ * @param name the name of the test method, and the name of the table used
+ * in the test
+ * @param iterations the number of iterations in each test
+ * @param repeats the number of times each test should be repeated
+ */
+ private SelectDistinctTest(String name, int iterations, int repeats) {
+ super(name, iterations, repeats);
+ }
+
+ /**
+ * Create the suite of all test cases in this class.
+ *
+ * @return all test cases in this class
+ */
+ public static Test suite() {
+ TestSuite suite = new TestSuite("SelectDistinctTest");
+ suite.addTest(new SelectDistinctTest(TEST_CHAR5, 2000, 4));
+ suite.addTest(new SelectDistinctTest(TEST_BINARY5, 2000, 4));
+
+ return new CleanDatabaseTestSetup(suite) {
+ protected void decorateSQL(Statement s) throws SQLException {
+ s.execute("CREATE TABLE " + TEST_CHAR5 + "(COL CHAR(5))");
+ // insert all permutations of 'ABCDE'
+ s.execute("INSERT INTO " + TEST_CHAR5 +
+ " SELECT V1.X||V2.X||V3.X||V4.X||V5.X FROM " +
+ "(VALUES 'A', 'B', 'C', 'D', 'E') V1(X), " +
+ "(VALUES 'A', 'B', 'C', 'D', 'E') V2(X), " +
+ "(VALUES 'A', 'B', 'C', 'D', 'E') V3(X), " +
+ "(VALUES 'A', 'B', 'C', 'D', 'E') V4(X), " +
+ "(VALUES 'A', 'B', 'C', 'D', 'E') V5(X) " +
+ "WHERE (SELECT COUNT(*) FROM " +
+ "(VALUES 'A', 'B', 'C', 'D', 'E') VV(X) " +
+ "WHERE VV.X NOT IN (V1.X,V2.X,V3.X,V4.X,V5.X)) = 0");
+ // make some duplicates
+ for (int i = 0; i < 3; i++) {
+ s.execute("INSERT INTO " + TEST_CHAR5 + " " +
+ "SELECT * FROM " + TEST_CHAR5);
+ }
+
+ s.execute("CREATE TABLE " + TEST_BINARY5 +
+ "(COL CHAR(5) FOR BIT DATA)");
+ s.execute("CREATE FUNCTION CHAR_TO_BINARY(STR CHAR(5)) " +
+ "RETURNS CHAR(5) FOR BIT DATA " +
+ "LANGUAGE JAVA PARAMETER STYLE JAVA " +
+ "EXTERNAL NAME '" + SelectDistinctTest.class.getName() +
+ ".charToBinary' NO SQL");
+ s.execute("INSERT INTO " + TEST_BINARY5 +
+ " SELECT CHAR_TO_BINARY(COL) FROM " + TEST_CHAR5);
+ }
+ };
+ }
+
+ /**
+ * Set up the test environment.
+ */
+ protected void setUp() throws SQLException {
+ ps = prepareStatement("SELECT DISTINCT * FROM " + getName());
+ }
+
+ /**
+ * Tear down the test environment.
+ */
+ protected void tearDown() throws Exception {
+ super.tearDown();
+ ps = null; // automatically closed in super.tearDown()
+ }
+
+ /**
+ * Stored function that converts an SQL CHAR value to an SQL CHAR FOR BIT
+ * DATA value.
+ *
+ * @param s the string to convert to binary data
+ * @return binary UTF-8 representation of the string
+ */
+ public static byte[] charToBinary(String s)
+ throws UnsupportedEncodingException {
+ return s.getBytes("UTF-8");
+ }
+
+ /**
+ * Test case for SELECT DISTINCT on a CHAR(5) column.
+ */
+ public void selectDistinctChar5() throws SQLException {
+ JDBC.assertDrainResults(ps.executeQuery(), EXPECTED_DISTINCT_CHAR5);
+ }
+
+ /**
+ * Test case for SELECT DISTINCT on a CHAR(5) FOR BIT DATA column.
+ */
+ public void selectDistinctBinary5() throws SQLException {
+ JDBC.assertDrainResults(ps.executeQuery(), EXPECTED_DISTINCT_BINARY5);
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/basic/jdbc/SelectDistinctTest.java
------------------------------------------------------------------------------
svn:eol-style = native