如下程序,调用一次存储过程要 400豪秒,没法接受,可以接受的是 0.5豪秒以内,谢谢
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;public class test
{
public static void main(String args[]) throws Exception
{DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection("jdbc:oracle:oci8:@test","user","pass");
long time1 = System.currentTimeMillis() ;
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall("{call testInPersons(?)}");
StructDescriptor sd = new StructDescriptor("PERSON", con);
Object[] personAttrs1 = new Object[] { "This is code11", "100",new NUMBER(32), 200};
STRUCT person1 = new STRUCT(sd, con, personAttrs1);
Object[] personAttrs2 = new Object[] { "This is code22", "aaa", new NUMBER(33), 100 };
STRUCT person2 = new STRUCT(sd, con, personAttrs2);
ArrayDescriptor ad = new ArrayDescriptor("person_table_type".toUpperCase(), con);
ARRAY persons = new ARRAY(ad, con, new STRUCT[] { person1, person2 });
cstmt.setARRAY(1, persons);
cstmt.execute();long time2 = System.currentTimeMillis() ;
System.out.println(time2-time1);
}
}
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;public class test
{
public static void main(String args[]) throws Exception
{DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection("jdbc:oracle:oci8:@test","user","pass");
long time1 = System.currentTimeMillis() ;
OracleCallableStatement cstmt = (OracleCallableStatement) con.prepareCall("{call testInPersons(?)}");
StructDescriptor sd = new StructDescriptor("PERSON", con);
Object[] personAttrs1 = new Object[] { "This is code11", "100",new NUMBER(32), 200};
STRUCT person1 = new STRUCT(sd, con, personAttrs1);
Object[] personAttrs2 = new Object[] { "This is code22", "aaa", new NUMBER(33), 100 };
STRUCT person2 = new STRUCT(sd, con, personAttrs2);
ArrayDescriptor ad = new ArrayDescriptor("person_table_type".toUpperCase(), con);
ARRAY persons = new ARRAY(ad, con, new STRUCT[] { person1, person2 });
cstmt.setARRAY(1, persons);
cstmt.execute();long time2 = System.currentTimeMillis() ;
System.out.println(time2-time1);
}
}
现在currentTimeMillis()显示要200豪秒我循环100次,要16秒,因此和currentTimeMillis()没啥关系了
◎ 不设置数据,直接执行cstmt.execute()?那么存储过程也没啥好干的,必然很快。
◎ 设置了数据,但计时只计算cstmt.execute(); ?那么意味着前面 StructDescriptor、STRUCT、ARRAY 等这几句话就消耗了400毫秒?
仅执行 cstmt.execute() 耗费1豪秒
前面的话了400豪秒
从目前主流的测试情况来看,基本都是Thin更快。Anyway,如果你已经测试过,那就没啥好细化的了。无非是看能否进一步挖掘,这400毫秒究竟主要是消耗在这几句话中哪些了。
如果最终啥招都不行了,就最后一招:直接传一个大字符串进去(类似JSON或更简单的固定分割符方式),存储过程自行拆解该字符串。
而字符串的消耗时间是相对稳定的,所以才提出创建字符串。
另一种毛招是直接把数据写入临时表,然后存储过程从临时表中处理。用batchExecute的话,写数据的性能倒是非常高。总的来说,要达到0.5毫秒搞定,恐怕远没有这么乐观。