Java 取数据 循环效率变低

me阿木 2013-12-19 07:10:06
问题描述：数据总量是12万条。通过Java循环分批取出，每次取300条并进行处理。现在发现循环会越跑越慢，每个循环的执行时间一开始是2秒，然后很快就跃升到十几秒，并且执行时间一直在增长。
下面是我测试的循环时间输出，截止到我贴出来时，循环执行时间已经增长到18秒


Thu Dec 19 18:43:42 CST 2013

=Thu Dec 19 18:43:44 CST 2013

=Thu Dec 19 18:43:47 CST 2013

=Thu Dec 19 18:43:49 CST 2013

=Thu Dec 19 18:43:51 CST 2013

=Thu Dec 19 18:43:53 CST 2013

=Thu Dec 19 18:43:55 CST 2013

=Thu Dec 19 18:43:57 CST 2013

=Thu Dec 19 18:44:00 CST 2013

=Thu Dec 19 18:44:02 CST 2013

=Thu Dec 19 18:44:04 CST 2013

=Thu Dec 19 18:44:06 CST 2013

=Thu Dec 19 18:44:08 CST 2013

=Thu Dec 19 18:44:10 CST 2013

=Thu Dec 19 18:44:12 CST 2013

=Thu Dec 19 18:44:14 CST 2013

=Thu Dec 19 18:44:16 CST 2013

=Thu Dec 19 18:44:21 CST 2013

=Thu Dec 19 18:44:25 CST 2013

=Thu Dec 19 18:44:30 CST 2013

=Thu Dec 19 18:44:35 CST 2013

=Thu Dec 19 18:44:39 CST 2013

=Thu Dec 19 18:44:44 CST 2013

=Thu Dec 19 18:44:49 CST 2013

=Thu Dec 19 18:44:55 CST 2013

=Thu Dec 19 18:45:00 CST 2013

=Thu Dec 19 18:45:08 CST 2013

=Thu Dec 19 18:45:16 CST 2013

=Thu Dec 19 18:45:23 CST 2013

=Thu Dec 19 18:45:32 CST 2013

=Thu Dec 19 18:45:46 CST 2013

=Thu Dec 19 18:46:00 CST 2013

=Thu Dec 19 18:46:15 CST 2013

=Thu Dec 19 18:46:30 CST 2013

=Thu Dec 19 18:46:43 CST 2013

=Thu Dec 19 18:46:58 CST 2013

=Thu Dec 19 18:47:12 CST 2013

=Thu Dec 19 18:47:26 CST 2013

=Thu Dec 19 18:47:41 CST 2013

=Thu Dec 19 18:47:56 CST 2013

=Thu Dec 19 18:48:10 CST 2013

=Thu Dec 19 18:48:25 CST 2013

=Thu Dec 19 18:48:40 CST 2013

=Thu Dec 19 18:48:54 CST 2013

=Thu Dec 19 18:49:09 CST 2013

=Thu Dec 19 18:49:24 CST 2013

=Thu Dec 19 18:49:39 CST 2013

=Thu Dec 19 18:49:54 CST 2013

=Thu Dec 19 18:50:09 CST 2013

=Thu Dec 19 18:50:24 CST 2013

=Thu Dec 19 18:50:39 CST 2013

=Thu Dec 19 18:50:54 CST 2013

=Thu Dec 19 18:51:09 CST 2013

=Thu Dec 19 18:51:24 CST 2013

=Thu Dec 19 18:51:39 CST 2013

=Thu Dec 19 18:51:54 CST 2013

=Thu Dec 19 18:52:10 CST 2013

=Thu Dec 19 18:52:25 CST 2013

=Thu Dec 19 18:52:40 CST 2013

=Thu Dec 19 18:52:56 CST 2013

=Thu Dec 19 18:53:12 CST 2013

=Thu Dec 19 18:53:27 CST 2013

=Thu Dec 19 18:53:43 CST 2013

=Thu Dec 19 18:53:59 CST 2013

=Thu Dec 19 18:54:15 CST 2013

=Thu Dec 19 18:54:31 CST 2013

=Thu Dec 19 18:54:47 CST 2013

=Thu Dec 19 18:55:02 CST 2013

=Thu Dec 19 18:55:18 CST 2013

=Thu Dec 19 18:55:34 CST 2013

