Memsql, Sqlfire, Oracle XE performance comparison test

Source: Internet
Author: User
Tags create index memory usage memsql

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.