• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

series of updates

 
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is your 10-15 seconds target? For all 30000 excel rows to be processed? If it is this is very optimistic.

 
Prashanth Bhanu
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
reply
    Bookmark Topic Watch Topic
  • New Topic