TimesTen性能测试报告.docx
- 文档编号:12368063
- 上传时间:2023-04-18
- 格式:DOCX
- 页数:16
- 大小:20.09KB
TimesTen性能测试报告.docx
《TimesTen性能测试报告.docx》由会员分享,可在线阅读,更多相关《TimesTen性能测试报告.docx(16页珍藏版)》请在冰豆网上搜索。
TimesTen性能测试报告
TimesTen性能测试报告
∙测试目的
∙测试版本
∙机器配置
∙测试素材
o表结构
∙测试场景1——数据库端写性能
o测试sql语句示例
o测试结果
∙测试场景2——jdbc接口写性能
o测试java代码示例
o测试结果
∙测试场景3——并发写性能
o测试代码示例
o测试结果
∙测试场景4——查询性能
o测试用例
o测试结果
∙测试结论
测试目的
1、timesten在中等规模(百万级)数据量下的写入性能
2、timesten在中等规模(百万级)数据量下的查询性能(测试准备中)
测试版本
11.2.2.8
机器配置
cpu:
Intel(R)Core(TM)i7-4820KCPU@3.70GHz(1U/4Core)
mem:
64G
测试素材
表结构
自定义的用户表USERS的写入测试,此表大约40个字段,含常见的varchar、number、date、timstamp等类型。
以下为建表语句:
createtableUSERS(index_NUMBER(11),useridVARCHAR2(50),usernameVARCHAR2(100),passwordVARCHAR2(50),deparmentVARCHAR2(50),emailVARCHAR2(50),faxVARCHAR2(30),tel1VARCHAR2(50),tel2VARCHAR2(50),titleVARCHAR2(50),enabledVARCHAR2
(2),option_VARCHAR2(100),memoCLOB,isapproverNUMBER(11),lastchangetimeTIMESTAMP(6),fxtypeVARCHAR2
(2),is1104VARCHAR2
(1),isblVARCHAR2
(1),isglyVARCHAR2
(1),ishnVARCHAR2
(1),isrhVARCHAR2
(1),iswgjVARCHAR2
(1),isytVARCHAR2
(1),iszjblVARCHAR2
(1),jcVARCHAR2(6),llbbVARCHAR2
(1),roleVARCHAR2
(2),role_blVARCHAR2
(1),usertypeVARCHAR2(3),ztVARCHAR2(8),pwdchangetimeDATE,workeridVARCHAR2(13),old_useridVARCHAR2(50),isjudgeNUMBER(11))
测试场景1——数据库端写性能
逐行提交与批量提交
测试sql语句示例
--逐行提交
foriin1..1000000loop
insertintoUSERS(INDEX_,USERID,USERNAME,PASSWORD,DEPARMENT,EMAIL,FAX,TEL1,TEL2,TITLE,ENABLED,OPTION_,MEMO,ISAPPROVER,LASTCHANGETIME,FXTYPE,IS1104,ISBL,ISGLY,ISHN,ISRH,ISWGJ,ISYT,ISZJBL,JC,LLBB,ROLE,ROLE_BL,USERTYPE,ZT,PWDCHANGETIME,WORKERID,OLD_USERID,ISJUDGE)values(10150,'0000jinzp','0000XXX','123456','01000.8105390','jinzhp@','','159xxxxx773','','$人员$00$000$3100001','1','$人员$00$300$01000.8105390$3100001','',0,sysdate,'','','','','','','','','','','','','','','','','00003100001','jzp',1);
commit;
endloop;
--批量提交
insertintoUSERSselect*fromUSERS_BAK;
逐行提交并循环执行一百万次,测试写入时长。
测试结果
timesten(逐行提交)
oracle(逐行提交)
timesten(批量提交)
oracle(批量提交)
100w
15s
86s
14.5s
28s
10w
1.5s
8.5s
未测试
未测试
测试场景2——jdbc接口写性能
采用jdbc方式提交
Timesten的jdbc连接方式有两种,一种是基于客户端连接,一种是直连模式。
以下是摘抄官方文档的介绍:
TheJDBCURLprovidesacompactwayinwhichtoprovidetheconnectionpropertiestheTimesTenDataSourceobject.TheTimesTenJDBCURLhasthefollowingformat.jdbc:
timesten:
{direct|client}:
dsn=DSNname;[DSNattributes;]ConnectionstoTimesTendatabasescanbeeitherdirectorclientwithdirectbeingthedefaultconnectiontype.AdirectconnectioncanonlybemadefromaprogramthatisrunningonsameserverastheTimesTendatabase.Clientconnectionscanbemadefromprogramsrunningremotelyoronserverhostingthedatabase.
直连模式要求java应用与Timesten服务器在同一台物理机器上,客户端模式则运行java应用远程连接Timesten服务器。
一般来讲,直连模式性能更好
java测试代码示例
PreparedStatementstmt=con.prepareStatement("insertintoUSERSvalues(?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
)");
for(inti=0;i<1000000;i++){
stmt.setInt(1,10150);
stmt.setString(2,"0000jinzhp");
stmt.setString(3,"0000xxx");
stmt.setString(4,"{123456}");
stmt.setString(5,"01000.8105390");
stmt.setString(6,"jinzp@");
stmt.setString(7,"");
stmt.setString(8,"159xxxxx773");
stmt.setString(9,"");
stmt.setString(10,"$人员$00$000$3100001");
stmt.setString(11,"1");
stmt.setString(12,"$人员$00$300$01000.8105390$3100001");
stmt.setObject(13,null);
stmt.setInt(14,0);
stmt.setObject(15,null);
stmt.setString(16,"");
stmt.setString(17,"");
stmt.setString(18,"");
stmt.setString(19,"");
stmt.setString(20,"");
stmt.setString(21,"");
stmt.setString(22,"");
stmt.setString(23,"");
stmt.setString(24,"");
stmt.setString(25,"");
stmt.setString(26,"");
stmt.setString(27,"");
stmt.setString(28,"");
stmt.setString(29,"");
stmt.setString(30,"");
stmt.setObject(31,null);
stmt.setString(32,"00003100001");
stmt.setString(33,"jzp");
stmt.setInt(34,1);
stmt.addBatch();
if(i%500==0){
stmt.executeBatch();
}
}
测试结果
jdbc方式
timesten(direct)
timesten(client)
oracle
100w
25s
110s
70s
10w
2.5s
11s
8s
测试场景3——并发写性能
分别启用2线程、4线程和10线程来测试,写入的数据总量保持为100w,2线程时,每个线程分配50w记录,10线程时,每个线程分配10w记录
测试代码示例
publicclassMultiThreadimplementsRunnable{
privateDataWriterTestdataWrite=newDataWriterTest();
publicvoidrun(){
dataWrite.test();}
}
//dataWrite.test()代码参见测试场景2的示例代码
MultiThreadmt=newMultiThread();
newThread(mt).start();
newThread(mt).start();
newThread(mt).start();
newThread(mt).start();
测试结果
jdbc方式
timesten(2线程)
timesten(4线程)
timesten(10线程)
100w
19s
20s
20s
测试场景4——查询性能
百万级数据量下模拟BI场景中的即席查询和报表分析
测试用例
场景用例
sql用例
即席分析_1个维、1个指标
selectrow_.*,rownumfrom(selectsum(a.F7)asF70,SUBSTR(a.XZQH,1,2)asXZQH0
fromISDZD_B0_10Y_NEWa
where(a.HYDMISNOTNULL)
groupbySUBSTR(a.XZQH,1,2))row_whererownum<=600
即席分析_2个维、5个指标
selectrow_.*,rownumfrom(selectsum(a.F7)asF70,sum(a.F8)asF80,sum(a.F9)asF90,sum(a.F12)asF120,sum(a.F13)asF130,SUBSTR(a.XZQH,1,2)asXZQH0,SUBSTR(a.DJZCLX,1,1)asDJZCLX0
fromISDZD_B0_10Y_NEWa
where(a.HYDMISNOTNULL)
groupbySUBSTR(a.XZQH,1,2),SUBSTR(a.DJZCLX,1,1))row_whererownum<=600
一般报表_1个维,5个指标
selectrow_.*,rownumfrom(selectcount(a.NSRSBH)asC2,sum(a.F15)asD2,sum(a.F25)asE2,sum(a.F30)asF2,sum(a.F33)asG2,sum(a.F10)asH2,SUBSTR(a.XZQH,1,2)asB2
fromISDZD_B0_10Y_NEWa
where((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))
groupbySUBSTR(a.XZQH,1,2)
orderbysum(a.F10)descnullslast)row_whererownum<=1000
一般报表_1个维,20个指标
selectrow_.*,rownumfrom(selectC2asC2,D2asD2,E2asE2,F2asF2,G2asG2,H2asH2,I2asI2,J2asJ2,K2asK2,L2asL2,M2asM2,N2asN2,O2asO2,P2asP2,Q2asQ2,R2asR2,S2asS2,T2asT2,U2asU2,V2asV2,B2asB2,B2_sortnasB2_sortn
from(
selectsum(a.F15)asC2,sum(a.F25)asD2,sum(a.F30)asE2,sum(a.F33)asF2,sum(a.F10)asG2,sum(a.F37)asH2,sum(a.F42)asI2,sum(a.F48)asJ2,sum(a.F49)asK2,sum(a.F50)asL2,sum(a.F52)asM2,sum(a.F53)asN2,sum(a.F54)asO2,sum(a.F55)asP2,sum(a.F56)asQ2,sum(a.F57)asR2,sum(a.F59)asS2,sum(a.F60)asT2,sum(a.F60)asU2,sum(a.F58)asV2,SUBSTR(a.XZQH,1,2)asB2,row_number()over(orderbysum(a.F10)descnullslast)asB2_sortn
fromISDZD_B0_10Y_NEWa
where((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))
groupbySUBSTR(a.XZQH,1,2))a
where(B2_sortn<=40)
orderbyB2_sortnnullsfirst)row_whererownum<=1000
多级浮动_2个维,5个指标
selectrow_.*,rownumfrom(selectC3asC3,D3asD3,E3asE3,F3asF3,G3asG3,C2asC2,D2asD2,E2asE2,F2asF2,G2asG2,B3asB3,B2asB2,B2_sortnasB2_sortn
from(
selecta.C3asC3,a.D3asD3,a.E3asE3,a.F3asF3,a.G3asG3,b.C2asC2,b.D2asD2,b.E2asE2,b.F2asF2,b.G2asG2,a.B3asB3,a.B2asB2,row_number()over(partitionbya.B3orderbyb.G2descnullslast)asB2_sortn
from(
selectsum(a.F15)asC3,sum(a.F25)asD3,sum(a.F30)asE3,sum(a.F33)asF3,sum(a.F10)asG3,SUBSTR(a.DJZCLX,1,1)asB3,SUBSTR(a.XZQH,1,2)asB2
fromISDZD_B0_10Y_NEWa
where((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))
groupbySUBSTR(a.XZQH,1,2),SUBSTR(a.DJZCLX,1,1))a
(
selectsum(a.F15)asC2,sum(a.F25)asD2,sum(a.F30)asE2,sum(a.F33)asF2,sum(a.F10)asG2,SUBSTR(a.XZQH,1,2)asB2
fromISDZD_B0_10Y_NEWa
where((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))
groupbySUBSTR(a.XZQH,1,2))b
where(A.B2=B.B2(+)))a
where(B2_sortn<=40)
orderbyB3nullsfirst,B2_sortnnullsfirst)row_whererownum<=1000
多级浮动_2个维,20个指标
selectrow_.*,rownumfrom(selectC3asC3,D3asD3,E3asE3,F3asF3,G3asG3,H3asH3,I3asI3,J3asJ3,K3asK3,L3asL3,M3asM3,N3asN3,O3asO3,P3asP3,Q3asQ3,R3asR3,S3asS3,T3asT3,U3asU3,V3asV3,C2asC2,D2asD2,E2asE2,F2asF2,G2asG2,H2asH2,I2asI2,J2asJ2,K2asK2,L2asL2,M2asM2,N2asN2,O2asO2,P2asP2,Q2asQ2,R2asR2,S2asS2,T2asT2,U2asU2,V2asV2,B3asB3,B2asB2,B2_sortnasB2_sortn
from(
selecta.C3asC3,a.D3asD3,a.E3asE3,a.F3asF3,a.G3asG3,a.H3asH3,a.I3asI3,a.J3asJ3,a.K3asK3,a.L3asL3,a.M3asM3,a.N3asN3,a.O3asO3,a.P3asP3,a.Q3asQ3,a.R3asR3,a.S3asS3,a.T3asT3,a.U3asU3,a.V3asV3,b.C2asC2,b.D2asD2,b.E2asE2,b.F2asF2,b.G2asG2,b.H2asH2,b.I2asI2,b.J2asJ2,b.K2asK2,b.L2asL2,b.M2asM2,b.N2asN2,b.O2asO2,b.P2asP2,b.Q2asQ2,b.R2asR2,b.S2asS2,b.T2asT2,b.U2asU2,b.V2asV2,a.B3asB3,a.B2asB2,row_number()over(partitionbya.B3orderbyb.G2descnullslast)asB2_sortn
from(
selectsum(a.F15)asC3,sum(a.F25)asD3,sum(a.F30)asE3,sum(a.F33)asF3,sum(a.F10)asG3,sum(a.F37)asH3,sum(a.F42)asI3,sum(a.F48)asJ3,sum(a.F49)asK3,sum(a.F50)asL3,sum(a.F52)asM3,sum(a.F53)asN3,sum(a.F54)asO3,sum(a.F55)asP3,sum(a.F56)asQ3,sum(a.F57)asR3,sum(a.F59)asS3,sum(a.F60)asT3,sum(a.F60)asU3,sum(a.F58)asV3,SUBSTR(a.DJZCLX,1,1)asB3,SUBSTR(a.XZQH,1,2)asB2
fromISDZD_B0_10Y_NEWa
where((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))
groupbySUBSTR(a.XZQH,1,2),SUBSTR(a.DJZCLX,1,1))a
(
selectsum(a.F15)asC2,sum(a.F25)asD2,sum(a.F30)asE2,sum(a.F33)asF2,sum(a.F10)asG2,sum(a.F37)asH2,sum(a.F42)asI2,sum(a.F48)asJ2,sum(a.F49)asK2,sum(a.F50)asL2,sum(a.F52)asM2,sum(a.F53)asN2,sum(a.F54)asO2,sum(a.F55)asP2,sum(a.F56)asQ2,sum(a.F57)asR2,sum(a.F59)asS2,sum(a.F60)asT2,sum(a.F60)asU2,sum(a.F58)asV2,SUBSTR(a.XZQH,1,2)asB2
fromISDZD_B0_10Y_NEWa
where((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))
groupbySUBSTR(a.XZQH,1,2))b
where(A.B2=B.B2(+)))a
where(B2_sortn<=40)
orderbyB3nullsfirst,B2_sortnnullsfirst)row_whererownum<=1000
图表展现_单图单表
selectrow_.*,rownumfrom(selectC2as
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- TimesTen 性能 测试报告