=Thu Dec 19 18:55:50 CST 2013

=Thu Dec 19 18:56:06 CST 2013

=Thu Dec 19 18:56:22 CST 2013

=Thu Dec 19 18:56:38 CST 2013

=Thu Dec 19 18:56:54 CST 2013

=Thu Dec 19 18:57:10 CST 2013

=Thu Dec 19 18:57:26 CST 2013

=Thu Dec 19 18:57:42 CST 2013

=Thu Dec 19 18:57:58 CST 2013

=Thu Dec 19 18:58:14 CST 2013

=Thu Dec 19 18:58:30 CST 2013

=Thu Dec 19 18:58:46 CST 2013

=Thu Dec 19 18:59:03 CST 2013

=Thu Dec 19 18:59:19 CST 2013

=Thu Dec 19 18:59:35 CST 2013

=Thu Dec 19 18:59:51 CST 2013

=Thu Dec 19 19:00:08 CST 2013

=Thu Dec 19 19:00:24 CST 2013

=Thu Dec 19 19:00:41 CST 2013

=Thu Dec 19 19:00:57 CST 2013

=Thu Dec 19 19:01:14 CST 2013

=Thu Dec 19 19:01:31 CST 2013

=Thu Dec 19 19:01:48 CST 2013

=Thu Dec 19 19:02:05 CST 2013

=Thu Dec 19 19:02:21 CST 2013

=Thu Dec 19 19:02:38 CST 2013

=Thu Dec 19 19:02:55 CST 2013

=Thu Dec 19 19:03:11 CST 2013

=Thu Dec 19 19:03:28 CST 2013

=Thu Dec 19 19:03:44 CST 2013

=Thu Dec 19 19:04:01 CST 2013

=Thu Dec 19 19:04:18 CST 2013

=Thu Dec 19 19:04:35 CST 2013

=Thu Dec 19 19:04:52 CST 2013

=Thu Dec 19 19:05:08 CST 2013

=Thu Dec 19 19:05:25 CST 2013

=Thu Dec 19 19:05:42 CST 2013

=Thu Dec 19 19:05:59 CST 2013

=Thu Dec 19 19:06:16 CST 2013

=Thu Dec 19 19:06:34 CST 2013

=Thu Dec 19 19:06:51 CST 2013

=Thu Dec 19 19:07:08 CST 2013

=Thu Dec 19 19:07:25 CST 2013

=Thu Dec 19 19:07:41 CST 2013

=Thu Dec 19 19:07:58 CST 2013

=Thu Dec 19 19:08:16 CST 2013

=Thu Dec 19 19:08:33 CST 2013

=Thu Dec 19 19:08:50 CST 2013

=Thu Dec 19 19:09:07 CST 2013

=Thu Dec 19 19:09:24 CST 2013

=Thu Dec 19 19:09:41 CST 2013

=Thu Dec 19 19:09:58 CST 2013

=Thu Dec 19 19:10:15 CST 2013

=Thu Dec 19 19:10:33 CST 2013

把代码也贴出来一下
数据库连接


public class BaseDao {



	protected DataSourceManager dataSourceManager;

	protected Connection conn;

	protected PreparedStatement pstmt;

	protected Statement stmt;

	protected Savepoint savepoint;

	protected ResultSet rs;



	/**

	 * 执行查询操作

	 * 

	 * @param sql

	 *            要执行的查询语句

	 * @return

	 */

	protected ResultSet doQuery(String sql) {

		if(null == dataSourceManager){

			dataSourceManager = DataSourceManager.getInstance();

		}

		if(null == conn){

			conn = dataSourceManager.getConnection();

		}

		try {

			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();

		} catch (Exception e) {

			e.printStackTrace();

			System.out.println("###BaseDao - 执行查询语句出现异常！");

		}

		

		return rs;

	}



	/**

	 * 关闭数据库连接

	 */

	public void closeAll() {

		try {

			if (pstmt != null) {

				pstmt.close();

				pstmt = null;

			}

			if (rs != null)

				rs.close();

			if (conn != null) {

				conn.close();

				conn = null;

			}

		} catch (SQLException e) {

			e.printStackTrace();

		}

	}

}

