Solved

Time taking process alternative

Posted on 2010-09-21
9
383 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
Via a live example, show how to take different types of Oracle backups using RMAN.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

856 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