英文:
Looking for the optimization of the below code
问题
以下是翻译好的内容：
我发现下面的代码有错误，因为它降低了extjs3网格的性能，我正在寻找在查询或代码层面进行优化的可能性。根据我的分析，如果我们提取出查询，那么有两个嵌套的内部查询响应速度较慢，此外，在“while循环”内部的代码试图查找唯一的ID，我们不能在查询中使用“distinct”或连接来替代内部查询。
请为我提供最佳实践，以实现优化。
public boolean isSCACreditOverviewGridVisible(String sessionId) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
boolean result = false;
try {
CommonUtility commUtil = new CommonUtility();
List<String> hmIds = new ArrayList<String>();
Map<String, String> tmStockMap = new TreeMap<String, String>();
Set<String> setRecentCertificate = new HashSet<String>();
String managerAccountId = sessionInfo.getMembershipAccount();
String stockQuery = " select memberId , RootCertficateId from stockposition sp where sp.stocktype = 'TR' and sp.memberId "
+ " IN ( select hm2.accountId from "
+ DATALINK
+ ".holdingmembers hm2 "
+ " where hm2.holdingId = ( select holdingId from "
+ DATALINK
+ ".holdingmembers hm1 where hm1.accountId = ? )) "
+ " order by sp.createdDate desc ";
conn = getChildDBConnection();
if (null != conn) {
ps = conn.prepareStatement(stockQuery);
ps.setString(1, managerAccountId);
rs = ps.executeQuery();
if (null != rs) {
while (rs.next()) {
String memberId = rs.getString("memberId");
String rootCertficateId = rs.getString("RootCertficateId");
if (tmStockMap.containsKey(rootCertficateId)) {
continue;
}
hmIds.add(memberId);
tmStockMap.put(rootCertficateId, memberId);
}
}
rs.close();
ps.close();
if (null != hmIds && !hmIds.isEmpty()) {
String inIds = commUtil.getInStateParam(hmIds);
String mostRecentLicense = "Select RootCertificateId , memberaccountid from "
+ OctopusSchema.octopusSchema
+ ".certificate c where c.memberaccountid IN ("
+ inIds
+ ") and c.isrootcertificate=0 and c.certificationstatusid > 1 order by c.modifieddate desc";
ps = conn.prepareStatement(mostRecentLicense);
rs = ps.executeQuery();
if (null != rs) {
while (rs.next()) {
String rootCertficateId = rs.getString("RootCertificateId");
String memberaccountid = rs.getString("memberaccountid");
if (setRecentCertificate.contains(memberaccountid)) {
continue;
}
setRecentCertificate.add(memberaccountid);
if (tmStockMap.containsKey(rootCertficateId)) {
result = true;
break;
}
}
}
rs.close();
ps.close();
} else {
result = false;
}
}
} catch (Exception e) {
LOGGER.error(e);
} finally {
closeDBReferences(conn, ps, null, rs);
}
return result;
}
查询：
select RootCertficateId, memberId from stockposition sp where sp.stocktype = 'TR' and sp.memberId
IN ( select hm2.accountId from
DATALINK.holdingmembers hm2
where hm2.holdingId = ( select holdingId from
DATALINK.holdingmembers hm1 where hm1.accountId = '4937' ))
order by sp.createdDate DESC;
英文:
I have found below code buggy as it degrades the performance of extjs3 grid, i am looking for possibilities of optimization at query or code level, as per my analysis, if we extract out the query there are two nested inner queries which are responding slow, in addition, the code inside while loop trying to find the unique id, can't we have distinct in query, or joins rather than inner queries.
Please suggest me the best practice to follow in order to achieve optimization.
public boolean isSCACreditOverviewGridVisible(String sessionId) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
boolean result = false;
try {
CommonUtility commUtil = new CommonUtility();
List<String> hmIds = new ArrayList<String>();
Map<String, String> tmStockMap = new TreeMap<String, String>();
Set<String> setRecentCertificate = new HashSet<String>();
String managerAccountId = sessionInfo.getMembershipAccount();
String stockQuery = " select memberId , RootCertficateId from stockposition sp where sp.stocktype = 'TR' and sp.memberId "
+ " IN ( select hm2.accountId from "
DATALINK
+ ".holdingmembers hm2 "
+ " where hm2.holdingId = ( select holdingId from "
DATALINK
+ ".holdingmembers hm1 where hm1.accountId = ? )) "
+ " order by sp.createdDate desc ";
conn = getChildDBConnection();
if (null != conn) {
ps = conn.prepareStatement(stockQuery);
ps.setString(1, managerAccountId);
rs = ps.executeQuery();
if (null != rs) {
while (rs.next()) {
String memberId = rs.getString("memberId");
String rootCertficateId = rs
.getString("RootCertficateId");
if (tmStockMap.containsKey(rootCertficateId)) {
continue;
}
hmIds.add(memberId);
tmStockMap.put(rootCertficateId, memberId);
}
}
rs.close();
ps.close();
if (null != hmIds && !hmIds.isEmpty()) {
String inIds = commUtil.getInStateParam(hmIds);
String mostRecentLicense = "Select RootCertificateId , memberaccountid from "
+ OctopusSchema.octopusSchema
+ ".certificate c where c.memberaccountid IN ("
+ inIds
+ ") and c.isrootcertificate=0 and c.certificationstatusid > 1 order by c.modifieddate desc";
ps = conn.prepareStatement(mostRecentLicense);
rs = ps.executeQuery();
if (null != rs) {
while (rs.next()) {
String rootCertficateId = rs
.getString("RootCertificateId");
String memberaccountid = rs
.getString("memberaccountid");
if (setRecentCertificate.contains(memberaccountid)) {
continue;
}
setRecentCertificate.add(memberaccountid);
if (tmStockMap.containsKey(rootCertficateId)) {
result = true;
break;
}
}
}
rs.close();
ps.close();
} else {
result = false;
}
}
} catch (Exception e) {
LOGGER.error(e);
} finally {
closeDBReferences(conn, ps, null, rs);
}
return result;
}
QUERY:
select RootCertficateId,memberId from stockposition sp where sp.stocktype = 'TR' and sp.memberId
IN ( select hm2.accountId from
DATALINK.holdingmembers hm2
where hm2.holdingId = ( select holdingId from
DATALINK.holdingmembers hm1 where hm1.accountId = '4937' ))
order by sp.createdDate DESC;
答案1
得分: 0
一个快速的方法是将你的IN替换为EXISTS。如果你的内部查询返回了很多行，这会更加高效。这取决于你的子查询是否返回了许多结果。
链接：https://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance
英文:
One quick approach would be a substition of your IN by EXISTS. If your inner queryes return a lot of rows, it would be a lot more efficient. It depends if your subquery returns a lot of results.
https://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance
专注分享java语言的经验与见解，让所有开发者获益！
评论