Solved

Time taking process alternative

Posted on 2010-09-21
9
379 Views
Last Modified: 2013-11-11
Hello Guys,

I have functionality which is actually taking more than 30 min to complete. The technologies that are used Java, Hibernate and Spring. This functionality deals with around 20 tables at a time. It like copying around  5000 recording with updating references for each Row.  The complete code has is written in Java. Are there any special ways that will actually reduce this process to 1 min.

How about writing a procedure ?

Thanks
Viswanath

0
Comment
Question by:Viswanathkrishna
  • 3
  • 3
  • 2
9 Comments
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 33727989
Hi,

Doing that kind of stuff on 5000 records with only one UPDATE SQL statement can probably be done in 1 minute - whether that statemement is called from java (jdbc statement), hibernate (SQL query) or stored procedure.
Did you check why it is so long ? Bad design and bad ORM mapping can lead to huge number of roundtrips with the database, getting lot of data that is not used.

Regards,
Franck.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 33728735
I believe java program is copying one row from parent, then again open connection for child copy all required rows in loop etc.
Just check if you have all required indexes. (specially for Foreign key columns)
Also check if your program is experiences a blocking lock issues etc.
Or look for any long running sql using gv$session_longops
Otherwise, rewrite sql something like this. I believe you dont need procedure
Since you are copying only 5000 rows, there must be some criteria, ie emp_number etc.
1. Make script of those who doesn't have parent/child table.
2. find Parent tables say Level_1 out of 20 tables. Make sql to copy 5000 rows (user insert/update/merge as per your requirement)
3. find all parent tables which are child of parent table Level_1 name it as Level_2. ie
  select * from Parent_tables where parent_table=Level_1 and has_child;
4. repeat step 3 till you reach extream child.
Main idea is to copy parent table first with required rows, then their immediate child, then their immediate child etc.
0
 

Author Comment

by:Viswanathkrishna
ID: 33729117
@ franck

It may not be possible with one update statement because there are around 10 entities each one have its own children.

@Vir

I agree with your logic because that is how it is implemented now which made it work for 30 mins....

Is there any Logic which can help me on the Database side.

Thanks

Viswanath
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 33732094
>> It may not be possible with one update statement because there are around 10 entities each one have its own children.
Ok but maybe 10 update statements, then. One for each table.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 33734959
In your current setup, Please look if queries  are doing full table scan.
I would recommend to generate Trace file, convert it into text using TKProf, and past.
We will look into which query/table is creating the problem.
0
 

Author Comment

by:Viswanathkrishna
ID: 33750127
How to get the trace file?
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 500 total points
ID: 33750709
Use alter session command as follows before running your code:-

alter session set sql_trace=true
Your code should be here.
alter session set sql_trace=false;

Use the following command to see where trace file is generated if you have dba access otherwise co-ordinate with DBA to find correct trace file.

show parameter user_dump_dest

Once you identified the file, use the following to generate text file

tkprof your_trace_file output_file sys=no explain=table_owner_user/table_owner_pass

0
 

Author Closing Comment

by:Viswanathkrishna
ID: 33951157
Thanks for your response
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A list of useful business intelligence software.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now