Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 14 of 14

Thread: Performance issue

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Performance issue

    Hello All,
    I am trying to retrieve 35000 records from sybase database after executing query. My query executing time is less than 3 secs.. but processing time is taking too much time 55 secs....

    while(rs.next()){
    }
    This above code is taking too much time. while reading is taking time for 35k records.
    Plz advise
    can someone help on this above?


  2. #2
    Super Moderator jps's Avatar
    Join Date
    Jul 2012
    Posts
    2,642
    My Mood
    Daring
    Thanks
    90
    Thanked 262 Times in 231 Posts

    Default Re: Performance issue

    Process on multiple threads

  3. #3
    Super Moderator helloworld922's Avatar
    Join Date
    Jun 2009
    Posts
    2,896
    Thanks
    23
    Thanked 619 Times in 561 Posts
    Blog Entries
    18

    Default Re: Performance issue

    Can you post your actual processing code? Most likely there's something inside it which is performing very poorly.

  4. #4
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Re: Performance issue

    Quote Originally Posted by helloworld922 View Post
    Can you post your actual processing code? Most likely there's something inside it which is performing very poorly.
    Hi,

    Actually i have commented all code inside while block. I just put logic to get count of Resultset count. Just while loop from rs.next itself taking so much time. After executing query we are getting only resultset obj, not all data right. So Reading data from remote database server is taking too much time (55 secs) due to network traffic and packet size I guess. My application is using Sybase database and using Jconnect 3 JDBC driver. Also Jconnect getFetchsize is 0(which means send all data @ once, so no more network round)

    long inTime = System.currentTimeMillis();
    ResultSet resul = null;
    resul = stmt.executeQuery(getQuery3());
    System.out.println("Query execute time :" + (System.currentTimeMillis() - inTime));
    // Above Query execute time - 625 milli secs, less than 1 sec 
     
    inTime = System.currentTimeMillis();
    int i = 0;
    while (resul.next()) {
    i++;
    }
    System.out.println("Iteratetime :"+ (System.currentTimeMillis() - inTime) + ", result records:" +i);
    // Iteratetime : 56400 milli secs, approx 56 secs.
    can someone advise how to improve performance for above scenario? plz..

    Currently my databse packet size is 2MB.

    if we improve packet size in sybase database server will improve performance ?
    or how to implement thread in this scenario ?
    Last edited by helloworld922; August 5th, 2013 at 02:04 AM. Reason: please use [code] tags

  5. #5
    Super Moderator jps's Avatar
    Join Date
    Jul 2012
    Posts
    2,642
    My Mood
    Daring
    Thanks
    90
    Thanked 262 Times in 231 Posts

    Default Re: Performance issue

    If you process them one at a time, you are restricted to linear time in processing. The-Time-To-Process-One-Item multiplied by The-Number-Of-Items
    If the problem is a large number of items, then the solution is to process more than one at a time. Sorry, I can not provide a solution, but will gladly provide what ever assistance I can.
    If the problem is a lengthy processing time, then I do not see enough code to identify a problem.
    Can you post a SSCCE? ...and a sample set?

  6. #6
    Super Moderator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,225
    Thanks
    176
    Thanked 817 Times in 760 Posts
    Blog Entries
    5

    Default Re: Performance issue

    Just while loop from rs.next itself taking so much time. After executing query we are getting only resultset obj, not all data right. So Reading data from remote database server is taking too much time (55 secs) due to network traffic and packet size I guess
    ...
    if we improve packet size in sybase database server will improve performance ?
    You did not provide any query info, but pull only the data necessary. A common problem is using "select *" sql syntax - specify only the columns needed. I would also recommend checking the transfer rate from A to B - the network could be running at max anyway in which case the only speed you can truly gain is a) lower the amount of information transfered or b) increase the network speed

  7. #7
    Super Moderator helloworld922's Avatar
    Join Date
    Jun 2009
    Posts
    2,896
    Thanks
    23
    Thanked 619 Times in 561 Posts
    Blog Entries
    18

    Default Re: Performance issue

    @jps:
    I don't think multithreading is the proper solution here.

    Assume your computer has 4 threads. At best, assuming perfect parallelization, you will get 56/4 = 14 sec. That should not be expected performance for just examining ~35000 query results.

    Things I would suspect:

    1. Perhaps search results aren't being returned at once (i.e. each call to resul.next() is querying the server for the next result).
    2. Perhaps executing a query is being done asynchronously (i.e. executeQuery can return before all results are actually sent to the client). This seems like it would be most likely.
    3. There's something else running in the background which is eating up resources.
    4. There's some sort of benchmarking fluke. Best way to ensure this isn't the case is to benchmark multiple times.
    5. Your system is strapped for memory and has to fallback to very slow virtual memory.

    Other things you could try:

    1. Do you really need to return all 35k query results? Perhaps you could do with limiting the results to say the first 1k. how does this perform?
    2. Do you have to use SyBase's database? Is it possible to test your code with another database?
    3. Do you have other hardware (with better specs) you could test with?
    4. Try asking SyBase's tech support. This seems like a specific issue with using their software, which few users know/have access to.

  8. #8
    Super Moderator jps's Avatar
    Join Date
    Jul 2012
    Posts
    2,642
    My Mood
    Daring
    Thanks
    90
    Thanked 262 Times in 231 Posts

    Default Re: Performance issue

    Quote Originally Posted by helloworld922 View Post
    @jps:
    I don't think multithreading is the proper solution here.
    Agreed. But if I can cut my processing time to 1/4, I would likely make it a part of the solution.
    Only the SSCCE and time will tell~

  9. #9
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Re: Performance issue

    Quote Originally Posted by jps View Post
    Agreed. But if I can cut my processing time to 1/4, I would likely make it a part of the solution.
    Only the SSCCE and time will tell~
    @JPS, actually i have taken query part alone to standalone program. Same application db connection, i got it through Jconnect 3 driver program.

    Query sample is

    Select column 1,column 2,column 3,column 4,column 5,column 6,column 7 form Table A, Table B, Table c
    with corresponding join condition.

    As above mentioned i used same code after getting connection obj. we don't worry about query part, because
    Query executing time is less than 1 sec. But while(rs.next()) loop alone, its taking more than 55 secs.. i.e., Data reading part is taking too much time. Its may be network traffic or Sybase JDBC driver Capability. I don't think, its java code issue... or heap memory allocation issue.

    @JPS, Is thread program possible here, can you change my sample code to thread base code. ( I don't know how to implement thread here).

    Actually i also suggested pagination concept here instead of taking whole rows at one time, we will divide 100, 200 like that.. But its a production defect. So we can't go for implementation now they said. so I dont know how to improvement this scenario. Please help me on this.


    Thanks,
    Raj

  10. #10
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Performance issue

    Quote Originally Posted by itsurraja View Post
    @JPS, actually i have taken query part alone to standalone program. Same application db connection, i got it through Jconnect 3 driver program.

    Query sample is

    Select column 1,column 2,column 3,column 4,column 5,column 6,column 7 form Table A, Table B, Table c
    with corresponding join condition.

    As above mentioned i used same code after getting connection obj. we don't worry about query part, because
    Query executing time is less than 1 sec. But while(rs.next()) loop alone, its taking more than 55 secs.. i.e., Data reading part is taking too much time. Its may be network traffic or Sybase JDBC driver Capability. I don't think, its java code issue... or heap memory allocation issue.

    @JPS, Is thread program possible here, can you change my sample code to thread base code. ( I don't know how to implement thread here).

    Actually i also suggested pagination concept here instead of taking whole rows at one time, we will divide 100, 200 like that.. But its a production defect. So we can't go for implementation now they said. so I dont know how to improvement this scenario. Please help me on this.


    Thanks,
    Raj


    Can someone help me on this above issue? plz

  11. #11
    Super Moderator jps's Avatar
    Join Date
    Jul 2012
    Posts
    2,642
    My Mood
    Daring
    Thanks
    90
    Thanked 262 Times in 231 Posts

    Default Re: Performance issue

    I think the problem right now is, you have not provided enough for anyone to tell what the problem(s) is/are. We are making guesses and suggestions based on our understanding of what might be.

  12. #12
    Super Moderator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,225
    Thanks
    176
    Thanked 817 Times in 760 Posts
    Blog Entries
    5

    Default Re: Performance issue

    You seem to not be considering the entire context of the problem - to profile code you can't guess as to what the bottleneck is, you must break up and profile each step individually. Run the query on the server (for instance pipe the results to a file) - how long does it take (you seem to be assuming the executeQuery() represents this time - but why assume)? Are the columns on the tables you join indexed? How long does it take to transfer that sized file over your network?

  13. #13
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation Re: Performance issue

    Hi all,

    Please see my below code and corresponding output values. now you can understand where my problem is, i hope.

    inTime = System.currentTimeMillis();
    ResultSet resul = null;

    resul = stmt.executeQuery(getQuery3()); // Query3() contains actual query strings.

    System.out.println(" Query execute time :" + (System.currentTimeMillis() - inTime));



    long startTime = new java.util.Date().getTime();
    long beforeNext = new java.util.Date().getTime();
    long afterNext = 0;
    int rowCounter = 1;
    try {
    File file = new File("\\C:\\Data\\RajTemp\\fileContent.txt");
    FileWriter fw = new FileWriter(file.getAbsoluteFile());
    BufferedWriter bw = new BufferedWriter(fw);

    while (resul.next())
    {
    // I commented all my java code here to find out the exact db processing time
    afterNext = new java.util.Date().getTime();
    bw.write(rowCounter + ". (" + (afterNext - beforeNext)+ ") ");
    bw.newLine();
    rowCounter++;
    beforeNext = afterNext;
    }
    bw.close();
    } catch (IOException ie) {
    ie.printStackTrace();
    }
    startTime = new java.util.Date().getTime() - startTime;
    System.out.println("\n\nTotal Processing Time: " + startTime);

    //Query execute time :641 milli seconds

    // Total Processing Time: 53641 milli seconds

    Please find the below output and see the milli sec timings in between rows. My question here is, this time delay is taken due to network traffic or our heap size related?

    Can someone help me on this? I read resultset values are storing in heap memory.

    Output:-
    Row no with (milli secs)
    1. (31)
    2. (0)
    3. (0)
    4. (0)
    5. (0)
    6. (0)
    7. (0)
    8. (0)
    9. (0)
    10. (0)
    11. (0)
    12. (0)
    13. (0)
    14. (0)
    15. (0)
    16. (0)
    17. (0)
    18. (0)
    19. (0)
    20. (0)
    21. (203)
    22. (0)
    23. (0)
    24. (0)
    25. (0)
    26. (0)
    27. (0)
    28. (0)
    29. (0)
    30. (0)
    31. (0)
    32. (0)
    33. (0)
    34. (0)
    35. (0)
    36. (0)
    37. (0)
    38. (0)
    39. (0)
    40. (0)
    41. (0)
    42. (0)
    43. (0)
    44. (0)
    45. (0)
    46. (0)
    47. (0)
    48. (0)
    49. (0)
    50. (0)
    51. (0)
    52. (0)
    53. (0)
    54. (0)
    55. (0)
    56. (0)
    57. (0)
    58. (0)
    59. (16)
    60. (0)
    61. (0)
    62. (0)
    63. (0)
    64. (0)
    65. (0)
    66. (0)
    67. (0)
    68. (0)
    69. (219)
    70. (0)
    71. (0)
    72. (0)
    73. (0)
    74. (0)
    75. (0)
    76. (0)
    77. (0)
    78. (0)
    79. (0)
    80. (0)
    81. (0)
    82. (0)
    83. (0)
    84. (0)
    85. (0)
    86. (0)
    87. (0)
    88. (0)
    89. (0)
    90. (0)
    91. (0)
    92. (0)
    93. (0)
    94. (0)
    95. (0)
    96. (0)
    97. (0)
    98. (0)
    99. (0)
    100. (0)
    101. (0)
    102. (0)
    103. (0)
    104. (0)
    105. (0)
    106. (0)
    107. (0)
    108. (0)
    109. (0)
    110. (0)
    111. (0)
    112. (0)
    113. (0)
    114. (0)
    115. (0)
    116. (0)
    117. (0)
    118. (0)
    119. (0)
    120. (0)
    121. (0)
    122. (0)
    123. (0)
    124. (0)
    125. (0)
    126. (0)
    127. (0)
    128. (0)
    129. (0)
    130. (0)
    131. (47)
    132. (0)
    133. (0)
    134. (0)
    135. (0)
    136. (0)
    137. (0)
    138. (0)
    139. (0)
    140. (203)

    I am giving parcial outputs here. (since output rows are more than 35K).

    Thanks,
    Raj

    --- Update ---

    Actual getQuery3 query string is,

    SELECT st.cusip,st.id_type,st.src_acct, st.acct_num, st.par, st.sec_type,s.pool_num, st.mtr_date, st.cpn_rate, st.price,st.price_src,s.sec_issuer, st.mkt_val, st.accr_intr_fact,st.cash_val, st.coll_val, st.exchange_rate,t.acct_num, t.acct_name, t.loan_amt,t.start_date,t.end_date,t.expected_intr ,t.intr_rate,t.cash_coll_amt,t.cash_margin_rate,t. intr_unpaid_to_date,t.alloc_method,t.intr_type,t.i ntr_days,t.loan_curr,t.deal_exchange_rate,accr_int r_val = ( st.accr_intr_fact * st.par * st.exchange_rate ),description = ( s.sec_issuer + ' ' + st.sec_type + ' ' + str( round( st.cpn_rate,5), 10, 5 ) + ' ' + convert( char( 12 ), st.mtr_date, 107 ) ),margin = ( st.cash_val - st.coll_val ),st.depos, t.orig_intr_unpaid_to_date, curr_code_prin = ISNULL(st.curr_code_prin,' ' ) ,t.rule_set_id, s.fed_desc, sx.sx_id, sx.src_sys_cd, st.dealer_id, st.lender_id, same_day_pricing FROM #std_ptemp st, #ptemp t, secr s, secr_id_xref sx WHERE st.acct_num = t.acct_num AND st.cusip = s.cusip AND st.id_type = s.id_type AND s.cusip *= sx.sx_internal_id AND s.id_type *= sx.sx_internal_id_type order by st.acct_num, st.coll_val desc, st.cusip

    my guess is, query execution time is less than a sec (547 milli secs). So no need to think about query tune here. Please correct me if am wrong.

    Thanks,
    Raj

  14. #14
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Performance issue

    Hello All,

    Anyone please help me on above issue? how to resolve this network delay? any solution?

    Thanks,
    Raj

Similar Threads

  1. Exceptions : Is there any performance overhead ?
    By Bineeth & Neethu in forum Member Introductions
    Replies: 1
    Last Post: January 17th, 2013, 10:16 AM
  2. Mysql query performance issue in java batch
    By siva.wins in forum JDBC & Databases
    Replies: 3
    Last Post: January 9th, 2013, 11:32 AM
  3. Will inheritance affect performance?
    By IHeartProgramming in forum Object Oriented Programming
    Replies: 1
    Last Post: October 12th, 2012, 01:50 PM
  4. CPU Performance Software.
    By Mr.777 in forum Java Theory & Questions
    Replies: 11
    Last Post: September 30th, 2011, 01:10 AM
  5. Replies: 1
    Last Post: May 13th, 2008, 08:08 AM