执行查询


	private List<TrsVO> doTrsQueryWithoutClose(String sql) 

									throws Exception {

		List<TrsVO> trsList = new ArrayList<TrsVO>();

		try {

			rs = doQuery(sql);

			TrsVO trsVo;

			while (rs.next()) {

				trsVo = new TrsVO();



				trsVo.setNo(StringUtils.checkNull(rs.getString("no")));

				trsVo.setCorpName(StringUtils.checkNull( rs.getString("corpName") ));

				trsVo.setProdName(StringUtils.checkNull( rs.getString("prodName") ));

				trsVo.setAddress(StringUtils.checkNull( rs.getString("address") ));

				trsVo.setProAdd(StringUtils.checkNull( rs.getString("proadd") ));

				trsVo.setTestWay(StringUtils.checkNull( rs.getString("testWay") ));

				trsVo.setStartdate(StringUtils.checkNull( rs.getString("startdate") ));

				trsVo.setDeadline(StringUtils.checkNull( rs.getString("deadline") ));

				trsVo.setAgencies(StringUtils.checkNull( rs.getString("agencies") ));

				

				trsVo.setBatch(StringUtils.checkNull( rs.getString("batch") ));

				trsVo.setStatus(StringUtils.checkNull( rs.getString("status") ));

				trsVo.setChangeDate(StringUtils.checkNull( rs.getString("changeDate") ));

				trsVo.setImportDate(StringUtils.checkNull( rs.getString("importDate") ));

				trsVo.setRemarks(StringUtils.checkNull( rs.getString("remarks") ));

				trsVo.setWriteOffTime(StringUtils.checkNull( rs.getString("writeOffTime") ));

				trsVo.setWriteoffDepart(StringUtils.checkNull( rs.getString("writeOffDepart") ));



				trsList.add(trsVo);

			}

		} catch (Exception e) {

			e.printStackTrace();

			System.out.println("###TRSDAO - 查询获取信息出错");

			throw e;

		} 



		return trsList;

	}





	public List<TrsVO> getAllValid(int start, int end) throws Exception{

		List<TrsVO> trsList = null;

		String sql = "select * from "

				   + "(select t.*, row_number() over(order by id) rn "

				   + "from " + tableName + " t "

				   + "where status = 3 and deadline >= add_months(sysdate, -6)"

				   + ")"

				   + "where rn between "+start+" and " + end;

		try{

			trsList = doTrsQuery(sql);

		}catch(Exception e){

			e.printStackTrace();

			System.out.println("###TrsDao -  分页获取全部有效信息出现错误！");

			throw e;

		}

		

		return trsList;

	}

循环处理




	private boolean doExport2(int flag, String filePath, boolean is_del){

		TrsDao dao = new TrsDao();

		List<TrsVO> trsList = null;

		TableCount tc = new TableCount();

		try{

			//创建TRS文件

			TxtUtils.creatTxtFile("", filePath);

			//获取记录总数

			tc.total = dao.getAllValidCount();

			

			//批量将信息写入文件

			while(tc.start < tc.total){

				tc.batch += 1; 

				tc = dealQueryCount(tc);

				

				trsList = dao.getAllValid(tc.start, tc.end) ;

				if(trsList.size()>0){

					printTrsInfo(trsList, filePath, is_del);

				}



				System.out.print("=");

			}

			

		}catch(Exception e){

			e.printStackTrace();

			return false;

		}finally{

			dao.closeAll();

		}

		

		return true;

	}





	protected void printTrsInfo(List<TrsVO> trsList, String filePath, boolean is_del) {

		

		try{

			TrsVO trs = null;

			StringBuffer sb = new StringBuffer();

			for (int i = 0; i < trsList.size(); i++) {

				trs = trsList.get(i);

				sb.append("<REC>");

				sb.append("\n<证书编号>=").append( trs.getNo() );

				sb.append("\n<企业名称>=").append( StringUtils.deleteWrap(trs.getCorpName()) );

				sb.append("\n<产品名称>=").append( StringUtils.deleteWrap(trs.getProdName()) );

				sb.append("\n<住所>=").append( StringUtils.deleteWrap(trs.getAddress()) );

				sb.append("\n");

				TxtUtils.writeTxtFile(filePath, sb.toString());

				sb.setLength(0);

			}

		}catch(Exception e){

			e.printStackTrace();

			System.out.println("###输出TRS信息出现异常：printTrsInfo("+filePath+")");

		}

	}



