• Post Reply Bookmark Topic Watch Topic
  • New Topic

Huge Tree Manipulation  RSS feed

 
Amer Seifeddine
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I have a tree stored in oracle table which I need to duplicate.The table has two columns: code and parentcode. Using classic recursive startegy(connect by or java recursive method) to reassign new codes to the hierarchy would take 10 minutes (52,000 records) in best cases. All what I need to do is to assign new codes from an oracle sequence and to link new codes all together. The question is how to build it in less than 15 seconds? Any algorithm? any special insert sql statement?

Thanks.
 
Scott Selikoff
author
Bartender
Posts: 4093
21
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stored Procedure.

In your example, are you making 50,000 separate JDBC calls? Unless you have a monsterly-good persistence manager, this will be extremely expensive as you describe. In general, you always optimize database code for the number of individual JDBC calls, and if its extremely high then you replace them all with a single stored procedure.

Alternately, if this still takes an extremely large amount of time, there are other things you can do such as building a denormalized table on the data that can be used during lookup and maps all parent to descendant relationships.
 
Amer Seifeddine
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You were right. I am happy when I found your answer excatly same as I did. I did recursive stored procedure and it cut the time to 20 seconds to 2.8 minutes as maximum. I beleive in pl/sql!
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!