1、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-4820K CPU 3.70GHz （1U / 4Core ）mem: 64G测试素材表
2、结构自定义的用户表USERS的写入测试，此表大约40 个字段，含常见的 varchar、number、date、timstamp等类型。以下为建表语句：create table USERS( index_ NUMBER(11), userid VARCHAR2(50), username VARCHAR2(100), password VARCHAR2(50), deparment VARCHAR2(50), email VARCHAR2(50), fax VARCHAR2(30), tel1 VARCHAR2(50), tel2 VARCHAR2(50), title VARCHAR2(50
3、), enabled VARCHAR2(2), option_ VARCHAR2(100), memo CLOB, isapprover NUMBER(11), lastchangetime TIMESTAMP(6), fxtype VARCHAR2(2), is1104 VARCHAR2(1), isbl VARCHAR2(1), isglyVARCHAR2(1), ishn VARCHAR2(1), isrh VARCHAR2(1), iswgj VARCHAR2(1), isyt VARCHAR2(1), iszjbl VARCHAR2(1), jc VARCHAR2(6), llbb
4、VARCHAR2(1), role VARCHAR2(2), role_bl VARCHAR2(1), usertype VARCHAR2(3), zt VARCHAR2(8), pwdchangetime DATE, workerid VARCHAR2(13), old_userid VARCHAR2(50), isjudge NUMBER(11)测试场景1数据库端写性能逐行提交与批量提交测试sql语句示例-逐行提交 for i in 11000000 loop insert into USERS (INDEX_, USERID, USERNAME, PASSWORD, DEPARMENT,
5、 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, , , 159xxxxx
6、773, , $人员$00$000$3100001, 1, $人员$00$300$01000.8105390$3100001, , 0, sysdate, , , , , , , , , , , , , , , , , 00003100001, jzp, 1); commit; end loop; -批量提交insert into USERS select * from USERS_BAK;逐行提交并循环执行一百万次，测试写入时长。测试结果timesten（逐行提交） oracle（逐行提交） timesten（批量提交） oracle（批量提交）100w 15s 86s 14.5s 28s1
7、0w 1.5s 8.5s 未测试 未测试测试场景2jdbc接口写性能采用jdbc方式提交Timesten的jdbc连接方式有两种，一种是基于客户端连接，一种是直连模式。以下是摘抄官方文档的介绍：The JDBC URL provides a compact way in which to provide the connection properties the TimesTenDataSource object. The TimesTen JDBC URL has the following format. jdbc:timesten:direct|client:dsn=DSNname;DS
8、Nattributes; Connections to TimesTen databases can be either direct or client with direct being the default connection type. A direct connection can only be madefrom a program that is running on same server as the TimesTen database. Client connections can be made from programs running remotely or on
9、 server hosting the database.直连模式要求java应用与T imesten服务器在同一台物理机器上，客户端模式则运行java应用远程连接Timesten服务器。一般来讲，直连模式性能更好java测试代码示例PreparedStatement stmt = con.prepareStatement(“insert into USERS values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)“); for(int i=0;i1) group by SUBSTR(a.XZQH,
10、1,2) order by sum(a.F10) desc nulls last )row_ where rownum 1) group by SUBSTR(a.XZQH,1,2) a where (B2_sortn1) group by SUBSTR(a.XZQH,1,2), SUBSTR(a.DJZCLX,1,1) a ,( select sum(a.F15) as C2,sum(a.F25) as D2,sum(a.F30) as E2,sum(a.F33) as F2,sum(a.F10) as G2, SUBSTR(a.XZQH,1,2) as B2from ISDZD_B0_10Y
11、_NEW a where (a.HYDM IS NOT NULL) AND (a.BBQ_ like 2014%) AND (21) group by SUBSTR(a.XZQH,1,2) b where (A.B2=B.B2(+) a where (B2_sortn1) group by SUBSTR(a.XZQH,1,2), SUBSTR(a.DJZCLX,1,1) a ,( select sum(a.F15) as C2,sum(a.F25) as D2,sum(a.F30) as E2,sum(a.F33) as F2,sum(a.F10) as G2,sum(a.F37) as H2
12、,sum(a.F42) as I2,sum(a.F48) as J2,sum(a.F49) as K2,sum(a.F50) as L2,sum(a.F52) as M2,sum(a.F53) as N2,sum(a.F54) as O2,sum(a.F55) as P2,sum(a.F56) as Q2,sum(a.F57) as R2,sum(a.F59) as S2,sum(a.F60) as T2,sum(a.F60) as U2,sum(a.F58) as V2, SUBSTR(a.XZQH,1,2) as B2from ISDZD_B0_10Y_NEW a where (a.HYD
13、M IS NOT NULL) AND (a.BBQ_ like 2014%) AND (21) group by SUBSTR(a.XZQH,1,2) b where (A.B2=B.B2(+) a where (B2_sortn1) group by SUBSTR(a.XZQH,1,2) a where (B2_sortn1) group by SUBSTR(a.HYDM,1,1) a where (B2_sortn1) group by SUBSTR(a.XZQH,1,2) a where (B2_sortn1) )row_ where rownum 1) AND (21) group b
14、y SUBSTR(a.XZQH,1,2) )row_ where rownum 1)group by a.DJZCLX order by a.DJZCLX nulls first )row_ where rownum 1) group by SUBSTR(a.XZQH,1,2) )row_ where rownum = 1000测试结果场景用例 Oracle TimesTen即席分析_1个维、1个指标 0.64s 0.34s即席分析_ 2个维、5个指标 0.9s 0.53s一般报表_1个维，5个指标 0.72s 0.44s一般报表_1个维，20个指标 0.89s 0.64s多级浮动_ 2个维，
15、5个指标 1.45s 0.89s多级浮动_ 2个维，20个指标 1.76s 1.44s图表展现_单图单表 0.67s 0.29s图表展现_双图双表 0.69s 0.29s统计报表下钻 _ 折叠展开 0.92s 0.67s统计报表下钻 _ 跳转 1.12s 1.12s图表联动 0.72s 0.37s测试结论1、timesten 适用于简单SQL、短小事务、高并发的事务处理场景，与oracle相比写入性能有24X左右的提升2、简单事务批量提交场景下，timesten性能与逐行操作基本一致，oracle虽然在批量场景下性能有明显提升，但与timesten相比仍有2X左右的差距，从掌握的资料来看，在大
16、数据集批量写场景下，相比oracle，timesten没有优势，不适合做批量ETL3、timesten 的写入耗时随数据量增大而线性增加，测试过程未出现性能抖动，表现比较稳定。4、采用jdbc方式访问tt时，direct直连模式比client客户端模式拥有更好的性能且更接近数据库性能，生产环境应尽量采用direct模式，client模式性能衰减较大5、timesten 的jdbc只是个”桥“ ，本质上还是odbc 方式访问6、与单线程相比，多线程性能有一定幅度的提升，从测试结果来看，24 线程比较适合7、timesten 的查询性能比 oracle大约有1.52.5X的提升，查询越复杂，指标项越多，性能优势越小。8、timesten 适合响应要求和吞吐量非常高的简短事务和查询场景，擅长做为缓存数据库缓存热点数据的场景，在某些应用中可设计为数据仓库的前置层，而单独作为ETL 或DW层并不适合。公开资料显示，timesten主要用于电信实时计费、基金股票实时撮合交易等场合。
