• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Queries within Queries within Queries...

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic