没有涉及多张表 drop procedure p_single; create or replace procedure p_single(sid in string,starttime in string,endtime in string, user in string,sub in string) as begin update single set start_time =starttime ,end_time=endtime where userName=user and subject=sub; IF SQL%NOTFOUND THEN INSERT INTO single(id,start_time,end_time,userName,subject) VALUES(sid,starttime,endtime,user,sub);END IF;commit; end;
先在系统起来后,获取所有的表中记录 HashSet existedSet在有新的更新或者插入请求s(最好也是批处理)到的时候,先从内存中比较是否已经存在 if (request in existedSet) { add to update list; } else { add to insert list; }
涉及到多张表吗?
是不是用到了blob或者是clob了,怎么会那么大?
drop procedure p_single;
create or replace procedure p_single(sid in string,starttime in string,endtime in string,
user in string,sub in string) as
begin
update single set start_time =starttime ,end_time=endtime where userName=user and subject=sub;
IF SQL%NOTFOUND THEN
INSERT INTO single(id,start_time,end_time,userName,subject)
VALUES(sid,starttime,endtime,user,sub);END IF;commit;
end;
String sqlString = "INSERT INTO single ( "
+ " id, start_time, Tm, end_time, userName"
+ " subject )"
+ " VALUES ( "
+ "?, ?, ?, ?, ?, "
+ "? ) ";pstmt = conn.prepareStatement(sqlString);
Iterator it = list.iterator();
while (it.hasNext()) {
...
pstmt.addBatch();
}pstmt.executeBatch();同时insert多的表少建索引。select或者update的多的表则需要增加索引,所以一定要想办法把那2个操作分开
如果量不是很大的话,自己维护一个记录主键的cache,明确地调用update或者insert
你说说,是不是IF SQL%NOTFOUND 的条件不成立的情况居多?
建议拆分表!!
----------------------------------
如果拆分表,就要联表查询生成hash值是不是更慢?
是的,使update居多,就算一条语句0.05秒还是不可接受,所以我想有什么批处理或者入参为数组,但是又不定长,而且加入索引是不是会增加插入成本?
能给我看看你的部分代码马?
我用的也是oracle,连接池是dhcp的,最大连接数500,用一个线程察看list,sleep300,list的更新达到0.2s,果真如你所说数据库操作是时快时慢,又是达到秒级
自己维护一个记录主键的cache,明确地调用update或者insert
-----------能够告诉我具体的做法吗?
try { con = DbManager.getConnection();
long t11 = System.currentTimeMillis();
pstmt = con.prepareCall("{call p_single(?,?,?,?,?)}");
for(Iterator it=listDatas.iterator();it.hasNext();)
{
SerialCode sc=new SerialCode();
UserDataInfo UserData = (UserDataInfo) it.next();
pstmt.setString(1, (sc.getSerial("SIG")));
pstmt.setString(2, ((Single)UserData).getStartTime());
pstmt.setString(3, ((Single)UserData).getEndType());
pstmt.setString(4, ((Single)UserData).getSubject());
pstmt.setString(5, ((Single)UserData).getUserName());
pstmt.addBatch();
}
pstmt.executeUpdate();
long t12 = System.currentTimeMillis();
System.out.println(t12-t11+"now stest "); }
catch( SQLException sqle ) {
sqle.printStackTrace();
throw sqle;
}
finally {
try { pstmt.close(); }
catch (Exception e) { }
try { con.close(); }
catch (Exception e) { e.printStackTrace(); }
}
现在想用批处理,但是没用过,出现错误
java.sql.SQLException: 批处理中出现错误: batch must be either executed or cleared
HashSet existedSet在有新的更新或者插入请求s(最好也是批处理)到的时候,先从内存中比较是否已经存在
if (request in existedSet) {
add to update list;
} else {
add to insert list;
}
shared_pool_size = 52428800,而且数据库建了索引后更慢了,0.46s,