aspose file tools*
The moose likes Object Relational Mapping and the fly likes series of updates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "series of updates" Watch "series of updates" New topic
Author

series of updates

Prashanth Bhanu
Ranch Hand

Joined: Sep 25, 2003
Posts: 110
Dear Friends


I upload a excel file which has about 30,000 records(having columns data1,data2,data3 etc..).Values in data1 column looks some thing like {XXXX-XXX-ABCD}
and so on . And my database table TXN_DATA is having about 200,000 records(data1,data2,dataK...dataZ).
While file is being uploaded based on some logic i would derive one more value like XXXX-XXX-ACDB.Now i have two values of data1(XXXX-XXX-ABCD and XXXX-XXX- ACDB) .By using these two values i must query the database table to verify for a match.

1) If match is found and the match was for XXXX-XXX-ABCD(which i obtained from excel file) then all values fecthed from excel row for this particular row is updated in TXN_DATA table
2) If match is found for XXXX-XXX-ACDB which is a derived value then data1 column will be updated with XXXX-XXX-ABCD and all other values where
TXN_DATA.data1='XXXX-XXX-ACDB'

Since i'm doing this activity row by row it takes about 1Hour 30 Minutes to process(for each row one select and one corresponding update is fired).

what did i do => 1) obtained the excel records in to a collection
2) looped through the collection
3) for each object of the collection i do the above said logic
4) query the database to see wether the record exist and fetch the record(using hibernate,fecthing Entity object)
5) update that row using plain sql(using org.springframework.jdbc.core.JdbcTemplate. and calling update method on it
6) i'm connecting to oracle 10G database


Here i'm seeking your valuable advice to do it more efficiently and it shouldn't take more than 5-10 seconds.Do you guys have any
suggestion for me to tackle the issue.Please help and Thanks in advance

Thanks and Regards,

Prashu
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

What is your 10-15 seconds target? For all 30000 excel rows to be processed? If it is this is very optimistic.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Prashanth Bhanu
Ranch Hand

Joined: Sep 25, 2003
Posts: 110
Hi Paul Sturrock,

Thanks for your reply.If i'm being very optimistic then i'lll be more than happy to have a relaistic solution for the above said problem.Ideally i shouldn't take minutes together.

Thanks and Records,

Prashu
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Well there are no silver bullets in Hibernate or Spring to make this perform better. In fact I'd recommend approaching this as if they were not there. Think about hter areas you have bottlenecks and see what you can do about them.

For example, Excel - things may have improved since I last did any Excel processing but Excel doesn't do very well with large amounts of data. If you remove the database step entirely (i.e. just process the data and discard it) how long does the operation take? Is this where the bulk of your time is used up?

Or, the database itself. Have you profiled the queries you intend to run and added any appropriate indices? Could you disable constraints before the bulk load? Both are liable to help the speed of the operation.

The JVM - can you spread the load of this opperation over multiple instances?

If response time is all you care about, can you make the database part asynchronous?

Prashanth Bhanu
Ranch Hand

Joined: Sep 25, 2003
Posts: 110
Hi Paul Sturrock,

Thanks,Well Excel part is taking very minimum time.Data base querying and updating part is taking time.I just queries the database during the updates each record updation is taking about 1 second.

I can't remove the index is because i dont expect duplicate records in the table.

I'll try distributing the load among multiple JVM instances

I'm not clear on database part asynchronous.Can you please elobarate.

Thanks and Regards,

Prashu
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: series of updates