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