...全文
198 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
Defonds 2013-12-20
  • 打赏
  • 举报
 回复
会不会是 db 负载太大了，慢的时候，你去 db server 看看负载情况
me阿木 2013-12-20
  • 打赏
  • 举报
 回复
引用 1 楼 defonds 的回复:
好长。 建议， 可疑的语句加日志打印执行时间，看看哪里耗时最多
按照提供的方法测试了一下，发现效率变低主要是因为sql语句执行的时间变长了。 处理的时间并没有变长。能指点一下么？？ 添加测试的语句： 

			pstmt = conn.prepareStatement(sql);
			System.out.print("" + new Date());//执行的起始时间
			rs = pstmt.executeQuery();
			System.out.println(" --- " + new Date());//执行的结束时间
下面是测试的输出： 

=Fri Dec 20 09:07:28 CST 2013 --- Fri Dec 20 09:07:29 CST 2013
=Fri Dec 20 09:07:31 CST 2013 --- Fri Dec 20 09:07:31 CST 2013
=Fri Dec 20 09:07:33 CST 2013 --- Fri Dec 20 09:07:34 CST 2013
=Fri Dec 20 09:07:35 CST 2013 --- Fri Dec 20 09:07:36 CST 2013
=Fri Dec 20 09:07:37 CST 2013 --- Fri Dec 20 09:07:38 CST 2013
=Fri Dec 20 09:07:42 CST 2013 --- Fri Dec 20 09:07:42 CST 2013
=Fri Dec 20 09:07:44 CST 2013 --- Fri Dec 20 09:07:44 CST 2013
=Fri Dec 20 09:07:46 CST 2013 --- Fri Dec 20 09:07:47 CST 2013
=Fri Dec 20 09:07:48 CST 2013 --- Fri Dec 20 09:07:49 CST 2013
=Fri Dec 20 09:07:50 CST 2013 --- Fri Dec 20 09:07:51 CST 2013
=Fri Dec 20 09:07:52 CST 2013 --- Fri Dec 20 09:07:53 CST 2013
=Fri Dec 20 09:07:55 CST 2013 --- Fri Dec 20 09:07:55 CST 2013
=Fri Dec 20 09:07:57 CST 2013 --- Fri Dec 20 09:07:57 CST 2013
=Fri Dec 20 09:07:59 CST 2013 --- Fri Dec 20 09:07:59 CST 2013
=Fri Dec 20 09:08:01 CST 2013 --- Fri Dec 20 09:08:02 CST 2013
=Fri Dec 20 09:08:03 CST 2013 --- Fri Dec 20 09:08:07 CST 2013
=Fri Dec 20 09:08:08 CST 2013 --- Fri Dec 20 09:08:12 CST 2013
=Fri Dec 20 09:08:14 CST 2013 --- Fri Dec 20 09:08:17 CST 2013
=Fri Dec 20 09:08:19 CST 2013 --- Fri Dec 20 09:08:22 CST 2013
=Fri Dec 20 09:08:23 CST 2013 --- Fri Dec 20 09:08:27 CST 2013
=Fri Dec 20 09:08:28 CST 2013 --- Fri Dec 20 09:08:32 CST 2013
=Fri Dec 20 09:08:33 CST 2013 --- Fri Dec 20 09:08:37 CST 2013
=Fri Dec 20 09:08:39 CST 2013 --- Fri Dec 20 09:08:42 CST 2013
=Fri Dec 20 09:08:44 CST 2013 --- Fri Dec 20 09:08:48 CST 2013
=Fri Dec 20 09:08:49 CST 2013 --- Fri Dec 20 09:08:56 CST 2013
=Fri Dec 20 09:08:58 CST 2013 --- Fri Dec 20 09:09:04 CST 2013
=Fri Dec 20 09:09:06 CST 2013 --- Fri Dec 20 09:09:12 CST 2013
=Fri Dec 20 09:09:13 CST 2013 --- Fri Dec 20 09:09:20 CST 2013
=Fri Dec 20 09:09:22 CST 2013 --- Fri Dec 20 09:09:35 CST 2013
=Fri Dec 20 09:09:36 CST 2013 --- Fri Dec 20 09:09:49 CST 2013
=Fri Dec 20 09:09:50 CST 2013 --- Fri Dec 20 09:10:03 CST 2013
=Fri Dec 20 09:10:05 CST 2013 --- Fri Dec 20 09:10:17 CST 2013
=Fri Dec 20 09:10:19 CST 2013 --- Fri Dec 20 09:10:32 CST 2013
=Fri Dec 20 09:10:33 CST 2013 --- Fri Dec 20 09:10:46 CST 2013
=Fri Dec 20 09:10:48 CST 2013 --- Fri Dec 20 09:11:01 CST 2013
=Fri Dec 20 09:11:02 CST 2013 --- Fri Dec 20 09:11:15 CST 2013
=Fri Dec 20 09:11:18 CST 2013 --- Fri Dec 20 09:11:31 CST 2013
=Fri Dec 20 09:11:33 CST 2013 --- Fri Dec 20 09:11:46 CST 2013
=Fri Dec 20 09:11:47 CST 2013 --- Fri Dec 20 09:12:00 CST 2013
=Fri Dec 20 09:12:02 CST 2013 --- Fri Dec 20 09:12:16 CST 2013
=Fri Dec 20 09:12:18 CST 2013 --- Fri Dec 20 09:12:31 CST 2013
=Fri Dec 20 09:12:33 CST 2013 --- Fri Dec 20 09:12:47 CST 2013
=Fri Dec 20 09:12:49 CST 2013 --- Fri Dec 20 09:13:02 CST 2013
=Fri Dec 20 09:13:03 CST 2013 --- Fri Dec 20 09:13:17 CST 2013
=Fri Dec 20 09:13:19 CST 2013 --- Fri Dec 20 09:13:33 CST 2013
=Fri Dec 20 09:13:35 CST 2013 --- Fri Dec 20 09:13:48 CST 2013
=Fri Dec 20 09:13:50 CST 2013 --- Fri Dec 20 09:14:04 CST 2013
=Fri Dec 20 09:14:06 CST 2013 --- Fri Dec 20 09:14:20 CST 2013
=Fri Dec 20 09:14:22 CST 2013 --- Fri Dec 20 09:14:35 CST 2013
=Fri Dec 20 09:14:37 CST 2013 --- Fri Dec 20 09:14:51 CST 2013
=Fri Dec 20 09:14:53 CST 2013 --- Fri Dec 20 09:15:06 CST 2013
=Fri Dec 20 09:15:08 CST 2013 --- Fri Dec 20 09:15:22 CST 2013
Defonds 2013-12-19
  • 打赏
  • 举报
 回复
