java/testing/org/apache/derbyTesting/perf/basic/jdbc/IndexScanTest.java - derby - Git at Google

 /*
  * Derby - Class org.apache.derbyTesting.perf.basic.jdbc.IndexScanTest
  *
  * 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.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Random;
 import junit.framework.Test;
 import org.apache.derbyTesting.junit.BaseTestSuite;
 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
 import org.apache.derbyTesting.junit.JDBCPerfTestCase;

 /**
  * Test the performance of different kinds of index scans. This test was
  * written in order to test the performance impact of the changes made in
  * DERBY-2991.
  */
 public class IndexScanTest extends JDBCPerfTestCase {

     /** The prepared statement for the SQL expression that we want to test. */
     private PreparedStatement preparedStmt;

     /**
      * Create an instance of the test.
      * @param name the name of the method to call in the test
      * @param iterations the number of iteration in each test run
      * @param repeats the number of times each test run is repeated
      */
     private IndexScanTest(String name, int iterations, int repeats) {
         super(name, iterations, repeats);
     }

     /**
      * Get the prepared statement that we use in the test. If this is the
      * first time the method is called on this object, prepare the statement
      * and then return it.
      * @param sql the SQL text to prepare if the statement isn't already
      *            compiled
      * @return the prepared statement to use in this test
      */
     private PreparedStatement getOrPrepareStatement(String sql)
             throws SQLException {
         if (preparedStmt == null) {
             preparedStmt = prepareStatement(sql);
         }
         return preparedStmt;
     }

     /**
      * Do the necessary clean-up after running the test.
      */
     protected void tearDown() throws Exception {
         super.tearDown();
         // Null out the statement to allow it to be gc'ed. It will be closed
         // automatically by the framework.
         preparedStmt = null;
     }

     /**
      * Create a test suite with all the test cases in this class.
      * @return a test suite
      */
     public static Test suite() {
         BaseTestSuite suite = new BaseTestSuite("IndexScanTest");
         suite.addTest(new IndexScanTest("varchar10", 5000, 4));
         suite.addTest(new IndexScanTest("varchar100", 5000, 4));
         suite.addTest(new IndexScanTest("varchar1000", 5000, 4));
         suite.addTest(new IndexScanTest("varcharAll", 5000, 4));
         suite.addTest(new IndexScanTest("decimal1column", 5000, 4));
         suite.addTest(new IndexScanTest("decimal10columns", 5000, 4));

         return new CleanDatabaseTestSetup(suite) {
             protected void decorateSQL(Statement s) throws SQLException {
                 // Create a table with some character data and decimal data
                 // that we can use in our tests.
                 s.execute("CREATE TABLE T (VC10 VARCHAR(10), " +
                         "VC100 VARCHAR(100), " +
                         "VC1000 VARCHAR(1000), " +
                         "DEC1 DECIMAL(10,10), " +
                         "DEC2 DECIMAL(10,10), " +
                         "DEC3 DECIMAL(10,10), " +
                         "DEC4 DECIMAL(10,10), " +
                         "DEC5 DECIMAL(10,10), " +
                         "DEC6 DECIMAL(10,10), " +
                         "DEC7 DECIMAL(10,10), " +
                         "DEC8 DECIMAL(10,10), " +
                         "DEC9 DECIMAL(10,10), " +
                         "DEC10 DECIMAL(10,10))");

                 // Fill the table with 1000 rows containing random data.
                 PreparedStatement ps = s.getConnection().prepareStatement(
                         "INSERT INTO T(VC10,VC100,VC1000,DEC1,DEC2,DEC3," +
                         "DEC4,DEC5,DEC6,DEC7,DEC8,DEC9,DEC10) VALUES (?,?,?," +
                         "RANDOM(),RANDOM(),RANDOM(),RANDOM(),RANDOM()," +
                         "RANDOM(),RANDOM(),RANDOM(),RANDOM(),RANDOM())");
                 char[] chars = new char[1000];
                 Random r = new Random();
                 for (int i = 0; i < 1000; i++) {
                     fillWithRandomChars(r, chars);
                     ps.setString(1, new String(chars, 0, 10));
                     ps.setString(2, new String(chars, 0, 100));
                     ps.setString(3, new String(chars, 0, 1000));
                     ps.executeUpdate();
                 }
                 ps.close();

                 // Create various indexes on the table.
                 s.execute("CREATE INDEX T_VC10 ON T(VC10)");
                 s.execute("CREATE INDEX T_VC100 ON T(VC100)");
                 s.execute("CREATE INDEX T_VC1000 ON T(VC1000)");
                 s.execute("CREATE INDEX T_VC_ALL ON T(VC10,VC100,VC1000)");
                 s.execute("CREATE INDEX T_DEC1 ON T(DEC1)");
                 s.execute("CREATE INDEX T_DEC_ALL ON T(DEC1,DEC2,DEC3,DEC4," +
                         "DEC5,DEC6,DEC7,DEC8,DEC9,DEC10)");
             }
         };
     }

     /**
      * Fill a {@code char} array with random characters.
      * @param r a random number generator
      * @param chars the array to fill
      */
     private static void fillWithRandomChars(Random r, char[] chars) {
         String alphabet =
             "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
         for (int i = 0; i < chars.length; i++) {
             chars[i] = alphabet.charAt(r.nextInt(alphabet.length()));
         }
     }

     /**
      * Test the performance of an index scan on a VARCHAR(10) column.
      */
     public void varchar10() throws SQLException {
         PreparedStatement ps = getOrPrepareStatement(
                 "SELECT VC10 FROM T --DERBY-PROPERTIES index=T_VC10");
         ResultSet rs = ps.executeQuery();
         while (rs.next()) {
             assertNotNull(rs.getString(1));
         }
         rs.close();
     }

     /**
      * Test the performance of an index scan on a VARCHAR(100) column.
      */
     public void varchar100() throws SQLException {
         PreparedStatement ps = getOrPrepareStatement(
                 "SELECT VC100 FROM T --DERBY-PROPERTIES index=T_VC100");
         ResultSet rs = ps.executeQuery();
         while (rs.next()) {
             assertNotNull(rs.getString(1));
         }
         rs.close();
     }

     /**
      * Test the performance of an index scan on a VARCHAR(1000) column.
      */
     public void varchar1000() throws SQLException {
         PreparedStatement ps = getOrPrepareStatement(
                 "SELECT VC1000 FROM T --DERBY-PROPERTIES index=T_VC1000");
         ResultSet rs = ps.executeQuery();
         while (rs.next()) {
             assertNotNull(rs.getString(1));
         }
         rs.close();
     }

     /**
      * Test the performance of an index scan with a compound index on
      * columns with type VARCHAR(10), VARCHAR(100) and VARCHAR(1000).
      */
     public void varcharAll() throws SQLException {
         PreparedStatement ps = getOrPrepareStatement(
           "SELECT VC10,VC100,VC1000 FROM T --DERBY-PROPERTIES index=T_VC_ALL");
         ResultSet rs = ps.executeQuery();
         while (rs.next()) {
             for (int col = 1; col <= 3; col++) {
                 assertNotNull(rs.getString(col));
             }
         }
         rs.close();
     }

     /**
      * Test the performance of an index scan on a DECIMAL(10,10) column.
      */
     public void decimal1column() throws SQLException {
         PreparedStatement ps = getOrPrepareStatement(
                 "SELECT DEC1 FROM T --DERBY-PROPERTIES index=T_DEC1");
         ResultSet rs = ps.executeQuery();
         while (rs.next()) {
             assertNotNull(rs.getBigDecimal(1));
         }
         rs.close();
     }

     /**
      * Test the performance of an index scan on a compound index on ten
      * DECIMAL(10,10) columns.
      */
     public void decimal10columns() throws SQLException {
         PreparedStatement ps = getOrPrepareStatement(
                 "SELECT DEC1,DEC2,DEC3,DEC4,DEC5,DEC6,DEC7,DEC8,DEC9,DEC10 " +
                 "FROM T --DERBY-PROPERTIES index=T_DEC_ALL");
         ResultSet rs = ps.executeQuery();
         while (rs.next()) {
             for (int col = 1; col <= 10; col++) {
                 assertNotNull(rs.getBigDecimal(col));
             }
         }
         rs.close();
     }
 }
	/*
	* Derby - Class org.apache.derbyTesting.perf.basic.jdbc.IndexScanTest
	*
	* 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.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.Random;
	import junit.framework.Test;
	import org.apache.derbyTesting.junit.BaseTestSuite;
	import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
	import org.apache.derbyTesting.junit.JDBCPerfTestCase;

	/**
	* Test the performance of different kinds of index scans. This test was
	* written in order to test the performance impact of the changes made in
	* DERBY-2991.
	*/
	public class IndexScanTest extends JDBCPerfTestCase {

	/** The prepared statement for the SQL expression that we want to test. */
	private PreparedStatement preparedStmt;

	/**
	* Create an instance of the test.
	* @param name the name of the method to call in the test
	* @param iterations the number of iteration in each test run
	* @param repeats the number of times each test run is repeated
	*/
	private IndexScanTest(String name, int iterations, int repeats) {
	super(name, iterations, repeats);
	}

	/**
	* Get the prepared statement that we use in the test. If this is the
	* first time the method is called on this object, prepare the statement
	* and then return it.
	* @param sql the SQL text to prepare if the statement isn't already
	* compiled
	* @return the prepared statement to use in this test
	*/
	private PreparedStatement getOrPrepareStatement(String sql)
	throws SQLException {
	if (preparedStmt == null) {
	preparedStmt = prepareStatement(sql);
	}
	return preparedStmt;
	}

	/**
	* Do the necessary clean-up after running the test.
	*/
	protected void tearDown() throws Exception {
	super.tearDown();
	// Null out the statement to allow it to be gc'ed. It will be closed
	// automatically by the framework.
	preparedStmt = null;
	}

	/**
	* Create a test suite with all the test cases in this class.
	* @return a test suite
	*/
	public static Test suite() {
	BaseTestSuite suite = new BaseTestSuite("IndexScanTest");
	suite.addTest(new IndexScanTest("varchar10", 5000, 4));
	suite.addTest(new IndexScanTest("varchar100", 5000, 4));
	suite.addTest(new IndexScanTest("varchar1000", 5000, 4));
	suite.addTest(new IndexScanTest("varcharAll", 5000, 4));
	suite.addTest(new IndexScanTest("decimal1column", 5000, 4));
	suite.addTest(new IndexScanTest("decimal10columns", 5000, 4));

	return new CleanDatabaseTestSetup(suite) {
	protected void decorateSQL(Statement s) throws SQLException {
	// Create a table with some character data and decimal data
	// that we can use in our tests.
	s.execute("CREATE TABLE T (VC10 VARCHAR(10), " +
	"VC100 VARCHAR(100), " +
	"VC1000 VARCHAR(1000), " +
	"DEC1 DECIMAL(10,10), " +
	"DEC2 DECIMAL(10,10), " +
	"DEC3 DECIMAL(10,10), " +
	"DEC4 DECIMAL(10,10), " +
	"DEC5 DECIMAL(10,10), " +
	"DEC6 DECIMAL(10,10), " +
	"DEC7 DECIMAL(10,10), " +
	"DEC8 DECIMAL(10,10), " +
	"DEC9 DECIMAL(10,10), " +
	"DEC10 DECIMAL(10,10))");

	// Fill the table with 1000 rows containing random data.
	PreparedStatement ps = s.getConnection().prepareStatement(
	"INSERT INTO T(VC10,VC100,VC1000,DEC1,DEC2,DEC3," +
	"DEC4,DEC5,DEC6,DEC7,DEC8,DEC9,DEC10) VALUES (?,?,?," +
	"RANDOM(),RANDOM(),RANDOM(),RANDOM(),RANDOM()," +
	"RANDOM(),RANDOM(),RANDOM(),RANDOM(),RANDOM())");
	char[] chars = new char[1000];
	Random r = new Random();
	for (int i = 0; i < 1000; i++) {
	fillWithRandomChars(r, chars);
	ps.setString(1, new String(chars, 0, 10));
	ps.setString(2, new String(chars, 0, 100));
	ps.setString(3, new String(chars, 0, 1000));
	ps.executeUpdate();
	}
	ps.close();

	// Create various indexes on the table.
	s.execute("CREATE INDEX T_VC10 ON T(VC10)");
	s.execute("CREATE INDEX T_VC100 ON T(VC100)");
	s.execute("CREATE INDEX T_VC1000 ON T(VC1000)");
	s.execute("CREATE INDEX T_VC_ALL ON T(VC10,VC100,VC1000)");
	s.execute("CREATE INDEX T_DEC1 ON T(DEC1)");
	s.execute("CREATE INDEX T_DEC_ALL ON T(DEC1,DEC2,DEC3,DEC4," +
	"DEC5,DEC6,DEC7,DEC8,DEC9,DEC10)");
	}
	};
	}

	/**
	* Fill a {@code char} array with random characters.
	* @param r a random number generator
	* @param chars the array to fill
	*/
	private static void fillWithRandomChars(Random r, char[] chars) {
	String alphabet =
	"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
	for (int i = 0; i < chars.length; i++) {
	chars[i] = alphabet.charAt(r.nextInt(alphabet.length()));
	}
	}

	/**
	* Test the performance of an index scan on a VARCHAR(10) column.
	*/
	public void varchar10() throws SQLException {
	PreparedStatement ps = getOrPrepareStatement(
	"SELECT VC10 FROM T --DERBY-PROPERTIES index=T_VC10");
	ResultSet rs = ps.executeQuery();
	while (rs.next()) {
	assertNotNull(rs.getString(1));
	}
	rs.close();
	}

	/**
	* Test the performance of an index scan on a VARCHAR(100) column.
	*/
	public void varchar100() throws SQLException {
	PreparedStatement ps = getOrPrepareStatement(
	"SELECT VC100 FROM T --DERBY-PROPERTIES index=T_VC100");
	ResultSet rs = ps.executeQuery();
	while (rs.next()) {
	assertNotNull(rs.getString(1));
	}
	rs.close();
	}

	/**
	* Test the performance of an index scan on a VARCHAR(1000) column.
	*/
	public void varchar1000() throws SQLException {
	PreparedStatement ps = getOrPrepareStatement(
	"SELECT VC1000 FROM T --DERBY-PROPERTIES index=T_VC1000");
	ResultSet rs = ps.executeQuery();
	while (rs.next()) {
	assertNotNull(rs.getString(1));
	}
	rs.close();
	}

	/**
	* Test the performance of an index scan with a compound index on
	* columns with type VARCHAR(10), VARCHAR(100) and VARCHAR(1000).
	*/
	public void varcharAll() throws SQLException {
	PreparedStatement ps = getOrPrepareStatement(
	"SELECT VC10,VC100,VC1000 FROM T --DERBY-PROPERTIES index=T_VC_ALL");
	ResultSet rs = ps.executeQuery();
	while (rs.next()) {
	for (int col = 1; col <= 3; col++) {
	assertNotNull(rs.getString(col));
	}
	}
	rs.close();
	}

	/**
	* Test the performance of an index scan on a DECIMAL(10,10) column.
	*/
	public void decimal1column() throws SQLException {
	PreparedStatement ps = getOrPrepareStatement(
	"SELECT DEC1 FROM T --DERBY-PROPERTIES index=T_DEC1");
	ResultSet rs = ps.executeQuery();
	while (rs.next()) {
	assertNotNull(rs.getBigDecimal(1));
	}
	rs.close();
	}

	/**
	* Test the performance of an index scan on a compound index on ten
	* DECIMAL(10,10) columns.
	*/
	public void decimal10columns() throws SQLException {
	PreparedStatement ps = getOrPrepareStatement(
	"SELECT DEC1,DEC2,DEC3,DEC4,DEC5,DEC6,DEC7,DEC8,DEC9,DEC10 " +
	"FROM T --DERBY-PROPERTIES index=T_DEC_ALL");
	ResultSet rs = ps.executeQuery();
	while (rs.next()) {
	for (int col = 1; col <= 10; col++) {
	assertNotNull(rs.getBigDecimal(col));
	}
	}
	rs.close();
	}
	}