Benchmark Sql

A open-source sql benchmark

https://sourceforge.net/projects/benchmarksql/

Usage

config file for all-in-one install.

oracle.properties

driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@localhost:1521:XE
user=bmuser
password=bmuser

bm_config.bat

set SYSTEM_PASSWD=manager
set BM_TNSNAME=orcl
set BM_USER=bmuser
set BM_PASSWD=bmuser
set BM_TBS_DAT=USERS
set BM_TBS_IDX=USERS

RunTestSuite.bat all-in-one install.

REM 
REM Instructions for building
REM -------------------------
REM 
REM Use of JDK 1.5 is recommended, build with "ant jar" from the command line of 
REM the base directory or use your favorite IDE such as Netbeans or Eclipse.  
REM 
REM 
REM Instructions for running
REM ------------------------
REM The below scripts all use relative paths, but, they depend on JAVA_HOME
REM environment varibale being set so that the correct runtime can be found.
REM 
REM JDBC drivers and sample "?.properties" files are included to make it extremely easy for
REM you to test out the performance of EnterpriseDB, PostgreSQL, MySQL, Oracle, & SQL Svr
REM in your environment.
REM 
REM 
 
REM 0. Create user, tablespace. Configure bm_config.bat before execute this script.
REM In this case, create tablespace %BM_TBS_DAT%, %BM_TBS_IDX% and user %BM_USER%, then make the default tablespace to %BM_TBS_DAT%
 
call bm_config.bat
echo create user %BM_USER% identified by %BM_PASSWD% default tablespace %BM_TBS_DAT%; | sqlplus system/%SYSTEM_PASSWD%@%BM_TNSNAME%
 
REM 1. Go to the 'run/scripts' directory, edit the appropriate "??????.properties" file to 
REM    point to the database instance you'd like to test.   Of course you'll substitute in
REM    the name of your appropriate config file in the command lines below.
REM 
REM 
 
REM 2. Run the "sqlTableCreates" to create the base tables.
REM 
REM       -  runSQL EnterpriseDB.properties sqlTableCreates
REM 
REM    Note:  "sqlTableCreates" will truncate all the tables so you can start over clean.
REM           There is also a "sqlTableDrops" script if you need it.
REM 
REM 
 
call runSQL oracle.properties sqlTableCreates
 
REM 3. Run the "loadData" command file to load all of the default data for a benchmark:
REM 
REM 
REM   A.) Approximately half a million rows in total will be loaded across 9 tables
REM       per Warehouse.  (The default is numWarehouses=1)  A decent test size of data
REM       totaling about 1 GB is 10 warehouses as follows:
REM         $ loadData EnterpriseDB.properties numWarehouses=10
REM 
 
call loadData oracle.properties numWarehouses=10
 
REM   B.) Alternatively, you may choose to generate test out to CSV files that can be 
REM       bulk loaded as follows:
REM         $ loadData EnterpriseDB.properties numWarehouses=10 fileLocation=c:/temp/
REM 
REM       These CSV files can be bulk loaded into EDB-Postgres via the following:
REM         $  runSQL EnterpriseDB.properties sqlTableCopies
REM 
REM       You may clean out the data in the tables without dropping them via:
REM         $  runSQL EnterpriseDB.properties sqlTableTruncates
REM 
REM 
 
REM 4. Run the "runSQL" command file to execute the SQL script "sqlIndexCreates" to 
REM    create the primary keys & other indexes on the tables.
REM 
REM       -  runSQL EnterpriseDB.properties sqlIndexCreates
REM 
REM 
 
echo alter user %BM_USER% default tablespace %BM_TBS_IDX%; | sqlplus system/%SYSTEM_PASSWD%@%BM_TNSNAME%
 
call runSQL oracle.properties sqlIndexCreates
 
echo alter user %BM_USER% default tablespace %BM_TBS_DAT%; | sqlplus system/%SYSTEM_PASSWD%@%BM_TNSNAME%
 
echo execute dbms_stats.gather_schema_stats('%BM_USER%', 100); | sqlplus system/%SYSTEM_PASSWD%@%BM_TNSNAME%
 
REM 5. Run the "runBenchmark" command file to execute the swing GUI application to 
REM    test the database.  Don't forget to set the number of warehouses equal to the
REM    number you created in step 3
REM 
REM       -  runBenchmark EnterpriseDB.properties
 
start runBenchmark oracle.properties