好长。 建议， 可疑的语句加日志打印执行时间，看看哪里耗时最多
Java版数据结构
Java数据结构20讲 第01讲 - 数组 第02讲 - 简单排序 第03讲 - 栈和队列 第04讲 - 链表 第05讲 - 双端链表和双向链表 第06讲 - 递归的应用 第07讲 - 递归的高级应用 第08讲 - 希尔排序 第09讲 - 快速排序 第10讲 - ...
java 嵌套for循环优化_Java优化for循环嵌套的高效率方法
前几天有人问过我一个问题，就是两个嵌套for循环执行效率的问题，问有什么好的办法替换。当时我想了想，实在想不起来，哎，惭愧！！！ 请教了答案，恍然大悟。比如：两个list中分别装有相同的对象数据。 list1中有3...
* java版HashMap，大数据量时效率很低，建议1000条数据以下使用
 * java版HashMap，大数据量时效率很低，建议1000条数据以下使用  */ function HashMap(){    var DEFAULT_CAPACITY=16;  var MAX_CAPACITY=2147483647;  var DEFAULT_LOAD_FACTOR=0.75;  /**********...
java for循环多次查询效率慢，怎么处理啊？
最外层循环必须先查出来，然后再去通过外层查询结果去分层查询。 使用时间 做过的更改测试： 1.减少查询出来的结果，发现查询时间并没有减少。估计访问数据库次数过多。 ...
java递归和循环
方案1：使用循环 方案2：使用递归 递归：递归的基本思想就是“自己调用自己”，一个使用递归技术的方法将会直接或者间接的调用自己。 StackOverflowError:当应用程序递归太深而发生堆栈溢出时，抛出该错误。 ...
Web 开发

81,092

社区成员

341,716

社区内容

发帖
与我相关
我的任务
社区描述
Java Web 开发
社区管理员
  • Web 开发社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章