请列举插入100w行整数最快的数据库

显示全部楼层 · 2012-5-21 10:19:41

sqlite3 1.5秒

千问 · 2012-5-21 10:19:41

要求:插入1-100w这些整数到表，不一定要提交，但要能select count(*),sum(a)from t;

千问 · 2012-5-21 10:19:41

这是我在我的几年前买的一个烂PC 上执行的:
SQL> create table t ( a number);

Table created

SQL>
SQL> create or replace procedure sp_ins_test
2as
3 type t_targetis table of t%rowtype;
4 l_target t_target;
5 l_limitnumber default 3000;
6 c_commit constant smallint :=100000;
7 l_ins_cntnumber :=0;
8 l_start_time number default 0;
9 l_end_time number default 0;
10
11 cursor cur is select level rn from dual connect by level = c_commit then
22
commit;
23
l_ins_cnt :=0;
24
end if;
25exit when cur%notfound;
26 end loop;
27 commit work;
28 close cur;
29 l_end_time := dbms_utility.get_time;
30 dbms_output.put_line('Elipse time :'||(l_end_time - l_start_time)/100);
31end sp_ins_test;
32
33/

Procedure created

SQL> set serveroutput on ;
SQL> exec sp_ins_test;

Elipse time :1.44

PL/SQL procedure successfully completed

SQL> select count(1),sum(a) from t;

COUNT(1) SUM(A)
---------- ----------
1000000 5000005000

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE
11.2.0.1.0
Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

千问 · 2012-5-21 10:19:41

SQL> set timing on;
SQL> truncate table t;

Table truncated

Executed in 0.515 seconds

SQL> insert into t select level rn from dual connect by levelcommit;

Commit complete

Executed in 0 seconds

千问 · 2012-5-21 10:19:41

mysql 不要innodb也很快。

千问 · 2012-5-21 10:19:41

毫无疑问，在一台配置很好的设备上，安装一个可以重复利用系统资源的数据库软件，然后采用并行、直接路径等方式，显然可以大幅度提高插入的速度
但无论怎么做，都能有方案比你更快，所以，永远都是没有最快的

千问 · 2012-5-21 10:19:41

给各参赛厂商一台相同的机器，让他们用尽所有手段调优后比赛。

千问 · 2012-5-21 10:19:41

今天早上一来,我又测了一下,用的是自己工作用的 DELL PRECESION ,
SQL> create table tab_test ( a number);

Table created

SQL>
SQL>create or replace procedure sp_ins_test
2 as
3type t_targetis table of tab_test%rowtype;
4l_target t_target;
5l_limitnumber default 3000;
6c_commit constant smallint :=100000;
7l_ins_cntnumber :=0;
8l_start_time number default 0;
9l_end_time number default 0;
10
11 cursor cur is select level rn from dual connect by level = c_commit then
22
commit;
23
l_ins_cnt :=0;
24
end if;
25exit when cur%notfound;
26 end loop;
27 commit work;
28 close cur;
29 l_end_time := dbms_utility.get_time;
30 dbms_output.put_line('Elipse time :'||(l_end_time - l_start_time)/100);
31end sp_ins_test;
32
33/

Procedure created

SQL> set serveroutput on;
SQL> exec sp_ins_test;

Elipse time :.81

PL/SQL procedure successfully completed

千问 · 2012-5-21 10:19:41

SQL> set timing on

SQL> truncate table tab_test;

Table truncated

Executed in 0.171 seconds

SQL>insert into tab_test select level rn from dual connect by levelcommit;

Commit complete

Executed in 0.015 seconds

SQL>

千问 · 2012-5-21 10:19:41

临时表会不会更快？