[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Time taking process alternative

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
Viswanathkrishna
Asked:
Viswanathkrishna
  • 3
  • 3
  • 2
1 Solution
 
Franck PachotCommented:
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
 
Devinder Singh VirdiCommented:
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
 
ViswanathkrishnaAuthor Commented:
@ 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Franck PachotCommented:
>> 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
 
Devinder Singh VirdiCommented:
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
 
ViswanathkrishnaAuthor Commented:
How to get the trace file?
0
 
Devinder Singh VirdiCommented:
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
 
ViswanathkrishnaAuthor Commented:
Thanks for your response
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now