File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Performance and the fly likes Queries within Queries within Queries... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Queries within Queries within Queries..." Watch "Queries within Queries within Queries..." New topic

Queries within Queries within Queries...

Kim Lilienfeld

Joined: Apr 13, 2004
Posts: 25

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.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Jul 11, 2001
Posts: 14112
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.

The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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

Joined: Apr 13, 2004
Posts: 25
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!!
I agree. Here's the link:
subject: Queries within Queries within Queries...
It's not a secret anymore!