Preparing benchmark data

sudo mkdir -p /home/justin/src
sudo chown -R a+wrx /home/justin/src
cd /home/justin/src
https://github.com/greenlion/ssb-dbgen.git
cd ssb-dbgen
make
./dbgen -s 10 -T l
./dbgen -s 10 -T c
./dbgen -s 10 -T p
./dbgen -s 10 -T s
./dbgen -s 10 -T d

create schema

CREATE DATABASE ssb_cs;
USE ssb_cs;

DROP TABLE IF EXISTS customer;
CREATE TABLE IF NOT EXISTS customer
(
C_CustomerKey int ,
C_Name varchar(25),
C_Address varchar(25),
C_City varchar(10),
C_Nation varchar(15),
C_Region varchar(12),
C_Phone varchar(15),
C_MktSegment varchar(10)
);

DROP TABLE IF EXISTS part;
CREATE TABLE IF NOT EXISTS part
(
P_PartKey int ,
P_Name varchar(25),
P_MFGR varchar(10),
P_Category varchar(10),
P_Brand varchar(15),
P_Colour varchar(15),
P_Type varchar(25),
P_Size tinyint,
P_Container char(10)
);

DROP TABLE IF EXISTS supplier;
CREATE TABLE supplier
(
S_SuppKey int ,
S_Name char(25),
S_Address varchar(25),
S_City char(10),
S_Nation char(15),
S_Region char(12),
S_Phone char(15)
);

DROP TABLE IF EXISTS dim_date;
CREATE TABLE IF NOT EXISTS dim_date
(
D_DateKey int ,
D_Date char(18),
D_DayOfWeek char(9),
D_Month char(9),
D_Year smallint,
D_YearMonthNum int,
D_YearMonth char(7),
D_DayNumInWeek tinyint,
D_DayNumInMonth tinyint,
D_DayNumInYear smallint,
D_MonthNumInYear tinyint,
D_WeekNumInYear tinyint,
D_SellingSeason char(12),
D_LastDayInWeekFl tinyint,
D_LastDayInMonthFl tinyint,
D_HolidayFl tinyint,
D_WeekDayFl tinyint
);

DROP TABLE IF EXISTS lineorder;
CREATE TABLE IF NOT EXISTS lineorder
(
LO_OrderKey bigint not null,
LO_LineNumber tinyint not null,
LO_CustKey int not null,
LO_PartKey int not null,
LO_SuppKey int not null,
LO_OrderDateKey int not null,
LO_OrderPriority varchar(15),
LO_ShipPriority char(1),
LO_Quantity tinyint,
LO_ExtendedPrice decimal,
LO_OrdTotalPrice decimal,
LO_Discount decimal,
LO_Revenue decimal,
LO_SupplyCost decimal,
LO_Tax tinyint,
LO_CommitDateKey int not null,
LO_ShipMode varchar(10)
);

Loading CS

load data infile '/home/justin/src/ssb-dbgen/lineorder.tbl' into table lineorder fields terminated by '|';
– Query OK, 59986052 rows affected (6 min 21.08 sec)
– Records: 59986052 Deleted: 0 Skipped: 0 Warnings: 0

load data infile '/home/justin/src/ssb-dbgen/customer.tbl' into table customer fields terminated by '|';
– Query OK, 300000 rows affected (12.94 sec)
– Records: 300000 Deleted: 0 Skipped: 0 Warnings: 0

load data infile '/home/justin/src/ssb-dbgen/supplier.tbl' into table supplier fields terminated by '|';
– Query OK, 20000 rows affected (3.39 sec)
– Records: 20000 Deleted: 0 Skipped: 0 Warnings: 0

load data infile '/home/justin/src/ssb-dbgen/part.tbl' into table part fields terminated by '|';
– Query OK, 800000 rows affected (12.78 sec)
– Records: 800000 Deleted: 0 Skipped: 0 Warnings: 0

load data infile '/home/justin/src/ssb-dbgen/date.tbl' into table dim_date fields terminated by '|';
– Query OK, 2556 rows affected (2.21 sec)
– Records: 2556 Deleted: 0 Skipped: 0 Warnings: 0

Test queries ColumnStore vs Infobright Community Edition

Q1.1 CS:

---------------

---------------

---------------
1 row in set (8.09 sec)

Q1.1 ICE:

---------------

---------------

---------------
1 row in set (8.57 sec)

Q1.2 CS:

--------------

--------------

--------------
1 row in set (1.15 sec)

Q1.2 ICE:

--------------

--------------

--------------
1 row in set (3.20 sec)

Q1.3 CS:

--------------

--------------

--------------
1 row in set (1.07 sec)

Q1.3 ICE:

--------------

--------------

--------------
1 row in set (3.29 sec)

Q2.1 CS

EMPTY SET

Q2.1 ICE

...

--------------------------------
280 rows in set (6.48 sec)

Q2.2 CS

EMPTY SET

Q2.1 ICE

...

--------------------------------
56 rows in set (2.62 sec)

Q2.3 CS

EMPTY SET

Q2.3 ICE

...

--------------------------------
7 rows in set (2.52 sec)

Q3.1 CS

EMPTY SET

Q3.1 ICE

...

-------------------------------------+
150 rows in set (6.04 sec)

Q3.2 CS

EMPTY SET

Q3.2 ICE

...

-------------------------------------+
600 rows in set (2.98 sec)

Q3.3 CS

-------------------------------------+
24 rows in set (1.59 sec)

Q3.3 ICE

-------------------------------------+
24 rows in set (2.04 sec)

Q3.4 CS

------------------------------------+

------------------------------------+

------------------------------------+
4 rows in set (1.42 sec)

Q3.4 ICE

------------------------------------+

------------------------------------+

------------------------------------+
4 rows in set (1.96 sec)

Q4.1 CS

EMPTY SET

Q4.1 ICE

...

---------------------------------
35 rows in set (7.42 sec)

Q4.2 CS

EMPTY SET

Q4.2 ICE

...

------------------------------------------+
100 rows in set (3.98 sec)

Q4.3 CS

EMPTY SET

Q4.3 ICE

...

------------------------------------+
800 rows in set (3.31 sec)