在Trafodion中,我们发现SQL在trafci查看到的数据库实际编译时间很短,不到1ms,但通过JDBC程序的PrepareStatement执行发现每次都需要几毫秒的时间。以下是用JDBC程序执行的输出结果,编译时间的单位为ms,从以下结果可以发现,同样的SQL执行三次,编译时间在2~4ms左右。
===============================Round 1
=======================================
开始时间:3973114842552
prepare后时间:3973117407086
编译时间:2564
===============================Round 2
=======================================
开始时间:3973638578247
prepare后时间:3973642935949
编译时间:4357
===============================Round 3
=======================================
开始时间:3974160244737
prepare后时间:3974164269660
编译时间:4024
通过在JDBC URL添加maxStatements参数,如下
jdbc:t4jdbc://192.168.168.201:23400/:schema=V7FAT;maxStatements=10
maxStatements表示缓存的PrepareStatment对象的个数,值为缓存的条数。默认值为0,表示关闭statement pooling。关于maxStatements可以参照Trafodion JDBC手册http://trafodion.apache.org/docs/jdbct4ref_guide/index.html#maxstatements
5.12. maxStatements Property
The maxStatements property sets the total number of PreparedStatement objects that the connection pool should cache. This total includes both free objects and objects in use.
Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. For information about how to set properties, see How to Specify JDBC Type 4 Properties.
Data type: int
Units: number of objects
Default: 0 (Disables statement pooling.)
Range: 0 through 2147483647
The value 0 disables statement pooling. Any negative value is treated like 0 (zero).
Example
To specify statement pooling, type:
maxStatements=10
下面是URL添加maxStatements参数后的编译时间输出,从以下结果我们可以发现,JDBC的编译时间得到了巨大的提升。
===============================Round 1
=======================================
开始时间:4034886517149
prepare后时间:4034889063763
编译时间:2546
===============================Round 2
=======================================
开始时间:4034915811537
prepare后时间:4034915886199
编译时间:74
===============================Round 3
=======================================
开始时间:4034936479263
prepare后时间:4034936540699
编译时间:61