Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Queries within Queries within Queries...

 
Kim Lilienfeld
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

I've been given the task of cleaning and optimizing some code as much as I know how to (which isn't very much!). The code I'm currently working on is a complex tangle of nested queries, all from inside the parent's open resultset. Also, for each query there is a new statement and resultset. Each statement is created from the same connection. The current program flow looks similar to what is decribed below. What is the best way to optimize this code?

statement 1
loop through resultset1{
statement 2
loop through resultset2{
statement 3
update 3
}

statement 4
loop though resultset4{
statement 5
loop through resulset5{
}
statement 6
loop through resultset6{
}
methodcall which performs more queries and manipulation
}
}

Any help would be appreciated!
Thanks a million.
Kim
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it would be helpful if you write the statements as well .So, we might figure out that those statements, one by one, are really needed or not. And may be we could be able to tune the queries instead.
 
Ilja Preuss
author
Sheriff
Posts: 14112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Kim Lilienfeld:
What is the best way to optimize this code?


The best way to optimize *any* code is to run it through an optimizer to find the bottlenecks and then specifically work to remove them. If you start to optimize without having hard data where your code spends how much time, you are very likely to optimize the wrong parts.

If you find that the database queries are your problem, P6Spy might help.
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Assuming that this code is indeed a performance culprit and needs to be optimized (as per Ilja's recommendation), you'll need to provide more of the algorithm to us. You have nested loops, resulting in nested queries. This is generally best avoided, but without knowing the actual queries, that's the best we can do.

As an example of a simple optimization, think about loading a standard tree structure. As I found it, the structure was basic: each node had its parent node's ID. Given the root node's ID, the method to load the tree was to find all that node's children and recursively load from there. If the tree had 50 nodes, this resulted in 50 queries, each returning a few nodes at a time (and none for the leaves).

The solution I chose was to add the root node's ID to every node. Now loading the tree involved one query for all nodes and then a simple loop in Java to build a tree from the nodes. While the algorithm to build the tree looked much like the original algorithm, reducing the query down to one cut the total time by 95%-98%, depending on the size of the tree (larger trees approached 99% savings).

Perhaps there is a way to perform joins to minimize the nesting of queries, but we'll need more information to know.
 
Kim Lilienfeld
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you all very much for the suggestions. Unfortunately I can't actually paste the specific code + queries in here due to company policy. However I am very grateful for the guidance and I feel like I am actually kinda maybe beginning to go in the right direction now!

Thank you 2 million and 2!!
Kim
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic