Solved

Time taking process alternative

Posted on 2010-09-21
9
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
texting/mms service for clients 4 37
running on tomcat not jboss eap 7.0 3 32
format dd/mm/yyyy parameter 16 48
downlod failures 6 72
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.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

733 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