MemSQL, SQLFire, Oracle XE performance comparison test, memsqlsqlfire

MemSQL, SQLFire, Oracle XE performance comparison test, memsqlsqlfire

1. Software Version

Memory SQL 3.1

Pivotal SQLFire 1.1.2

Oracle 11g XE

 

2. Test Environment

Host

OS: Windows 7 64-bit Professional Edition

CPU: i7-3770 3.4 GHz

Memory: 16 GB

Hard Disk: larger than 800 GB

 

Vmwarevm

OS: CentOS 6.3 64-bit

Memory: 16 GB

 

MemSQL runs on virtual machines, and SQLFire and Oracle directly run on host machines.

SQLFire only enables one server member

3. DDL

MemSQL

DROP TABLE t_main;DROP TABLE t_rel1;DROP TABLE t_rel2;DROP TABLE t_rel3;create table t_main(rec_id BIGINT not null  PRIMARY KEY,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) ;create table t_rel1(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) ;create table t_rel2(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) ;create table t_rel3(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) ;create index rel1main on t_rel1(main_id);create index rel2main on t_rel2(main_id);create index rel3main on t_rel3(main_id);

SQLFire

DROP TABLE t_main;DROP TABLE t_rel1;DROP TABLE t_rel2;DROP TABLE t_rel3;create table t_main(rec_id BIGINT not null  PRIMARY KEY,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) PERSISTENT;create table t_rel1(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) PERSISTENT;create table t_rel2(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) PERSISTENT;create table t_rel3(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) PERSISTENT;create index rel1main on t_rel1(main_id);create index rel2main on t_rel2(main_id);create index rel3main on t_rel3(main_id);

 

Oracle

DROP TABLE t_main;DROP TABLE t_rel1;DROP TABLE t_rel2;DROP TABLE t_rel3;create table t_main(rec_id NUMBER(19,0) not null  PRIMARY KEY,c1 DOUBLE PRECISION,c2 NUMBER(19,0),c3 VARCHAR2(100),c4 VARCHAR2(100),c5 VARCHAR2(100),c6 VARCHAR2(100)) ;create table t_rel1(rec_id NUMBER(19,0) not null  PRIMARY KEY,main_id NUMBER(19,0),c1 DOUBLE PRECISION,c2 NUMBER(19,0),c3 VARCHAR2(100),c4 VARCHAR2(100),c5 VARCHAR2(100),c6 VARCHAR2(100)) ;create table t_rel2(rec_id NUMBER(19,0) not null  PRIMARY KEY,main_id NUMBER(19,0),c1 DOUBLE PRECISION,c2 NUMBER(19,0),c3 VARCHAR2(100),c4 VARCHAR2(100),c5 VARCHAR2(100),c6 VARCHAR2(100)) ;create table t_rel3(rec_id NUMBER(19,0) not null  PRIMARY KEY,main_id NUMBER(19,0),c1 DOUBLE PRECISION,c2 NUMBER(19,0),c3 VARCHAR2(100),c4 VARCHAR2(100),c5 VARCHAR2(100),c6 VARCHAR2(100)) ;create index rel1main on t_rel1(main_id);create index rel2main on t_rel2(main_id);create index rel3main on t_rel3(main_id);

 

4. Test Data

Four tables, with 1 million data records each

The code snippet for generating data is

String sql = "insert into t_main(rec_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?)";PreparedStatement st1 = conn.prepareStatement(sql);sql = "insert into t_rel1(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";PreparedStatement st2 = conn.prepareStatement(sql);sql = "insert into t_rel2(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";PreparedStatement st3 = conn.prepareStatement(sql);sql = "insert into t_rel3(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";PreparedStatement st4 = conn.prepareStatement(sql);long a = System.currentTimeMillis();for (int i = 1; i <= count; i++) {st1.setLong(1, i);st1.setDouble(2, i * 0.1);st1.setLong(3, i * 2);st1.setString(4, "c3_" + i);st1.setString(5, "c4_" + i);st1.setString(6, "c5_" + i);st1.setString(7, "c6_" + i);st1.executeUpdate();st2.setLong(1, i);st2.setLong(2, i);st2.setDouble(3, i * 0.2);st2.setLong(4, i * 3);st2.setString(5, "c3_" + i);st2.setString(6, "c4_" + i);st2.setString(7, "c5_" + i);st2.setString(8, "c6_" + i);st2.executeUpdate();st3.setLong(1, i);st3.setLong(2, i);st3.setDouble(3, i * 0.3);st3.setLong(4, i * 4);st3.setString(5, "c3_" + i);st3.setString(6, "c4_" + i);st3.setString(7, "c5_" + i);st3.setString(8, "c6_" + i);st3.executeUpdate();st4.setLong(1, i);st4.setLong(2, i);st4.setDouble(3, i * 0.4);st4.setLong(4, i * 5);st4.setString(5, "c3_" + i);st4.setString(6, "c4_" + i);st4.setString(7, "c5_" + i);st4.setString(8, "c6_" + i);st4.executeUpdate();}

5. Test Results

4 tables with 1 million data inserted each, time consumption comparison

Unit: milliseconds

 

MemSQL	SQLFire	Oracle
624765	196140	1289811

 

The following is the query test, where the average consumption time is obtained for 10 times (excluding the first execution)

Unit: milliseconds

 

Query Test 1: Comparison of integer fields in a single table

select count(*) from t_main where c2>1000;

 

MemSQL	SQLFire	Oracle
21	675	58

 

Query Test 2: like in a single table

select count(*) from t_main where c4 like '%c%';

 

MemSQL	SQLFire	Oracle
41	875	133

 

Query Test 3: Multi-table join floating point sum

select sum(m.c1+r1.c1+r2.c1+r3.c1) "rt" from t_main m,t_rel1 r1,t_rel2 r2,t_rel3 r3 where m.rec_id=r1.main_id and m.rec_id=r2.main_id and m.rec_id=r3.main_id;

 

MemSQL	SQLFire	Oracle
1365	14640	2077

 

 

Query Test 4: Multi-table join integer sum

select sum(m.c2+r1.c2+r2.c2+r3.c2) "rt" from t_main m,t_rel1 r1,t_rel2 r2,t_rel3 r3 where m.rec_id=r1.main_id and m.rec_id=r2.main_id and m.rec_id=r3.main_id;

 

MemSQL	SQLFire	Oracle
1360	10257	2084

 

6. Summary

During the test, the CPU and memory usage did not exceed 50%

The insert performance of SQLFire is the highest, MemSQL is the second, Oracle is the slowest, and MemSQL is about twice the efficiency of Oracle

MemSQL has the highest query performance, followed by Oracle, and SQLFire is the slowest (surprisingly slow ...), MemSQL is about twice the efficiency of Oracle

I don't know what kind of environment can measure the performance improvement by 30 times...