寻求以下代码的优化。

huangapple
未分类评论49阅读模式
英文:

Looking for the optimization of the below code

问题

以下是翻译好的内容：

我发现下面的代码有错误，因为它降低了extjs3网格的性能，我正在寻找在查询或代码层面进行优化的可能性。根据我的分析，如果我们提取出查询，那么有两个嵌套的内部查询响应速度较慢，此外，在“while循环”内部的代码试图查找唯一的ID，我们不能在查询中使用“distinct”或连接来替代内部查询。

请为我提供最佳实践，以实现优化。

  1. public boolean isSCACreditOverviewGridVisible(String sessionId) {
  2.     Connection conn = null;
  3.     ResultSet rs = null;
  4.     PreparedStatement ps = null;
  5.     boolean result = false;
  6.     try {
  7.         CommonUtility commUtil = new CommonUtility();
  9.         List<String> hmIds = new ArrayList<String>();
  10.         Map<String, String> tmStockMap = new TreeMap<String, String>();
  11.         Set<String> setRecentCertificate = new HashSet<String>();
  13.         String managerAccountId = sessionInfo.getMembershipAccount();
  15.         String stockQuery = " select memberId , RootCertficateId from stockposition sp  where sp.stocktype = 'TR' and sp.memberId "
  16.             + " IN  ( select hm2.accountId from "
  17.             + DATALINK
  18.             + ".holdingmembers  hm2 "
  19.             + "    where hm2.holdingId = ( select holdingId from "
  20.             + DATALINK
  21.             + ".holdingmembers  hm1 where hm1.accountId =  ? )) "
  22.             + "    order by sp.createdDate desc ";
  24.         conn = getChildDBConnection();
  25.         if (null != conn) {
  26.             ps = conn.prepareStatement(stockQuery);
  27.             ps.setString(1, managerAccountId);
  28.             rs = ps.executeQuery();
  29.             if (null != rs) {
  30.                 while (rs.next()) {
  31.                     String memberId = rs.getString("memberId");
  32.                     String rootCertficateId = rs.getString("RootCertficateId");
  33.                     if (tmStockMap.containsKey(rootCertficateId)) {
  34.                         continue;
  35.                     }
  36.                     hmIds.add(memberId);
  37.                     tmStockMap.put(rootCertficateId, memberId);
  38.                 }
  39.             }
  40.             rs.close();
  41.             ps.close();
  43.             if (null != hmIds && !hmIds.isEmpty()) {
  44.                 String inIds = commUtil.getInStateParam(hmIds);
  45.                 String mostRecentLicense = "Select RootCertificateId , memberaccountid  from "
  46.                     + OctopusSchema.octopusSchema
  47.                     + ".certificate c where  c.memberaccountid IN ("
  48.                     + inIds
  49.                     + ") and c.isrootcertificate=0 and c.certificationstatusid > 1 order by c.modifieddate desc";
  50.                 ps = conn.prepareStatement(mostRecentLicense);
  51.                 rs = ps.executeQuery();
  52.                 if (null != rs) {
  53.                     while (rs.next()) {
  54.                         String rootCertficateId = rs.getString("RootCertificateId");
  55.                         String memberaccountid = rs.getString("memberaccountid");
  56.                         if (setRecentCertificate.contains(memberaccountid)) {
  57.                             continue;
  58.                         }
  59.                         setRecentCertificate.add(memberaccountid);
  60.                         if (tmStockMap.containsKey(rootCertficateId)) {
  61.                             result = true;
  62.                             break;
  63.                         }
  64.                     }
  65.                 }
  66.                 rs.close();
  67.                 ps.close();
  68.             } else {
  69.                 result = false;
  70.             }
  71.         }
  72.     } catch (Exception e) {
  73.         LOGGER.error(e);
  74.     } finally {
  75.         closeDBReferences(conn, ps, null, rs);
  76.     }
  77.     return result;
  78. }

查询：

  1. select RootCertficateId, memberId from stockposition sp  where sp.stocktype = 'TR' and sp.memberId 
  2.     IN  ( select hm2.accountId from 
  3. DATALINK.holdingmembers  hm2 
  4.     where hm2.holdingId = ( select holdingId from 
  5. DATALINK.holdingmembers  hm1 where hm1.accountId =  '4937' )) 
  6.     order by sp.createdDate DESC;

图片链接在这里：图片1，图片2。

英文:

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.

  1. public boolean isSCACreditOverviewGridVisible(String sessionId) {
  2. 		Connection conn = null;
  3. 		ResultSet rs = null;
  4. 		PreparedStatement ps = null;
  5. 		boolean result = false;
  6. 		try {
  7. 			CommonUtility commUtil = new CommonUtility();
  9. 			List&lt;String&gt; hmIds = new ArrayList&lt;String&gt;();
  10. 			Map&lt;String, String&gt; tmStockMap = new TreeMap&lt;String, String&gt;();
  11. 			Set&lt;String&gt; setRecentCertificate = new HashSet&lt;String&gt;();
  13. 			String managerAccountId = sessionInfo.getMembershipAccount();
  15. 			String stockQuery = &quot; select memberId , RootCertficateId from stockposition sp  where sp.stocktype = &#39;TR&#39; and sp.memberId &quot;
  16. 				+ &quot; IN  ( select hm2.accountId from &quot;
  17. 				DATALINK
  18. 				+ &quot;.holdingmembers  hm2 &quot;
  19. 				+ &quot;	where hm2.holdingId = ( select holdingId from &quot;
  20. 				DATALINK
  21. 				+ &quot;.holdingmembers  hm1 where hm1.accountId =  ? )) &quot;
  22. 				+ &quot;	order by sp.createdDate desc &quot;;
  24. 			conn = getChildDBConnection();
  25. 			if (null != conn) {
  26. 				ps = conn.prepareStatement(stockQuery);
  27. 				ps.setString(1, managerAccountId);
  28. 				rs = ps.executeQuery();
  29. 				if (null != rs) {
  30. 					while (rs.next()) {
  31. 						String memberId = rs.getString(&quot;memberId&quot;);
  32. 						String rootCertficateId = rs
  33. 						.getString(&quot;RootCertficateId&quot;);
  34. 						if (tmStockMap.containsKey(rootCertficateId)) {
  35. 							continue;
  36. 						}
  37. 						hmIds.add(memberId);
  38. 						tmStockMap.put(rootCertficateId, memberId);
  39. 					}
  40. 				}
  41. 				rs.close();
  42. 				ps.close();
  44. 				if (null != hmIds &amp;&amp; !hmIds.isEmpty()) {
  45. 					String inIds = commUtil.getInStateParam(hmIds);
  46. 					String mostRecentLicense = &quot;Select RootCertificateId , memberaccountid  from &quot;
  47. 						+ OctopusSchema.octopusSchema
  48. 						+ &quot;.certificate c where  c.memberaccountid IN (&quot;
  49. 						+ inIds
  50. 						+ &quot;) and c.isrootcertificate=0 and c.certificationstatusid &gt; 1 order by c.modifieddate desc&quot;;
  51. 					ps = conn.prepareStatement(mostRecentLicense);
  52. 					rs = ps.executeQuery();
  53. 					if (null != rs) {
  54. 						while (rs.next()) {
  55. 							String rootCertficateId = rs
  56. 							.getString(&quot;RootCertificateId&quot;);
  57. 							String memberaccountid = rs
  58. 							.getString(&quot;memberaccountid&quot;);
  59. 							if (setRecentCertificate.contains(memberaccountid)) {
  60. 								continue;
  61. 							}
  62. 							setRecentCertificate.add(memberaccountid);
  63. 							if (tmStockMap.containsKey(rootCertficateId)) {
  64. 								result = true;
  65. 								break;
  66. 							}
  67. 						}
  68. 					}
  69. 					rs.close();
  70. 					ps.close();
  71. 				} else {
  72. 					result = false;
  73. 				}
  74. 			}
  75. 		} catch (Exception e) {
  76. 			LOGGER.error(e);
  77. 		} finally {
  78. 			closeDBReferences(conn, ps, null, rs);
  79. 		}
  80. 		return result;
  81. 	}

QUERY:

  1.  select RootCertficateId,memberId from stockposition sp  where sp.stocktype = &#39;TR&#39; and sp.memberId 
  2. 				  IN  ( select hm2.accountId from 
  3. 				DATALINK.holdingmembers  hm2 
  4. 					where hm2.holdingId = ( select holdingId from 
  5. 				DATALINK.holdingmembers  hm1 where hm1.accountId =  &#39;4937&#39; )) 
  6. 					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

huangapple
  • 本文由 发表于 2020年4月6日 15:20:48
  • 转载请务必保留本文链接：https://java.coder-hub.com/61054766.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定