Solved

Time taking process alternative

Posted on 2010-09-21
9
380 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
DevOps Toolchain Recommendations

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

 
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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

863 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

25 Experts available now in Live!

Get 1:1 Help Now