how to improve upload performance?

[chuckc]

Hi,

I am new in java upload excel data to db. Currently I had do some performance testing with 10 thousand rows of records in excel to be inserted into db, and it took around 40 second. I am looking forward to reduce the time taken even more. I am very much appreciate for any suggestions or idea from you all.

I am using org.apache.poi to get the data out from excel using jsp, then insert save the data into a table. There is no table join or big linking is used.

Please help me in this knotty problem, thank you.

[Prajin]

If it's 10,000 rows that u said done testing, I would say 40 seconds was fair results. Speed, performance also depends upon what db use. Google and search for db comparison.

-Regards

[chuckc]

Thanks for your suggestion Prajin, my db used is SQL server 2008. I think I will look into it even more because my client was expecting something like 20 second, which it sound very ridiculous for me to achieve, lol. I am grateful for your suggestion. Thank you.

[JosAH]

Thanks for your suggestion Prajin, my db used is SQL server 2008. I think I will look into it even more because my client was expecting something like 20 second, which it sound very ridiculous for me to achieve, lol. I am grateful for your suggestion. Thank you.

Do you use a PreparedStatement and do you keep your connection open between inserts of the rows? Also switch off the auto commit mode per record.

kind regards,

Jos

[chuckc]

I am using hibernate DAO insert to commit the insertion, prepared statement is my last resolve because I would very much like to see how to use DAO insert to improve the performance, lol. Sorry for my stuborn. I open connection before insertion and close connection after insert all. I cannot use the method of open and close transaction for each row of records because it will insert into the table directly, which if any error occur in the middle of insertion, my client do not want to delete the already inserted rows of data in the excel.

But of course, your idea is definitely faster... lol. But my client is lazy. So too bad for me.

[JosAH]

I am using hibernate DAO insert to commit the insertion, prepared statement is my last resolve because I would very much like to see how to use DAO insert to improve the performance, lol. Sorry for my stuborn. I open connection before insertion and close connection after insert all. I cannot use the method of open and close transaction for each row of records because it will insert into the table directly, which if any error occur in the middle of insertion, my client do not want to delete the already inserted rows of data in the excel.

But of course, your idea is definitely faster... lol. But my client is lazy. So too bad for me.

Leaving the connection open for all insert operations is a good thing but having the "auto commit" mode on is a show stopper. Tell your customer that s/he gets what s/he deserves. 4ms per record isn't that bad though (depending on the size of each record).

kind regards,

Jos

[chuckc]

Thank you Josah, i think this conclude it. If I happen to found any more faster ways of insertion I would love to share it out to all of you. Thank you.