insert语句执行速度慢应该怎么解决
答案:2 悬赏:10
解决时间 2021-01-07 09:56
- 提问者网友:沉默菋噵
- 2021-01-06 19:22
insert语句执行速度慢应该怎么解决
最佳答案
- 二级知识专家网友:天凉才是好个秋
- 2021-01-06 19:58
Oracle 的主键 是 sequence 生成的吗?若是,以下是解决方法:
1,用Oracle 的returning 语句。
PreparedStatement sta = conn.prepareStatement("INSERT INTO LOGGING VALUES (TESTSEQ.NEXTVAL,SYSDATE)returning id into ? ");
sta.execute();
ResultSet rset = sta.getResultSet();
while(rset.next())
{
int id = rset.getInt(1);
}
如果是既有传入值,又有返回值的话,应该:
PreparedStatement sta = conn.prepareStatement("BEGIN INSERT INTO LOGGING VALUES
(TESTSEQ.NEXTVAL,? )returning id into ?; END; ");
sta = conn.prepareCall(sqlText);
sta .setString(1, srqID);
sta .registerOutParameter(2, Types.NUMERIC);
sta .execute();
return sta .getLong(2);
或者用oracle的 OraclePreparedStatement
String sql = "insert into test values(1,?) returning id into ?";
OraclePreparedStatement pstt = (OraclePreparedStatement) conn.prepareStatement(sql);
pstt.setString(1, "xxx");
pstt.registerReturnParameter(2, OracleTypes.INTEGER);
pstt.executeUpdate();
ResultSet rset = pstt.getReturnResultSet(); // rest is not null
while (rset.next()) {
int id = rset.getInt(1);
System.out.println("Insert returnning: " + id);
}
1,用Oracle 的returning 语句。
PreparedStatement sta = conn.prepareStatement("INSERT INTO LOGGING VALUES (TESTSEQ.NEXTVAL,SYSDATE)returning id into ? ");
sta.execute();
ResultSet rset = sta.getResultSet();
while(rset.next())
{
int id = rset.getInt(1);
}
如果是既有传入值,又有返回值的话,应该:
PreparedStatement sta = conn.prepareStatement("BEGIN INSERT INTO LOGGING VALUES
(TESTSEQ.NEXTVAL,? )returning id into ?; END; ");
sta = conn.prepareCall(sqlText);
sta .setString(1, srqID);
sta .registerOutParameter(2, Types.NUMERIC);
sta .execute();
return sta .getLong(2);
或者用oracle的 OraclePreparedStatement
String sql = "insert into test values(1,?) returning id into ?";
OraclePreparedStatement pstt = (OraclePreparedStatement) conn.prepareStatement(sql);
pstt.setString(1, "xxx");
pstt.registerReturnParameter(2, OracleTypes.INTEGER);
pstt.executeUpdate();
ResultSet rset = pstt.getReturnResultSet(); // rest is not null
while (rset.next()) {
int id = rset.getInt(1);
System.out.println("Insert returnning: " + id);
}
全部回答
- 1楼网友:骨子里都是戏
- 2021-01-06 21:37
通常大数据插入的话都不会一条一条插入,使用bulk insert等方法。 如果是程序限制的话,一批一批的提交 BEGIN TRANSACTION INSERT … INSERT … INSERT … COMMIT TRANSACTION 另外去掉索引,把日志设为简单都能提高一点效率
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯
• 手机登qq时,显示手机磁盘不足,清理后重新登 |
• 刺客的套装怎么选啊? |