TPC-H is an ad-hoc, decision support benchmark.
It consists of a suite of business oriented ad-hoc queries and concurrent data modifications.
The TPC-H benchmark models the analysis end of the business environment where trends are computed and refined data are produced to support the making of sound business decisions.
This benchmark illustrates decision support systems that
To achieve the optimal compromise between performance and operational requirements, the database administrator can set, once and for all:
for queries and refresh functions.
TPC-H does not represent the activity of any particular business segment, but rather any industry which must manage sell, or distribute a product worldwide (e.g., car rental, food distribution, parts, suppliers, etc.).
The TPC believes that comparisons of TPC-H results measured against different database sizes are misleading and discourages such comparisons.
The only benchmark results comparable to TPC-H are other TPC-H results compliant with the same revision.
Benchmark results are highly dependent upon workload, specific application requirements, and systems design and implementation.
Relative system performance will vary as a result of these and other factors. Therefore, TPC-H should not be used as a substitute for a specific customer application benchmarking when critical capacity planning and/or product evaluation decisions are contemplated.
Partitioning schemes rules. They:
Partition schemes key rules:
The performance metric reported by TPC-H is called the TPC-H Composite Query-per-Hour Performance Metric (QphH@Size), and reflects multiple aspects of the capability of the system to process queries including:
The TPC-H Price/Performance metric is expressed as $/QphH@Size.
The tpc-h leverages:
A mix of queries and refresh functions is subject to specific ACIDity requirements, since queries and refresh functions may execute concurrently;
These queries have been given a realistic context, portraying the activity of a wiki/wholesale supplier.
The queries that have been selected exhibit the following characteristics:
These selected queries provide answers to the following classes of business analysis:
QGEN is a utility provided by the TPC to generate executable query text with substitution parameters. The substitution parameters are created randomly. If a tool is created instead of QGEN, the behavior of its seeds must be satisfied.
Their purpose of the refresh function is to demonstrate the update functionality for the DBMS, while simultaneously assessing an appropriate performance cost to the maintenance of auxiliary data structures, such as secondary indices.
where:
The following datatype definitions apply to the list of columns of each table:
All the data type by column are defined in the documentation. A default SQL schema file dss.ddl is also available in the dbgen dir of the toolkit.
The use of constraints is optional and limited to primary key, foreign key, check, and not null constraints.
ANSI C++ Compiler
Download and Install Microsoft Visual C++ Express
################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC = CL
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
# SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
DATABASE= ORACLE
MACHINE = WIN32
WORKLOAD = TPCH
#
CFLAGS = /DEBUG /DDBNAME=\"dss\" /D$(MACHINE) /D$(DATABASE) /D$(WORKLOAD) /DRNG_TEST /D_FILE_OFFSET_BITS=64
LDFLAGS = /O2
# The OBJ,EXE and LIB macros will need to be changed for compilation under
# Windows NT
OBJ = .obj
EXE = .EXE
LIBS =
where:
SET PATH=%PATH%;C:\Program Files\Microsoft Visual Studio 10.0\VC\bin\
echo %LIB%
%LIB%
vcvars32.bat
Setting environment for using Microsoft Visual Studio 2010 x86 tools.
echo %LIB%
C:\Program Files\Microsoft Visual Studio 10.0\VC\LIB;C:\Program Files\Microsoft SDKs\Windows\v7.0A\lib;
cd C:\Users\gerard\Desktop\tpc\tpch_2_14_3\dbgen
nmake /f makefile
Microsoft (R) Program Maintenance Utility Version 10.00.30319.01
Copyright (C) Microsoft Corporation. All rights reserved.
CL /DEBUG /DDBNAME=\"dss\" /DWIN32 /DORACLE /DTPCH /DRNG_TEST /D_FILE_OFFSET_BITS=64 /c build.c driver.c bm_util
s.c rnd.c print.c load_stub.c bcd2.c speed_seed.c text.c permute.c rng64.c
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.30319.01 for 80x86
Copyright (C) Microsoft Corporation. All rights reserved.
build.c
driver.c
bm_utils.c
rnd.c
print.c
load_stub.c
bcd2.c
speed_seed.c
text.c
permute.c
rng64.c
Generating Code...
CL /DEBUG /DDBNAME=\"dss\" /DWIN32 /DORACLE /DTPCH /DRNG_TEST /D_FILE_OFFSET_BITS=64 /O2 -o dbgen.EXE build.obj
driver.obj bm_utils.obj rnd.obj print.obj load_stub.obj bcd2.obj speed_seed.obj text.obj permute.obj rng64.obj
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.30319.01 for 80x86
Copyright (C) Microsoft Corporation. All rights reserved.
cl : Command line warning D9035 : option 'o' has been deprecated and will be removed in a future release
Microsoft (R) Incremental Linker Version 10.00.30319.01
Copyright (C) Microsoft Corporation. All rights reserved.
/out:build.exe
/out:dbgen.EXE
build.obj
driver.obj
bm_utils.obj
rnd.obj
print.obj
load_stub.obj
bcd2.obj
speed_seed.obj
text.obj
permute.obj
rng64.obj
CL /DEBUG /DDBNAME=\"dss\" /DWIN32 /DORACLE /DTPCH /DRNG_TEST /D_FILE_OFFSET_BITS=64 /c qgen.c varsub.c
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.30319.01 for 80x86
Copyright (C) Microsoft Corporation. All rights reserved.
qgen.c
varsub.c
Generating Code...
CL /DEBUG /DDBNAME=\"dss\" /DWIN32 /DORACLE /DTPCH /DRNG_TEST /D_FILE_OFFSET_BITS=64 /O2 -o qgen.EXE build.obj b
m_utils.obj qgen.obj rnd.obj varsub.obj text.obj bcd2.obj permute.obj speed_seed.obj rng64.obj
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.30319.01 for 80x86
Copyright (C) Microsoft Corporation. All rights reserved.
cl : Command line warning D9035 : option 'o' has been deprecated and will be removed in a future release
Microsoft (R) Incremental Linker Version 10.00.30319.01
Copyright (C) Microsoft Corporation. All rights reserved.
/out:build.exe
/out:qgen.EXE
build.obj
bm_utils.obj
qgen.obj
rnd.obj
varsub.obj
text.obj
bcd2.obj
permute.obj
speed_seed.obj
rng64.obj
################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC = GCC
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
# SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
DATABASE= ORACLE
MACHINE = LINUX
WORKLOAD = TPCH
#
CFLAGS = -g -DDBNAME=\"dss\" -D$(MACHINE) -D$(DATABASE) -D$(WORKLOAD) -DRNG_TEST -D_FILE_OFFSET_BITS=64
LDFLAGS = -O
# The OBJ,EXE and LIB macros will need to be changed for compilation under
# Windows NT
OBJ = .o
EXE =
LIBS = -lm
where the following GCC Options:
make
dbgen.exe -h
TPC-H Population Generator (Version 2.14.3 build 0)
Copyright Transaction Processing Performance Council 1994 - 2010
USAGE:
dbgen [-{vf}][-T {pcsoPSOL}]
[-s <scale>][-C <procs>][-S <step>]
dbgen [-v] [-O m] [-s <scale>] [-U <updates>]
Basic Options
===========================
-C <n> -- separate data set into <n> chunks (requires -S, default: 1)
-f -- force. Overwrite existing files
-h -- display this message
-q -- enable QUIET mode
-s <n> -- set Scale Factor (SF) to <n> (default: 1)
-S <n> -- build the <n>th step of the data/update set (used with -C or -U)
-U <n> -- generate <n> update sets
-v -- enable VERBOSE mode
Advanced Options
===========================
-b <s> -- load distributions for <s> (default: dists.dss)
-d <n> -- split deletes between <n> files (requires -U)
-i <n> -- split inserts between <n> files (requires -U)
-T c -- generate cutomers ONLY
-T l -- generate nation/region ONLY
-T L -- generate lineitem ONLY
-T n -- generate nation ONLY
-T o -- generate orders/lineitem ONLY
-T O -- generate orders ONLY
-T p -- generate parts/partsupp ONLY
-T P -- generate parts ONLY
-T r -- generate region ONLY
-T s -- generate suppliers ONLY
-T S -- generate partsupp ONLY
To generate the SF=1 (1GB), validation database population, use:
dbgen -vf -s 1
To generate updates for a SF=1 (1GB), use:
dbgen -v -U 1 -s 1
qgen.EXE -h
TPC-H Parameter Substitution (v. 2.14.3 build 0)
Copyright Transaction Processing Performance Council 1994 - 2010
USAGE: qgen.EXE <options> [ queries ]
Options:
-a -- use ANSI semantics.
-b <str> -- load distributions from <str>
-c -- retain comments found in template.
-d -- use default substitution values.
-h -- print this usage summary.
-i <str> -- use the contents of file <str> to begin a query.
-l <str> -- log parameters to <str>.
-n <str> -- connect to database <str>.
-N -- use default rowcounts and ignore :n directive.
-o <str> -- set the output file base path to <str>.
-p <n> -- use the query permutation for stream <n>
-r <n> -- seed the random number generator with <n>
-s <n> -- base substitutions on an SF of <n>
-v -- verbose.
-t <str> -- use the contents of file <str> to complete a query
-x -- enable SET EXPLAIN in each query.
Scale factors used for the test database must be chosen from the set of fixed scale factors defined as follows:
Scale factor (SF) | 1 | 10 | 30 | 100 | 300 | 1000 | 3000 | 10000 | 30000 | 100000 |
---|---|---|---|---|---|---|---|---|---|---|
Database sizes | 1GB | 10GB | 30GB | 100GB | 300GB | 1000GB | 3000GB | 10000GB | 30000GB | 100000GB |
where:
dbgen -vf -s 1
TPC-H Population Generator (Version 2.14.3)
Copyright Transaction Processing Performance Council 1994 - 2010
Generating data for suppliers table/
Preloading text ... 100%
done.
Generating data for customers tabledone.
Generating data for orders/lineitem tablesdone.
Generating data for part/partsupplier tablesdone.
Generating data for nation tabledone.
Generating data for region tabledone.
Java Port: https://github.com/airlift/tpch
CREATE USER TPC_H IDENTIFIED BY TPC_H
DEFAULT tablespace users
TEMPORARY tablespace temp
quota unlimited ON users;
GRANT CONNECT, resource, CREATE VIEW TO TPC_H;
.............
Loading table H_Lineitem ( 5997000 (99,95%) rows completed )
Loading table H_Lineitem ( 5998000 (99,97%) rows completed )
Completed loading table H_Lineitem.
Create Indexes
Test completed successfully.
** Job finished.
Total Time to perform the job:
Start Time: 5-4-2012 16:27:53
End Time : 5-4-2012 16:32:49
Elapsed : 0 Days 00:04:55.775
TPC-H Population Generator (Version 2.14.3)
Copyright Transaction Processing Performance Council 1994 - 2010
Open failed for .\dists.dss at c:\users\gerard\desktop\tpc\tpch_2_14_3\dbgen\bm_utils.c:308
By default, the generator use the b option and search the default file dists.dss. Try to use the -h operator.
C:\Users\gerard\Desktop\tpc\tpch_2_14_3\dbgen>nmake /f makefile
Microsoft (R) Program Maintenance Utility Version 10.00.30319.01
Copyright (C) Microsoft Corporation. All rights reserved.
makefile(127) : fatal error U1001: syntax error : illegal character '{' in macro
Stop.
In the makefile, replace: