如何提升迭代循环的性能

huangapple
go评论99阅读模式
英文:

How to Improve performance of iteration loops

问题

以下是翻译好的内容：

我有一个查找父节点的所有子节点（叶子节点）的 Java 方法。它能正常工作，但存在性能问题。在子节点数量变多时，问题变得突出。下面是这个方法：

  1. private void getChildren(String orgId, List<String> children, List<String> organs, int j) {
  2.     Query query = entityManager.createNativeQuery("select c.organization_id from organization c where c.parent_org_id=" + orgId);
  3.     if (query.getResultList().size() > 0)
  4.         organs.addAll((List<String>) query.getResultList());
  5.     else
  6.         children.add(orgId);
  7.     for (int i = j + 1; i < organs.size(); i++) {
  8.         j = i;
  9.         query = entityManager.createNativeQuery("select c.organization_id from organization c where c.parent_org_id=" + String.valueOf(organs.get(i)));
  10.         if (query.getResultList().size() > 0)
  11.             organs.addAll((List<String>) query.getResultList());
  12.         else
  13.             children.add(String.valueOf(organs.get(i)));
  14.     }
  15. }

我调用这个方法的方式如下：

  1. getChildren("68", new ArrayList<>(), new ArrayList<>(), -1);

我使用的是 Oracle 数据库，如果通过 Oracle 查询可以更好地实现这一点，请告诉我。顺便说一下，我的 Spring 版本不支持流（streams）。

英文:

I have a java method which finds all children (leaves) of a parent. It works ok.but there is performance issue. How can i make it work faster? When the children gets large it becomes problematic. Here is the method:

  1. Private void getChildren(String orgId, List&lt;String&gt; children, List&lt;String&gt; organs, int j){
  2. Query query= entityManager.createNativeQuery(&quot;select c.organization_id from organization c where c.parent_org_id=&quot;+orgId);
  3. if(query.getResultList().size()&gt;0)
  4.   organs.addAll((List&lt;String&gt;) query.getResultList());
  5. else
  6.   children.add(orgId);
  7. for(int i=j+1; i&lt;organs.size();i++){
  8. j=i;
  9. query=entityManager.createNativeQuery(&quot;select c.organization_id from organization c where c.parent_org_id=&quot;+String.valueOf(organs.get(i)));
  10. if(query.getResultList().size()&gt;0)
  11.   organs.addAll((List&lt;String&gt;) query.getResultList());
  12. else
  13.   children.add(String.valueOf(organs.get(i)));
  14. }
  15. }

I call this method like

  1. getChildren(&quot;68&quot;,new ArrayList&lt;&gt;(),new ArrayList&lt;&gt;(),-1)

I have oracle as database so if it is better to acheive this via oracle query please note me.
By the way my spring version doesn't support streams!

答案1

得分: 3

你基本上有一个1+N的选择问题（为集合中的每个id发出单独的查询）。不要这样做，而是使用IN子句发出单个查询！

另一件你不应该做的事情是在查询创建时使用连接操作，绝对不要这样做！你应该做的是只返回结果，并使用带有IN子句的查询。

  1. String query = "select c.organization_id from organization c where c.parent_org_id IN (:ids)";
  2. return em.createNativeQuery(query).setParameter("ids", organs);

或者更好的做法是完全使用单个查询。你可以使用自连接来实现这个）

  1. String query = "SELECT c1.organization_id " +
  2.                "FROM organization c1 LEFT OUTER JOIN organization c2 " +
  3.                "ON c1.parent_org_id=c2.organization_id " +
  4.                "WHERE c2.parent_org_id=?";
  5. return em.createNativeQuery(query, String.class).setParameter(1, orgId).getResultList();

根据你的解决方案，我强烈建议学习SQL和JPA。

英文:

You basically have a 1+N select problem (issue a single query for each id in your collection). Don't do this, instead use the IN clause and issue a single query!.

Another thing you shouldn't be doing is use concat for your query creation, never do this! What you should do is just return the result and use a query with an IN clause.

  1. String query = &quot;select c.organization_id from organization c where c.parent_org_id IN (:ids))
  2. return em.createNativeQuery(query).setParameters(&quot;ids&quot;, organs);

or even better use a single query altogether. You can use a self join for that)

  1. String query = &quot;SELECT c1.organization_id &quot; +
  2.                &quot;FROM organization c1 LEFT OUTER JOIN organization c2 &quot; +
  3.                &quot;ON c1.parent_org_id=c2.organization_id &quot; +
  4.                &quot;WHERE c2.parent_org_id=?&quot;; 
  5. return em.createNativeQuery(query, String.class).setParameter(1, orgId).getResultList();

Judging from your solution I would strongly recommend studying SQL and JPA.

答案2

得分: 1

尝试在大查询中使用

  1. 从组织中选择 c.organization_id ，其中 ( 
  2. 从组织中选择 c.organization_id 作为 sub1 ，其中 c.parent_org_id=&quot;+orgId +&quot; ) 作为 x_ ，
  3. 其中 organization.parent_org_id = x_.sub1

之所以慢的原因是，您为每个子项查询了数据库。这是非常低效的，我认为甚至数据库的负载也会较小。

英文:

Try it in a big query

  1. select c.organization_id from organization, (
  2. select c.organization_id as sub1 from organization c where c.parent_org_id=&quot;+orgId +&quot;) as x_ where organization.parent_org_id = x_.sub1

The reason why it is slow, is that you query the database for every child. This is very inefficient, I assume even the load on the database will be smaller.

答案3

得分: 0

Deinum提供的答案仅获取直接子项。而我的Java方法会返回包括嵌套子项在内的所有子项。

我已找到了一个比我的Java方法更优化的查询方法。它使用临时表：

  1. with temp_tbl (id,parent_id) as 
  2. (
  3. select organization_id,parent_org_id from organization where parent_org_id =? 
  4. union all 
  5. select c.organization_id ,c.parent_org_id from organization c join temp_tbl t on t.id=c.parent_org_id and t.parent_id !=t.id
  6. )
  7. Select to_char(g.id) from temp_tbl g

最后我返回to_char(g.id)将其转换为字符串，而不是大十进制数。因为em.createNativeQuery(query, String.class)没有将其转换为字符串，对我抛出了错误。所以只需使用em.createNativeQuery(query)。

英文:

Answer provided by Deinum only gets the direct children. Whereas my java method returns all children including nested ones.

I have found a query for that which is much more optimized than my java method. It uses temporary tables:

  1. with temp_tbl (id,parent_id) as 
  2. (
  3. select organization_id,parent_org_id from organization where parent_org_id =? 
  4. union all 
  5. select c.organization_id ,c.parent_org_id from organization c join temp_tbl t on t.id=c.parent_org_id and t.parent_id !=t.id
  6. )
  7. Select to_char(g.id) from temp_tbl g

At the end i return to_char(g.id) to make it a string instead of big decimal. Because em.createNativeQuery(query, String.class) didn't turn it to string and threw error for me.
So just use em.createNativeQuery(query)

huangapple
  • 本文由 发表于 2020年8月17日 14:46:03
  • 转载请务必保留本文链接：https://go.coder-hub.com/63445832.html
  • java
  • oracle
  • spring
匿名

发表评论

匿名网友

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

确定