MemSQL、SQLFire、OracleXE性能对比测试(一)

1.软件版本

MemSQL 3.1

Pivotal SQLFire 1.1.2

Oracle 11g XE

2.测试环境

宿主机

OS：Windows 7 64位 专业版

CPU：i7-3770 3.4GHz

内存：16G

硬盘：大于800G

VMware虚拟机

OS：CentOS 6.3 64位

内存：16G

其中MemSQL运行于虚拟机，SQLFire和Oracle直接运行于宿主机

SQLFire只开启一个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(mai