我用的是Sql server数据库,表大约有150万条纪录
在Java程序中使用Sql语句更新表中的一条纪录,要花超过30秒的时间。但使用查询分析器执行该语句,在1秒内即可执行完毕。请大家指教!
解决方案 »
- !!!!!线程join方法的timeout的理解!!!
- java得到14天前和14天后日期,在线等
- 请问错在哪里呢?
- 请问一个页面正则表达式
- paint和paintComponent做什么用的,有什么区别?
- 请大家帮帮忙,一个小问题
- 在线等!这段代码到底有什么问题??????????
- 我是java的初学者,谁能告诉我什么书最好,谢谢
- 各位前辈,关于java命令的问题/
- 誰能告訴我JBuilder4 的Serial No & Key
- Log4j用JdbcAppender碰到的问题
- log4j 日志请求中的DEBUG、INFO、WARN、ERROR和FATAL五5个级别 ,它们起个什么作用啊,不都是向外输出信息吗?它们作用有什么不同吗
1.是不是你CODE里面还有别的逻辑处理?如果是单改的话速度肯定差不了这么多.
2.看看是不是内存不够,IDE运行起来缓慢?
pstmt = conn.prepareStatement(sql);....//参数赋值pstmt.executeUpdate(); //这个语句需要30多秒才返回注:t_stat_hour_user对user_mobile+USDDPortal+time_begin建立了唯一索引。
private void updateStatTable(Req2DBReq req)
{
String sql = "update t_stat_hour_user set FNum = FNum + 1 where user_mobile = ? and USSDPortal = ? and time_begin = ? ";
Connection conn = null;
PreparedStatement pstmt = null; try
{
Date now = new Date(); conn = DBConnectionPool.getInstance().getConnection();
pstmt = conn.prepareStatement(sql); GregorianCalendar timeBegin = null; timeBegin = new GregorianCalendar();
timeBegin.setTime(now);
timeBegin.set(GregorianCalendar.MINUTE, 0);
timeBegin.set(GregorianCalendar.SECOND, 0);
timeBegin.set(GregorianCalendar.MILLISECOND, 0);
pstmt.setString(1, req.getMobile());
pstmt.setString(2, req.getUSSDPortal());
pstmt
.setTimestamp(3, new Timestamp(timeBegin.getTime().getTime()));
pstmt.executeUpdate();
}
catch (SQLException e)
{
DsmpProxyApp.getLog().error(
"数据库操作错误(Req2DBHandler):" + e.getMessage());
}
finally
{
DBTools.close(conn, pstmt, null);
conn = null;
pstmt = null;
} }
where user_mobile = '8613910866967' and USSDPortal = '138*11' and time_begin = cast('2006-12-21 09:00:00' as datetime)
{
String sql = "update t_stat_hour_user set FNum = FNum + 1 " +
"where user_mobile = '8613910866967' and USSDPortal = '138*11' " +
"and time_begin = cast('2006-12-21 09:00:00' as datetime)";
Connection conn = null;
PreparedStatement pstmt = null; try
{
Date now = new Date(); conn = DBConnectionPool.getInstance().getConnection();
pstmt = conn.prepareStatement(sql); pstmt.executeUpdate();
}
catch (SQLException e)
{
DsmpProxyApp.getLog().error(
"数据库操作错误(Req2DBHandler):" + e.getMessage());
}
finally
{
DBTools.close(conn, pstmt, null);
conn = null;
pstmt = null;
} }
//'8613910866967' 但愿是假号码
1)你执行的读表记录的语句可能读的是服务器内存cache里面的值,这时由于没有实际访问硬盘(或磁盘阵列),速度非常快;而你update表记录时,由于由实际的访问硬盘和写操作的时间,于是速度就相差很大了。
可以考虑引入新的缓存管理技术,一般的公司在做项目时,这部分都是用现成的应用服务器或DBMS提供的,其性能的好坏无法得知。
2)看看你的连接池的实现,连接池的性能好坏很是影响对数据的操作速度。
这样写也好..
就是用到了工厂模式..还有着只是一小部分的内容:能看懂的...
package com.sccp.dbUtil;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Properties;
import java.util.Vector;public class ConnectionPool { private static String SQL_CONFIG = "SqlConfig.cfg"; private static final String driver = "driver"; private static final String connection = "connection"; private static final String name = "name"; private static String password = "password"; private static Properties property = SqlConfig.sql_Init(SQL_CONFIG); private final int MAX_CONNECTION = 4; private Vector vectPool = new Vector(); private String dataBaseURL; private static ConnectionPool conPool; ConnectionPool() {
}; private static Hashtable poolSources = new Hashtable(); private int innum = 0; private int outnum = 0; private int closenum = 0; /*
* 返回主数据库链接池
* */
public synchronized static ConnectionPool getConnectionPool() {
if (conPool == null)
conPool = new ConnectionPool();
return conPool;
} /**/
public synchronized static ConnectionPool getConnectionPool(
String prefixname) {
ConnectionPool pool = (ConnectionPool) poolSources.get(prefixname);
if (pool == null) {
pool = new ConnectionPool();
pool.initDriver();
try { } catch (Exception ex) {
}
poolSources.put(prefixname, pool);
}
return pool;
} /*
* 得到数据库的连接
* */
public void initDriver() {
try {
Class.forName((String) property.get(driver));
System.out.println(property.get(driver));
System.out.println(property.get(driver));
} catch (Exception e) {
}
} public synchronized Connection getConnection() {
Connection con = null;
Statement stmt = null;
if (vectPool.size() == 0) {
try {
this.outnum++;
String url = property.getProperty(connection);
System.out.println(url);
System.out.println("0000000000000000000000000");
System.out.println((String) property.getProperty(name));
System.out.println((String) property.getProperty(password));
System.out.println("11111111111111111111111111111111");
con = DriverManager.getConnection(url, (String) property
.getProperty(name), (String) property
.getProperty(password));
System.out.println("connection is success!"); con.setAutoCommit(true);
} catch (SQLException sqle) {
sqle.printStackTrace();
}
} else { con = (Connection) vectPool.remove(0);
this.outnum++;
this.innum--;
}
try {
stmt = con.createStatement();
} catch (SQLException sqle) {
try {
con.close();
} catch (SQLException sqle2) {
sqle2.printStackTrace();
}
con = getConnection();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqle2) {
sqle2.printStackTrace();
}
}
}
return con;
} /*
* 释放数据库连接
* */
public synchronized void poolConncetion(Connection con) {
if (vectPool.size() < MAX_CONNECTION) { if (con == null)
return;
vectPool.addElement(con);
this.innum++;
this.outnum--;
} else {
try {
con.close();
this.closenum++;
} catch (Exception e) {
}
}
} /*
* 率新数据库连接池
* */
public void refrech() {
for (int i = 0; i < vectPool.size(); i++) {
Connection con = (Connection) vectPool.get(i);
try {
con.close();
} catch (Exception e) {
continue;
}
}
vectPool.removeAllElements();
} /**
* @return Returns the dataBaseURL.
*/
public String getDataBaseURL() {
return dataBaseURL;
} /**
* @param dataBaseURL The dataBaseURL to set.
*/
public void setDataBaseURL(String dataBaseURL) {
this.dataBaseURL = dataBaseURL;
} /**
* @return Returns the password.
*/
public String getPassword() {
return password;
} /**
* @param password The password to set.
*/
public void setPassword(String password) {
this.password = password;
} /**
* @return Returns the name.
*/
public String getName() {
return name;
} public static void main(String args[]) {
ConnectionPool conp = new ConnectionPool();
conp.initDriver();
conp.getConnection();
}}
在SQL里面它就是很慢的,,
大家可以试一下