MemSQL, SQLFire, Oracle XE performance comparison test, memsqlsqlfire

Source: Internet
Author: User
Tags memsql

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...

 




Related Article

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.