有Oracle存储过程如下
create or replace procedure proc_test(p_str varchar2) is
begin
null;
end;
/有java程序如下
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;public class test
{
public static void main(String args[]) throws Exception
{
String outstr ="";
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection("jdbc:oracle:oci8:@local","user", "pass");
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall("begin proc_test(?); end;");
long s = System.currentTimeMillis();
//我希望每次客户端(WEB页面)传入参数给这个java程序总是从这一行里开始执行,
//也就是prepareCall存储过程一次,调用存储过程多次,调用次数会很多
cstmt.setString(1,"客户端传入的参数值");
cstmt.execute(); long e = System.currentTimeMillis();
System.out.println(e - s);
}
}
create or replace procedure proc_test(p_str varchar2) is
begin
null;
end;
/有java程序如下
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;public class test
{
public static void main(String args[]) throws Exception
{
String outstr ="";
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection("jdbc:oracle:oci8:@local","user", "pass");
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall("begin proc_test(?); end;");
long s = System.currentTimeMillis();
//我希望每次客户端(WEB页面)传入参数给这个java程序总是从这一行里开始执行,
//也就是prepareCall存储过程一次,调用存储过程多次,调用次数会很多
cstmt.setString(1,"客户端传入的参数值");
cstmt.execute(); long e = System.currentTimeMillis();
System.out.println(e - s);
}
}
写一个public的调用方法,直接使用构造函数里初始化好的prepareCall
但是 cstmt.setString(1,"客户端传入的参数值");
cstmt.execute();
只要0.4豪秒如果每次客户端传入参数,每次 prepareCall,那样太慢了
而且prepareCall是打开一个游标的,这样打开关闭游标也较慢当然如果把prepareCall放在循环里,比如
for (int i=1; i<=100; i++)
{
prepareCall
setString
execute()
}
那么只是第一次prepareCall耗时 90豪秒,以后每次0.2豪秒
但是这样有什么用呢?
1、建立连接到数据库
2、prepareCall存储过程
然后程序一直等着,客户端有参数传过来了,就
setString
execute()就是prepareCall后程序一直等着,只要有参数传过来,就执行存储过程
所以最终可能变成你要自己模拟一个专门提供 OracleCallableStatement cstmt 的池,这个看起来复杂度就比较高了,如果不小心可能导致连接泄漏。
而且可能每次都是90豪秒,这在业务上是绝对无法接受的。
/**
* CREATE OR REPLACE PROCEDURE proc_test(p_str in out varchar2) AS
* BEGIN
* p_str := 'ECHO: ' || p_str;
* END;
*/
public class TestCall { private static int TIMES = 1000; private static String URL = "jdbc:oracle:thin:@192.168.234.128:1521:redhat5";
private static String USER = "zenki";
private static String PASS = "zenki"; /**
* Reuse CallableStatement: Time spend: 529 Per: 0.529
* Every time prepare new: Time spend: 671 Per: 0.671
*/
public static void main(String[] args) throws Exception {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection(URL, USER, PASS);
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall("call proc_test(?)");
long s = System.currentTimeMillis(); for (int i = 0; i < TIMES; i++) {
cstmt.close();
cstmt = (OracleCallableStatement) con.prepareCall("call proc_test(?)");
cstmt.setString(1, String.valueOf(i));
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.execute();
System.out.print(cstmt.getString(1) + " ");
} long e = System.currentTimeMillis() - s;
System.out.println("\nTime spend: " + e + "\t Per: " + (1.0 * e / TIMES));
}
}我本地的Oracle是跑在虚拟机中,循环测试1000次。上述两行红字注释掉(重用Callable),是529毫秒;不注释(每次创建Callable),是671毫秒。如果希望重用Callable,楼主你需要对Callable做池化以支持并发。
我就是想达到这样的效果
1、建立连接到数据库
2、prepareCall存储过程
然后程序一直等着,客户端有参数传过来了,就
setString
execute()就是prepareCall后程序一直等着,只要有参数传过来,就执行存储过程
你这个是循环,我需要的是客户端传入参数N次,只执行setString和execute() prepareCall执行一次后就一直等着客户端传参数,实在不行prepareCall执行多次也行,但是必须保证只有第一次prepareCall慢,第2次后都很快(0.2豪秒)
那么prepareCall本身需要耗时90豪秒,1000次,每次90豪秒?我因为是在客户端传入参数给存储过程的,这样的循环并不能解决问题
cstmt.close(); // 关闭
cstmt = (OracleCallableStatement) con.prepareCall("call proc_test(?)"); // 创建
cstmt.setString(1, String.valueOf(i));
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.execute();
System.out.print(cstmt.getString(1) + " ");
}也就是每次实际只消耗0.671毫秒。但这里重用了Connection,因为应用程序都会用连接池来池化数据库连接,所以Connection就没必要每次重新弄了。
而你单次调用并没有包含prepareCall的时间,也就是说 实际真正需要连接的 prepareCall只有一次?另外的prepareCall都是重用了连接,而prepareCall本身是很快的?
我的prepareCall很慢(90豪秒)是因为每次都要连接?还想请教一下,为什么我调用一次,打印出的时间有的时候是 16豪秒或者15豪秒,有的时候是 0豪秒?
我有点担心,16豪秒也是不能接受的。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Vector;import oracle.jdbc.OracleCallableStatement;/**
* CREATE OR REPLACE PROCEDURE proc_test(p_str in out varchar2) AS
* BEGIN
* p_str := 'ECHO: ' || p_str;
* END;
*/
public class TestCall { private static int TIMES = 1000; private static String URL = "jdbc:oracle:thin:@192.168.234.128:1521:redhat5";
private static String USER = "zenki";
private static String PASS = "zenki"; public static Connection openConnection() throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
return DriverManager.getConnection(URL, USER, PASS);
} public static void main(String[] args) throws Exception {
simpleRoundTest(); singleThreadTest(1); // SingleThread done. Time spend: 499 Per: 0.499 multiThreadTest(1, 1); // MulitThread done. Time spend: 450 ThreadNum: 1 PoolSize: 1
multiThreadTest(50, 1); // MulitThread done. Time spend: 17286 ThreadNum: 50 PoolSize: 1
multiThreadTest(50, 50); // MulitThread done. Time spend: 6441 ThreadNum: 50 PoolSize: 50
} private static void singleThreadTest(int poolSize) throws Exception {
CallablePool pool = new CallablePool("call proc_test(?)", poolSize);
long timer = System.currentTimeMillis();
for (int i = 0; i < TIMES; i++) {
String result = pool.call(String.valueOf(i));
if (!("ECHO: " + i).equals(result))
throw new RuntimeException("Result: " + result);
}
timer = System.currentTimeMillis() - timer;
System.out.println("SingleThread done. Time spend: " + timer + "\t Per: " + (1.0 * timer / TIMES));
} private static void multiThreadTest(int threadNum, int poolSize) throws Exception {
final CallablePool pool = new CallablePool("call proc_test(?)", poolSize); // 线程准备
Thread[] threads = new Thread[threadNum];
for (int i = 0; i < threads.length; i++) {
threads[i] = new Thread("T" + i) {
public void run() {
String name = this.getName() + "-";
long timer = System.currentTimeMillis();
for (int i = 0; i < TIMES; i++) {
String result = pool.call(name + i);
if (!("ECHO: " + name + i).equals(result))
throw new RuntimeException("Result: " + result);
}
timer = System.currentTimeMillis() - timer;
System.out.println(this.getName() + " done. Time spend: " + timer + "\t Per: "
+ (1.0 * timer / TIMES));
}
};
} long timer = System.currentTimeMillis();
// 全部启动
for (int i = 0; i < threads.length; i++) {
threads[i].start();
} // 等待结束
for (int i = 0; i < threads.length; i++) {
threads[i].join();
}
timer = System.currentTimeMillis() - timer;
System.out.println("MulitThread done. Time spend: " + timer + "\t ThreadNum: " + threadNum + "\t PoolSize: "
+ poolSize);
} // Reuse CallableStatement: Time spend: 529 Per: 0.529
// Every time create new: Time spend: 671 Per: 0.671
private static void simpleRoundTest() throws Exception {
Connection con = openConnection();
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall("call proc_test(?)");
long s = System.currentTimeMillis(); for (int i = 0; i < TIMES; i++) {
cstmt.close();
cstmt = (OracleCallableStatement) con.prepareCall("call proc_test(?)");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setString(1, String.valueOf(i));
cstmt.execute();
System.out.print(cstmt.getString(1) + " ");
} long e = System.currentTimeMillis() - s;
System.out.println("\nTime spend: " + e + "\t Per: " + (1.0 * e / TIMES)); con.close();
}
}class CallablePool {
private List<OracleCallableStatement> pool = new Vector<OracleCallableStatement>(); private static final int WAIT = 200; // 等待可用Callable对象:100ms
private static final int TIMEOUT = 5000; // 等待超时:1秒 public CallablePool(String sql, int size) throws SQLException {
// 准备好所有的对象,池化数量主要依据并发线程数来设定,如果是单线程就没必要池化了。
long timer = System.currentTimeMillis();
for (int i = 0; i < size; i++) {
Connection con = TestCall.openConnection();
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall(sql);
cstmt.registerOutParameter(1, Types.VARCHAR);
pool.add(cstmt);
}
timer = System.currentTimeMillis() - timer;
System.out.println("Pool prepared, size:" + size + ", spend: " + timer + "ms");
} public String call(Object... params) {
OracleCallableStatement cstmt = null;
long current = System.currentTimeMillis();
while (cstmt == null) {
try {
cstmt = pool.remove(0);
// 从完整性而言,此时需要检查该cstmt还是否有效(比如数据库连接可能断了),然后重建该cstmt
} catch (IndexOutOfBoundsException ex) {
// 说明没有任何可用对象了
}
if (cstmt == null) {
try {
System.out.print('.');
synchronized (this) {
this.wait(WAIT); // 等待通知池中有可用对象
}
} catch (InterruptedException e) {
e.printStackTrace();
}
}
if ((System.currentTimeMillis() - current) > TIMEOUT) {
return null;
}
}
try {
for (int i = 0; i < params.length; i++) {
cstmt.setObject(i + 1, params[i]);
}
cstmt.execute();
return cstmt.getString(1);
} catch (SQLException e) {
// 发生异常,完整性而言,此时需要检查该cstmt还是否有效(比如数据库连接可能断了),然后重建该cstmt
e.printStackTrace();
return null;
} finally {
pool.add(cstmt); // 将其返还池,供下次使用。
synchronized (this) {
this.notifyAll(); // 通知其它可能阻塞的线程,池中有可用对象了
}
}
} public void dispose() {
while (pool.size() > 0) {
try {
OracleCallableStatement cstmt = pool.remove(0);
Connection cn = cstmt.getConnection();
cn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}客观地说,我还是不建议自己来做Callable的Pool,风险太高了。
按照您的说法,把prepareCall放入连接池就可以了?0.6豪秒是可以接受的,如果能把prepareCall放入连接池我就放心了,以后自己都可以这么学着做了,感谢!比较奇怪的是如下代码(prepareCall没有放入连接池),也只是第一次prepareCall耗时90豪秒,第2-100都只有0.X豪秒
for (int i=1; i<=100; i++)
{
prepareCall
setString()
execute()
}
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall("call proc_test(?)");
// 然后才开始计时
long s = System.currentTimeMillis();for (int i = 0; i < TIMES; i++) {
cstmt.close();
cstmt = (OracleCallableStatement) con.prepareCall("call proc_test(?)"); // 每次都重建
cstmt.setString(1, String.valueOf(i));
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.execute();
System.out.print(cstmt.getString(1) + " ");
}
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall("call proc_test(?)");是否是可以放在连接池的?如果这样的话,就行了
不可以,连接池只池化数据库连接,也就是Connection。如果要池化CallableStatement,就自己编写,类似于我在20楼提供的 CallablePool 这个类。
这个可不是循环哦,,是客户端一次次的传参数进来,每次都prepareCall,每次90豪秒?
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;import oracle.jdbc.OracleCallableStatement;public class TestCall {
private static int TIMES = 1000; private static String URL = "jdbc:oracle:thin:@192.168.234.128:1521:redhat5";
private static String USER = "zenki";
private static String PASS = "zenki"; public static Connection openConnection() throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
return DriverManager.getConnection(URL, USER, PASS);
} public static void main(String[] args) throws Exception {
Connection con = openConnection();
String sql = "call proc_test(?)";
// 预跑
SimpleCaller.callProc(con, sql, "");
// 性能测试
long timer = System.currentTimeMillis();
for (int i = 0; i < TIMES; i++) {
SimpleCaller.callProc(con, sql, String.valueOf(i));
}
timer = System.currentTimeMillis() - timer;
System.out.println("\nTime spend: " + timer + "\t Per: " + (1.0 * timer / TIMES)); con.close();
}
}class SimpleCaller {
public static String callProc(Connection con, String sql, String param) throws SQLException {
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall(sql);
try {
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setString(1, String.valueOf(param));
cstmt.execute();
return cstmt.getString(1);
} finally {
cstmt.close();
}
}
}运行结果:
Time spend: 674 Per: 0.674
为什么我的OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall(sql);第一次执行要 90豪秒
而你的OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall(sql);
第一次执行也非常快,小于 0.X豪秒
你的代码里有个“预跑”功能,这个要90豪秒
我说的就是这个 90 豪秒,没法避免。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;import oracle.jdbc.OracleCallableStatement;public class TestCall {
private static int TIMES = 1000;
private static int THREAD_NUM = 100; private static String URL = "jdbc:oracle:thin:@192.168.234.128:1521:redhat5";
private static String USER = "zenki";
private static String PASS = "zenki"; public static Connection openConnection() throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
return DriverManager.getConnection(URL, USER, PASS);
} public static void main(String[] args) throws Exception {
Connection con = openConnection();
final String sql = "call proc_test(?)"; // 预跑
SimpleCaller.callProc(con, sql, ""); // 单线程性能测试
long timer = System.currentTimeMillis();
for (int i = 0; i < TIMES; i++) {
SimpleCaller.callProc(con, sql, String.valueOf(i));
}
timer = System.currentTimeMillis() - timer;
System.out.println("\nTime spend: " + timer + "\t Per: " + (1.0 * timer / TIMES)); // 多线程准备
Thread[] threads = new Thread[THREAD_NUM];
for (int i = 0; i < threads.length; i++) {
threads[i] = new Thread("T" + i) {
public void run() {
try {
Connection con = openConnection();
for (int i = 0; i < TIMES; i++) {
SimpleCaller.callProc(con, sql, String.valueOf(i));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
};
} // 多线程性能测试
timer = System.currentTimeMillis();
for (int i = 0; i < threads.length; i++) {
threads[i].start();
}
for (int i = 0; i < threads.length; i++) {
threads[i].join();
}
timer = System.currentTimeMillis() - timer;
System.out.println("MulitThread done. Time spend: " + timer + "\t Per: " + (1.0 * timer / TIMES / THREAD_NUM)
+ "\t ThreadNum: " + THREAD_NUM); }
}class SimpleCaller {
public static String callProc(Connection con, String sql, String param) throws SQLException {
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall(sql);
try {
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setString(1, String.valueOf(param));
cstmt.execute();
return cstmt.getString(1);
} finally {
cstmt.close();
}
}
}测试结果:
Time spend: 672 Per: 0.672
MulitThread done. Time spend: 20845 Per: 0.20845 ThreadNum: 100
因为我是4核的,所以并发下单次的平均时间会更低。
如果楼主还不信的话,就把Connection简单池化,然后用JSP做性能测试吧。
客户端调用的时候,程序如果是main函数吗?这样的话,每次都要预跑了?
“就把Connection简单池化”就是你上面写的代码?可靠么?
如果用于应用?因为听你说的好像风险较大?
能再快0.1豪秒当然最好了,0.1豪秒也是很可贵的。当然不能快0.1豪秒也只能算了。如果考虑到大的风险
把Connection池化,不是我上面写的代码,我只是示意;请使用中间件提供的JDBC池。如果没有,就用c3p0也行。要再快1毫秒,就得把Callable池化,类似我20楼写的 CallablePool 类;但这个类并不完善,风险还是较多的高;感觉你的技术水平还不太足以掌控这个风险,建议不要做。