TimesTen性能測(cè)試報(bào)告測(cè)試目的測(cè)試版本機(jī)器配置測(cè)試素材o表結(jié)構(gòu)測(cè)試場(chǎng)景1—數(shù)據(jù)庫端寫性能o測(cè)試sql語句示例o測(cè)試結(jié)果測(cè)試場(chǎng)景2—jdbc接口寫性能o測(cè)試java代碼示例o測(cè)試結(jié)果測(cè)試場(chǎng)景3—并發(fā)寫性能o測(cè)試代碼示例o測(cè)試結(jié)果測(cè)試場(chǎng)景4—查詢性能o測(cè)試用例o測(cè)試結(jié)果測(cè)試結(jié)論測(cè)試目的1、timesten在中等規(guī)模(百萬級(jí))數(shù)據(jù)量下的寫入性能2、timesten在中等規(guī)模(百萬級(jí))數(shù)據(jù)量下的查詢性能(測(cè)試準(zhǔn)備中)測(cè)試版本機(jī)器配置cpu：Intel(R)Core(TM)i7-4820KCPU@3.70GHz(1U/4Core)mem:64G測(cè)試素材表結(jié)構(gòu)自定義的用戶表USERS的寫入測(cè)試，此表大約40個(gè)字段，含常見的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,isapprover NUMBER(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,workerid VARCHAR2(13),old_useridVARCHAR2(50),isjudgeNUMBER(11))測(cè)試場(chǎng)景1一一數(shù)據(jù)庫端寫性能逐行提交與批量提交測(cè)試sql語句示例--逐行提交foriin1..1000000loopinsertintoUSERS(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@163.com','','159xxxxx773','','$人員$00$000$3100001','1','$人員$00$300$01000.8105390$3100001','',0,sysdate,'','','','','','','','','','','','','','','','','00003100001','jzp',1);commit;endloop;--批量提交insertintoUSERSselect*fromUSERS_BAK;逐行提交并循環(huán)執(zhí)行一百萬次，測(cè)試寫入時(shí)長。測(cè)試結(jié)果timesten(逐行提oracle(逐行提t(yī)imesten(批量提oracle(批量提交）交）交）交）100w15s86s14.5s28s10w1.5s8.5s未測(cè)試未測(cè)試測(cè)試場(chǎng)景2——jdbc接口寫性能采用jdbc方式提交Timesten的jdbc連接方式有兩種，一種是基于客戶端連接，一種是直連模式。以下是摘抄官方文檔的介紹：TheJDBCURLprovidesacompactwayinwhichtoprovidetheconnectionpropertiestheTimesTenDataSourceobject.TheTimesTenJDBCURLhasthefollowingformat.jdbc:timesten:{direct|client}:dsn=DSNname;[DSNattributes;]ConnectionstoTimesTendatabasescanbeeitherdirectorclientwithdirectbeingthedefaultconnectiontype.AdirectconnectioncanonlybemadefromaprogramthatisrunningonsameserverastheTimesTendatabase.Clientconnectionscanbemadefromprogramsrunningremotelyoronserverhostingthedatabase.直連模式要求java應(yīng)用與Timesten服務(wù)器在同一臺(tái)物理機(jī)器上，客戶端模式則運(yùn)行java應(yīng)用遠(yuǎn)程連接Timesten服務(wù)器。一般來講，直連模式性能更好java測(cè)試代碼示例PreparedStatementstmt=con.prepareStatement("insertintoUSERSvalues(??????????????????????????????????)");VCX_LL4</O\.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.//;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@163.com");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();))測(cè)試結(jié)果jdbc方式timesten(direct)timesten(client)oracle100w 25s 110s 70s10w 2.5s 11s 8s測(cè)試場(chǎng)景3——并發(fā)寫性能分別啟用2線程、4線程和10線程來測(cè)試，寫入的數(shù)據(jù)總量保持為100w,2線程時(shí)，每個(gè)線程分配50w記錄，10線程時(shí)，每個(gè)線程分配10w記錄測(cè)試代碼示例publicclassMultiThreadimplementsRunnable{privateDataWriterTestdataWrite=newDataWriterTest();publicvoidrun(){dataWrite.test();})〃dataWrite.test()代碼參見測(cè)試場(chǎng)景2的示例代碼MultiThreadmt=newMultiThread();newThread(mt).start();newThread(mt).start();newThread(mt).start();newThread(mt).start();測(cè)試結(jié)果jdbc方式timesten(2線程)timesten(4線程)timesten(10線程)100w 19s 20s 20s測(cè)試場(chǎng)景4——查詢性能百萬級(jí)數(shù)據(jù)量下模擬BI場(chǎng)景中的即席查詢和報(bào)表分析測(cè)試用例sql用例sql用例析_1個(gè)維、1個(gè)指標(biāo)即席分析_1個(gè)維、1個(gè)指標(biāo)where(a.HYDMISNOTNULL)groupbySUBSTR(a.XZQH,1,2))row_whererownum<=600

即席分析_2個(gè)維、5個(gè)指標(biāo)一般報(bào)表_1即席分析_2個(gè)維、5個(gè)指標(biāo)一般報(bào)表_1個(gè)維，5個(gè)指標(biāo)一般報(bào)表_1個(gè)維，20個(gè)指標(biāo)多級(jí)浮動(dòng)_2個(gè)維，5個(gè)指標(biāo)fromISDZD_B0_10Y_NEWawhere(a.HYDMISNOTNULL)groupbySUBSTR(a.XZQH,1,2),SUBSTR(a.DJZCLX,1,1))row_whererownum<=600selectrow_.*,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)asB2fromISDZD_B0_10Y_NEWawhere((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))groupbySUBSTR(a.XZQH,1,2)orderbysum(a.F10)descnullslast)row_whererownum<=1000selectrow_.*,rownumfrom(selectC2asC2,D2asD2,E2asE2,F2asF2,G2asG2,H2asH2,I2asI2J2asJ2,K2asK2,L2asL2,M2asM2,N2asN2,O2asO2,P2asP2,Q2asQ2,R2asR2,S2asS2,T2asT2,U2asU2,V2asV2,B2asB2,B2_sortnasB2_sortnfrom(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_sortnfromISDZD_B0_10Y_NEWawhere((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))groupbySUBSTR(a.XZQH,1,2))awhere(B2_sortn<=40)orderbyB2_sortnnullsfirst)row_whererownum<=1000selectrow_.*,rownumfrom(selectC3asC3,D3asD3,E3asE3,F3asF3,G3asG3,C2asC2,D2asD2,E2asE2,F2asF2,G2asG2,B3asB3,B2asB2,B2_sortnasB2_sortnfrom(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_sortnfrom(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)asB2fromISDZD_B0_10Y_NEWawhere((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)asB2fromISDZD_B0_10Y_NEWawhere((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))groupbySUBSTR(a.XZQH,1,2))bwhere(A.B2=B.B2(+)))awhere(B2_sortn<=40)orderbyB3nullsfirst,B2_sortnnullsfirst)row_whererownum<=1000selectrow_.*,rownumfrom(selectC3asC3,D3asD3,E3asE3,F3asF3,G3asG3,H3asH3,I3asI3J3asJ3,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_sortnfrom(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多級(jí)浮動(dòng)多級(jí)浮動(dòng)_2個(gè)維，20個(gè)指標(biāo)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)asB2fromISDZD_B0_10Y_NEWawhere((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)asB2fromISDZD_B0_10Y_NEWa

where((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))圖表展現(xiàn)_單圖單表圖表展現(xiàn)_單圖單表圖表展現(xiàn)_雙圖雙表where(A.B2=B.B2(+)))awhere(B2_sortn<=40)orderbyB3nullsfirst,B2_sortnnullsfirst)row_whererownum<=1000selectrow_.*,rownumfrom(selectC2asC2,D2asD2,B2asB2,B2_sortnasB2_sortnfrom(selectsum(a.F15)asC2,sum(a.F33)asD2,SUBSTR(a.XZQH,1,2)asB2,row_number()over(orderbysum(a.F33)descnullslast)asB2_sortnfromISDZD_B0_10Y_NEWawhere((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))groupbySUBSTR(a.XZQH,1,2))awhere(B2_sortn<=10)orderbyB2_sortnnullsfirst)row_whererownum<=1000selectrow_.*,rownumfrom(selectC2asC2,D2asD2,B2asB2,B2_sortnasB2_sortnfrom(selectsum(a.F15)asC2,sum(a.F33)asD2,SUBSTR(a.HYDM,1,1)asB2,row_number()over(orderbysum(a.F33)descnullslast)asB2_sortnfromISDZD_B0_10Y_NEWawhere((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))groupbySUBSTR(a.HYDM,1,1))awhere(B2_sortn<=10)orderbyB2_sortnnullsfirst)row_whererownum<=1000和selectrow_.*,rownumfrom(selectC2asC2,D2asD2,B2asB2,B2_sortnasB2_sortnfrom(selectsum(a.F15)asC2,sum(a.F33)asD2,SUBSTR(a.XZQH,1,2)asB2,row_number()over(orderbysum(a.F33))asB2_sortnfromISDZD_B0_10Y_NEWawhere((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1))groupbySUBSTR(a.XZQH,1,2))a

where(B2_sortn<=10)orderbyB2_sortnnullsfirst)row_whererownum<=1000展開前selectrow_.*,rownumfrom(selectsum(a.F15)asB2,sum(a.F25)asC2,sum(a.F30)asD2,sum(a.F33)asE2,sum(a.F10)asF2,sum(a.F37)asG2,sum(a.F42)asH2,sum(a.F48)asI2,sum(a.F49)asJ2,sum(a.F50)asK2,sum(a.F52)asL2,sum(a.F53)asM2,sum(a.F54)asN2,sum(a.F55)asO2,sum(a.F56)asP2,sum(a.F57)asQ2,sum(a.F59)asR2,sum(a.F60)asS2,sum(a.F60)asT2,sum(a.F58)asU2,'000000'asA2fromISDZD_B0_10Y_NEWa統(tǒng)計(jì)報(bào)where((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1)))row_whererownum<=1000統(tǒng)計(jì)報(bào)表下鉆展開后折疊展開selectrow_.*,rownumfrom(selectsum(a.F15)asB2,sum(a.F25)asC2,sum(a.F30)asD2,sum(a.F33)asE2,sum(a.F10)asF2,sum(a.F37)asG2,sum(a.F42)asH2,sum(a.F48)asI2,sum(a.F49)asJ2,sum(a.F50)asK2,sum(a.F52)asL2,sum(a.F53)asM2,sum(a.F54)asN2,sum(a.F55)asO2,sum(a.F56)asP2,sum(a.F57)asQ2,sum(a.F59)asR2,sum(a.F60)asS2,sum(a.F60)asT2,sum(a.F58)asU2,SUBSTR(a.XZQH,1,2)asA2fromISDZD_B0_10Y_NEWawhere((a.HYDMISNOTNULL)AND(a.BBQ_like'2014%')AND(2>1)AND(2>1))groupbySUBSTR(a.XZQH,1,2))row_whererownum<=1000主表統(tǒng)計(jì)報(bào)

表下鉆

跳轉(zhuǎn)selectrow_.*,rownumfrom(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,a.DJZCLXasB2,max(b.caption_)asmc_B20統(tǒng)計(jì)報(bào)

表下鉆

跳轉(zhuǎn)fromISDZD_B0_10Y_NEWa,DIM_2014NSSZLD_NSRDJZCLXbwhere(A.DJZCLX=B.ID_(+))and((a.HYDMISNOTNULL)