对按组织架构加载用户列表的程序性能的优化

组织架构是:  organization代表党委, branch代表党支部,  层级关系是  党委->党支部->用户. 

前端要把这个层级结构按树形加载出来全部用户列表,  结果加载缓慢, 等待的时间好像达到了十秒左右.

老杨查看审核了后端的代码如下所示:

			StringBuilder stringBuilder = new StringBuilder();
			List<Sys_Dictionary> orglist = userService.listByHQL("from Sys_Dictionary d where d.mcode='organization' and d.pcode='0' and d.isvalid='Y' order by d.orderby");
			stringBuilder.append("[");
			if (orglist.size() > 0) {
				for (Sys_Dictionary org : orglist) {
					stringBuilder.append("{");
					stringBuilder.append("\"id\":\"" + 0 + "\",");
					stringBuilder.append("\"text\":\"" + org.getName() + "\",");
					stringBuilder.append("\"children\":");

					List<Sys_Dictionary> branchlist = userService
							.listByHQL("from Sys_Dictionary d where d.mcode='org_branch' and d.pcode='" + org.getCode() + "' and d.isvalid='Y' and d.name in(select branch from Users group by branch) order by d.orderby");
					stringBuilder.append("[");
					if (branchlist.size() > 0) {
						for (Sys_Dictionary branch : branchlist) {
							stringBuilder.append("{");
							stringBuilder.append("\"id\":\"" + 0 + "\",");
							stringBuilder.append("\"text\":\"" + branch.getName() + "\",");
							stringBuilder.append("\"children\":");

							List<Users> userlist = userService.listByHQL("from Users u where u.organization='" +

							org.getName() + "' and u.branch='" + branch.getName() + "' and u.isvaild='Y' order by organization,branch");
							stringBuilder.append("[");
							if (userlist.size() > 0) {

								for (Users user : userlist) {
									stringBuilder.append("{");
									stringBuilder.append("\"id\":\"" + user.getId() + "\",");
									stringBuilder.append("\"text\":\"" + user.getName() +

									"\"");
									stringBuilder.append("},");
								}
								stringBuilder.setLength(stringBuilder.length() - 1);
							}
							stringBuilder.append("]");
							stringBuilder.append("}");
							stringBuilder.append(",");
						}
					}
					stringBuilder.setLength(stringBuilder.length() - 1);
					stringBuilder.append("]");
					stringBuilder.append("}");
					stringBuilder.append(",");
				}
				stringBuilder.setLength(stringBuilder.length() - 1);
				stringBuilder.append("]");

 

 看了代码就明白了, 代码的问题是在嵌套循环里多次执行数据库查询, 导致数据库查询这一开销比较高成本的执行次数过多, 经查看日志, 完成一次加载, 共发起执行了98次数据库查询操作   

对这个的优化其实是小菜一碟, 老杨重写代码如下, 数据库查询次数减到只有三次, 经测试前端的树结构加载速度是大大提高了,  

                        //written by Wooce Yang, 2016/1/19
			StringBuilder stringBuilder = new StringBuilder();
			List<Sys_Dictionary> orglist = userService.listByHQL("from Sys_Dictionary d where d.mcode='organization' and d.pcode='0' and d.isvalid='Y' order by d.orderby");
			List<Sys_Dictionary> branchlist = userService
					.listByHQL("from Sys_Dictionary d where d.mcode='org_branch' and d.isvalid='Y' order by d.orderby");
			List<Users> userlist = userService.listByHQL("from Users u where u.isvaild='Y' order by organization,branch");
			Map<String, Map<String,List<Users>> > jsonMap = new HashMap<>();
			for (Sys_Dictionary org : orglist) {
				Map<String,List<Users>> branchMap = new HashMap<>();
				for (Sys_Dictionary branch : branchlist) {
					if(branch.getPcode().equals(org.getCode())) {
						List<Users> uList = new ArrayList<Users>();
						for (Users user : userlist) {
							if(user.getOrganization()!=null && user.getOrganization().equals(org.getName()) && user.getBranch()!=null && user.getBranch().equals(branch.getName())) {
								uList.add(user);
							}
						}
						if(uList.size()>0)
							branchMap.put(branch.getName(),uList);
					}
				}
				if(branchMap.size()>0)
					jsonMap.put(org.getName(),branchMap);
			}

			stringBuilder.append("[");
			if (jsonMap.size() > 0) {
				for (Map.Entry<String, Map<String,List<Users>> > orgEntry: jsonMap.entrySet()) {
					stringBuilder.append("{");
					stringBuilder.append("\"id\":\"" + 0 + "\",");
					stringBuilder.append("\"text\":\"" + orgEntry.getKey() + "\",");
					stringBuilder.append("\"children\":");

					stringBuilder.append("[");
					Map<String,List<Users>> branchMap = orgEntry.getValue();
					if( branchMap.size()>0 ) {
						for (Map.Entry<String,List<Users>> branchEntry : branchMap.entrySet()) {
							stringBuilder.append("{");
							stringBuilder.append("\"id\":\"" + 0 + "\",");
							stringBuilder.append("\"text\":\"" + branchEntry.getKey() + "\",");
							stringBuilder.append("\"children\":");

							stringBuilder.append("[");
							List<Users> uList = branchEntry.getValue();
							if (uList.size() > 0) {
								for (Users user : uList) {
									stringBuilder.append("{");
									stringBuilder.append("\"id\":\"" + user.getId() + "\",");
									stringBuilder.append("\"text\":\"" + user.getName() +
											"\"");
									stringBuilder.append("},");
								}
								stringBuilder.setLength(stringBuilder.length() - 1);
							}
							stringBuilder.append("]");
							stringBuilder.append("}");
							stringBuilder.append(",");
						}
					}
					stringBuilder.setLength(stringBuilder.length() - 1);
					stringBuilder.append("]");
					stringBuilder.append("}");
					stringBuilder.append(",");
				}
				stringBuilder.setLength(stringBuilder.length() - 1);
				stringBuilder.append("]");