posted 15 years ago
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