Oracle10g、Db2v95、SqlServer2000性能对比(用数据说话).doc

Oracle10g、Db2v9.5、SqlServer2000性能对比(用数据说话)1.后台调用存储过程性能对比表:动作Oracle(ms)Db2(ms)Sqlserver(ms)Dowhile_i(10000)281ms 156 ms 2000ms Dowhile_i_commit(10000)734ms4400ms4000msDowhile_u(10000)5641ms 27389ms 61000msDowhile_u_commit(10000)6125ms46654ms74000msDowhile_d(10000)3578ms19014ms91000msDowhile_d_commit(10000)4032ms45482ms69000ms结论:后台做插入操作db2性能最高、更新和删除操作oracle性能最高,做批量提交与每条提交一次相比,oracle性能相差不大，但db2、sqlserver性能相差比较大（除sqlserver删除的性能有所下降外）。oracleDb2sqlserver时间(ms)带宽(100Mb)时间带宽(100Mb)时间带宽(100Mb)Insert(10000)6452ms3%7140ms4%7921ms3%Insert_commit(10000)174143ms0.12%49856ms0.9%45825ms0.65%Insert_batch(10000)250ms0.8%281ms2%7140ms3%update(10000)12405ms1%40528ms0.65%61402ms0.45%Update_commit(10000)171392ms0.13%27342ms1%88462ms0.3%Update_batch(10000)5859ms0.15%33154ms0.01%61573ms0.4%delete(10000)9859ms2%30029ms1%72588ms0.4%Delete_commit(10000)160364ms0.3%50216ms0.87%119976ms0.3%Delete_batch(10000)3469ms0.46%22826ms0.94%57480ms0.55%2.前台java调用sql语句性能对比表:结论:1.java向oracle,db2,sqlserver插入10000行数据后提交数据，与每插入1行数据提交一次的性能对比，oracle性能最少提高3倍、db2 性能最少提高6倍,sqlserver性能最少提高5倍, 如果批量处理与批量提交对比，oracle性能最少提高25倍，db2性能最少提高25倍,sqlserver性能提高很小。 2. java向oracle,db2,sqlserver插入10000行数据后提交数据，与每插入1行数据提交一次的性能对比，oracle的性能最高。 3. java向oracle,db2,sqlserver更新10000行数据后提交数据，与每更新1行数据提交一次的性能对比, oracle性能最少提高13倍、db2 性能降低1.4倍,sqlserver性能最少提高1.4倍, 如果批量处理与批量提交对比，oracle性能最少提高2倍，db2性能最少提高1.3倍,sqlserver性能提高有所降低。4. java向oracle,db2,sqlserver更新10000行数据后提交数据，与每更新1行数据提交一次的性能对比, oracle的性能最高5. java向oracle,db2,sqlserver删除10000行数据后提交数据，与每删除1行数据提交一次的性能对比，oracle性能最少提高16倍、db2 性能降低1.6倍,sqlserver性能最少提高1.6倍, 如果批量处理与批量提交对比，oracle性能最少提高2.8倍，db2性能最少提高1.3倍,sqlserver性能最少提高1.2。6.java向oracle,db2,sqlserver删除10000行数据后提交数据，与每删除1行数据提交一次的性能对比，oracle的性能最高总结:后台相对于前台性能对比如下：1. 批量插入操作,oracle、db2性能差不多，sqlserver有所提高。2. 批量更新操作,oracle、db2,sqlserver性能差不多。3. 批量删除操作,oracle、db2性能差不多，sqlserver有所下降4. 批量提交插入操作,oracle、db2,sqlserver性能提高较大5. 批量提交操作,oracle、db2性能提高较大,sqlserver性能差不多。6. 批量提交操作,oracle、db2性能提高较大,sqlserver有所下降 从对比来看后台与前台批量操作性能差不多，但后台与前台批量提交相比oracle,db2性能都有较大的提高，而sqlserver反而有所下降。 脚本:1.做批量插入1. oracle:create table test (a int,b char(10);CREATE OR REPLACE PROCEDURE dowhile(in_count int) as v1 INT;BEGIN v1 := in_count; WHILE v1 0 loop insert into test values (v1, abcd); v1 := v1 - 1; -commit; END loop;END;CREATE OR REPLACE PROCEDURE dowhile_commit(in_count int) as v1 INT;BEGIN v1 := in_count; WHILE v1 0 loop insert into test values (v1, abcd); v1 := v1 - 1; commit; END loop;END;Exec dowhile(10000); 发费时间:0.281秒Exec dowhile_commit(10000); 发费时间:0.734秒2. db2:create table test (a int,b char(10);CREATE PROCEDURE YDGFTEST01.DOWHILE (IN IN_COUNT int) LANGUAGE SQLSPECIFIC DOWHILEBEGIN DECLARE v1 int; set v1=in_count; while v10 do insert into test values(v1,abcd);SET v1=v1-1; end while;ENDCREATE PROCEDURE YDGFTEST01.DOWHILE_COMMIT (IN IN_COUNT int) LANGUAGE SQLSPECIFIC DOWHILE_COMMITBEGIN DECLARE v1 int; set v1=in_count; while v10 do insert into test values(v1,abcd); commit;SET v1=v1-1; end while;ENDCall dowhile(10000)发费时间: 0.156 秒Call dowhile_commit(10000)发费时间:44秒3. sqlserver:create table test (a int,b char(10);create procedure dowhile in_count intasbegin declare v1 int select v1=in_countwhile (v10) begin insert into test values(v1,abcd) select v1=v1-1 endendcreate procedure dowhile_commit in_count intasbegin declare v1 int select v1=in_countwhile (v10) begin insert into test values(v1,abcd) commit select v1=v1-1 endendexec dowhile 10000发费时间:2秒Exec dowhile_commit 10000发费时间:4秒2.做批量更新1.oracle CREATE OR REPLACE Procedure Dowhile_u(In_Count Int) As V1 Int;Begin V1 := In_Count; While V1 0 Loop Update test Set b=frtg Where a=v1; V1 := V1 - 1; End Loop;End;CREATE OR REPLACE Procedure Dowhile_u_commit(In_Count Int) As V1 Int;Begin V1 := In_Count; While V1 0 Loop Update test Set b=frtg Where a=v1; Commit; V1 := V1 - 1; End Loop;End;Exec Dowhile_u(10000) 发费时间:5.641秒Exec Dowhile_u_commit(10000) 发费时间:6.125秒2.db2 CREATE PROCEDURE YDGFTEST01.DOWHILE_U ( IN IN_COUNT INT) LANGUAGE SQL SPECIFIC DOWHILE_U BEGIN DECLARE v1 INT;SET v1=in_count;WHILE v10 DOUPDATE testSET b=frtqWHERE a=v1;SET v1=v1-1;END WHILE;ENDCREATE PROCEDURE YDGFTEST01.DOWHILE_U_commit ( IN IN_COUNT INT) LANGUAGE SQL SPECIFIC DOWHILE_U_commit BEGIN DECLARE v1 INT;SET v1=in_count;WHILE v10 DOUPDATE testSET b=frtqWHERE a=v1;commit;SET v1=v1-1;END WHILE;ENDCall dowhile_u(10000)发费时间:27.389秒Call dowhile_u(10000)发费时间:46.654秒3.sqlservercreate procedure dowhile_u in_count intasbegin declare v1 int select v1=in_count while (v10) begin update test set b=frtg where a=v1 select v1=v1-1 endendcreate procedure dowhile_u_commit in_count intasbegin declare v1 int select v1=in_count while (v10) begin update test set b=frtg where a=v1 commit select v1=v1-1 endendExec Dowhile_u(10000) 发费时间:1分1秒Exec Dowhile_u_commit(10000) 发费时间:1分14秒3.做批量删除1. oracleCREATE OR REPLACE Procedure Dowhile_D(In_Count Int) As V1 Int;Begin V1 := In_Count; While V1 0 Loop Delete From test Where id=v1; V1 := V1 - 1; End Loop;End;CREATE OR REPLACE Procedure Dowhile_D_commit(In_Count Int) As V1 Int;Begin V1 := In_Count; While V1 0 Loop Delete From testWhere id=v1;Commit; V1 := V1 - 1; End Loop;End;Exec dowhile_d(10000)发费时间:3.578秒Exec dowhile_d_commit(10000)发费时间:4.032秒2. db2 CREATE PROCEDURE YDGFTEST01.DOWHILE_D ( IN IN_COUNT INT) LANGUAGE SQL SPECIFIC DOWHILE_D BEGIN DECLARE v1 INT;SET v1=in_count;WHILE v10 DOdelete from testWHERE id=v1;SET v1=v1-1;END WHILE;ENDCREATE PROCEDURE YDGFTEST01.DOWHILE_D_COMMIT ( IN IN_COUNT INT) LANGUAG