Results 1 to 7 of 7
  1. #1
    chuckc is offline Member
    Join Date
    Dec 2010
    Posts
    4
    Rep Power
    0

    Default how to improve upload performance?

    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.

  2. #2
    Prajin's Avatar
    Prajin is offline Senior Member
    Join Date
    Jun 2010
    Location
    Ktm, Nepal
    Posts
    120
    Rep Power
    0

    Default

    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

  3. #3
    chuckc is offline Member
    Join Date
    Dec 2010
    Posts
    4
    Rep Power
    0

    Default

    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.

  4. #4
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    12,998
    Blog Entries
    7
    Rep Power
    19

    Default

    Quote Originally Posted by chuckc View Post
    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
    cenosillicaphobia: the fear for an empty beer glass

  5. #5
    chuckc is offline Member
    Join Date
    Dec 2010
    Posts
    4
    Rep Power
    0

    Default

    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.

  6. #6
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    12,998
    Blog Entries
    7
    Rep Power
    19

    Default

    Quote Originally Posted by chuckc View Post
    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
    cenosillicaphobia: the fear for an empty beer glass

  7. #7
    chuckc is offline Member
    Join Date
    Dec 2010
    Posts
    4
    Rep Power
    0

    Default

    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.

Similar Threads

  1. Improve my GUI!
    By AJArmstron@aol.com in forum New To Java
    Replies: 8
    Last Post: 04-27-2010, 09:17 PM
  2. How to Improve performance of query?
    By SE123 in forum Advanced Java
    Replies: 0
    Last Post: 10-29-2009, 09:13 AM
  3. :large file upload to server(chunk upload)
    By tommy_725 in forum Networking
    Replies: 0
    Last Post: 10-16-2009, 12:21 AM
  4. how to improve java performance
    By sunjavaboy in forum Advanced Java
    Replies: 6
    Last Post: 06-15-2008, 05:58 AM
  5. how to improve the performance of JWS?
    By dinesh kaushik in forum Java Applets
    Replies: 0
    Last Post: 11-21-2007, 08:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •