使用jdbc连接数据库查询时速度太慢?应该是什么情况?
代码的作用是根据查询出来的条件更新表的一个字段内容
前50W数据跑的都很快,基本是1秒钟处理更新4条左右
但是随后就出问题了,基本上几秒钟更新一条数据
我现在只能通过重启oracle数据库解决,这样只能维持30分钟左右,之后查询和更新效率就会急速下降。
下面附上我的代码:
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
public class BusStationMark {
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
Connection conn = null;
Statement stat = null;
//PreparedStatement stat = null;
ResultSet result = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.1.163:1521:ORCL";
String rootname = "uestcdengww";
String password = "B1611b1611";
conn = DriverManager.getConnection(url, rootname, password);
//stat = conn.createStatement();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
long item = 1;
int l_timeinterval = 0;
int l_nowinterval = 100;
int l_busstation = 0;
int stationnum = 0;
String l_lineno = "";
String table_name = "";
Scanner in = new Scanner(System.in);
System.out.println("输入表名 >");
table_name = in.nextLine();
System.out.println("输入开始位置 >");
item = in.nextInt();
if (item <= 0) {
item = 1;
}
String sql = "";
while (true) {
stat = conn.createStatement();
sql = "select NVL(TIMEINTERVAL,-1) as TIME_INTERVAL,LINENO,BUSNO from "
+ table_name + " where ITEM = " + item;
result = stat.executeQuery(sql);
if (result.next()) {
l_timeinterval = result.getInt("TIME_INTERVAL");
l_lineno = result.getString("LINENO");
} else {
break;
}
// if ((item % 10000) == 0) {
// System.out.println("当前线路 > " + l_lineno + ", 当前Item >" + item);
// }
if (item == 1 || l_timeinterval < 0) {
// //查询线路对应的阈值
//
System.out.println("当前时间 > " + df.format(new Date())
+", 当前线路 > " + l_lineno + ", 当前Item >" + item);
FileWriter writer = new FileWriter("record.txt", true);
writer.write("当前时间 > " + df.format(new Date())
+", 当前线路 > " + l_lineno + ", 当前Item >" + item +"\n");
writer.close();
l_busstation = 1;
sql = "update " + table_name + " set STATIONNO = "
+ l_busstation + " where ITEM = " + item;
stat.execute(sql);
sql = "select station_num from stationnum where lineno = '"
+ l_lineno + "'";
result = stat.executeQuery(sql);
while (result.next()) {
stationnum = result.getInt("station_num");
}
} else {
if (stationnum == 0) {
item++;
continue;
}
if (l_timeinterval >= l_nowinterval) {
l_busstation += l_timeinterval / l_nowinterval;
if (l_busstation >= stationnum) {
l_busstation = 1;
}
}
sql = "update " + table_name + " set STATIONNO = "
+ l_busstation + " where ITEM = " + item;
stat.execute(sql);
}
result.close();
stat.close();
item++;
}
// 断开数据库连接
result.close();
stat.close();
conn.close();
}
}