Memsql, Sqlfire, Oracle XE performance comparison test

1. Software version

Memsql 3.1

Pivotal Sqlfire 1.1.2

Oracle 11g XE

2. Test environment

Homestay Host

Os:windows 7 64-bit Professional Edition

cpu:i7-3770 3.4GHz

Memory: 16G

HDD: Greater than 800G

VMware virtual Machines

Os:centos 6.3 64-bit

Memory: 16G

Where Memsql runs on virtual machines, Sqlfire and Oracle run directly on the host

Sqlfire only open 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  PRI MARY key,c1 DOUBLE precision,c2 bigint,c3 varchar (+), C4 varchar (+), C5 varchar (+), C6 varchar); CREATE TABLE T_r EL1 (rec_id BIGINT not null  PRIMARY key,main_id bigint,c1 DOUBLE precision,c2 bigint,c3 varchar (+), C4 varchar (100), C5 varchar (+), C6 varchar (+)); Create Table T_rel2 (rec_id BIGINT not null  PRIMARY key,main_id bigint,c1 DOUBLE PREC ISION,C2 bigint,c3 varchar (+), C4 varchar (+), C5 varchar (+), C6 varchar (+); CREATE table T_rel3 (rec_id BIGINT not n ull  PRIMARY key,main_id bigint,c1 DOUBLE precision,c2 bigint,c3 varchar (+), C4 varchar (+), C5 varchar (+), C6 VARCHAR), 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  PRI MARY key,c1 DOUBLE precision,c2 bigint,c3 varchar (+), C4 varchar (+), C5 varchar (+), C6 varchar (+)) persistent; CREATE TABLE T_rel1 (rec_id BIGINT not null  PRIMARY key,main_id bigint,c1 DOUBLE precision,c2 bigint,c3 VARCHAR (+), C 4 varchar (+), C5 varchar (+), C6 varchar (+)) persistent;create table T_rel2 (rec_id BIGINT not null  PRIMARY KEY, main_id bigint,c1 DOUBLE precision,c2 bigint,c3 varchar (+), C4 varchar (+), C5 varchar (+), C6 varchar (100)) Persistent;create table T_rel3 (rec_id BIGINT not null  PRIMARY key,main_id bigint,c1 DOUBLE precision,c2 bigint,c3 VAR CHAR (+), C4 varchar (+), C5 varchar (+), C6 varchar) 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 (+), C4 VARCHAR2 (+), C5 VARCHAR2 (+), 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 (+), C4 VARCHAR2 (+), C5 VARCHAR2 (+), C6 VARCHAR2 (+)); 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 (+), C4 VARCHAR2 (+), C5 VARCHAR2 (+), C6 VARCHAR2 (+)), 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 (+), C4 VARCHAR2 (+), C5 VARCHAR2 (+), C6 VARCHAR2 (+)); 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

4 sheets, 1 million data per piece

The code snippet that generates the 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.execut Eupdate (); 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, each inserting 1 million data, consumption time comparison

Units: Milliseconds

Memsql	Sqlfire	Oracle
624765	196140	1289811

The following is a query test that performs 10 times the average consumption time (not including first execution)

Units: Milliseconds

Query Test one: Single-table integer field comparison

Select COUNT (*) from T_main where c2>1000;

Memsql	Sqlfire	Oracle
21st	675	58

Query Test two: single-table like

Select COUNT (*) from T_main where C4 like '%c% ';

Memsql	Sqlfire	Oracle
41	875	133

Query Test three: Multi-table associated floating-point number 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 four: Multi-table associative 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

No CPU or memory usage exceeded 50% during the test

Insert performance Sqlfire Highest, memsql second, Oracle slowest, memsql efficiency is about twice times faster than Oracle

Query performance Memsql Highest, Oracle second, Sqlfire slowest (slow surprisingly ...) ), the Memsql efficiency is about twice times that of Oracle

Do not know how the environment can detect 30 times times the improvement of performance ...

Memsql, Sqlfire, Oracle XE performance comparison test