Performance issue

**itsurraja** · August 4th, 2013, 11:25 AM

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?

**jps** · August 4th, 2013, 11:38 AM

Process on multiple threads

**helloworld922** · August 4th, 2013, 09:02 PM

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

**itsurraja** · August 5th, 2013, 12:32 AM

Originally Posted by helloworld922

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 ?

**jps** · August 5th, 2013, 01:14 PM

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?

**copeg** · August 5th, 2013, 02:50 PM

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

**helloworld922** · August 5th, 2013, 02:57 PM

@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.

**jps** · August 5th, 2013, 03:56 PM

Originally Posted by helloworld922

@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~

**itsurraja** · August 5th, 2013, 10:26 PM

Originally Posted by jps

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

**itsurraja** · August 12th, 2013, 04:15 AM

Originally Posted by itsurraja

@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

**jps** · August 12th, 2013, 11:04 AM

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.

**copeg** · August 12th, 2013, 12:08 PM

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?

**itsurraja** · August 14th, 2013, 04:09 AM

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

**itsurraja** · August 20th, 2013, 05:50 AM

Hello All,

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

Thanks,
Raj

Thread: Performance issue

LinkBack

Thread Tools

Display

Performance issue

Related threads:

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Re: Performance issue

Similar Threads

Exceptions : Is there any performance overhead ?

Mysql query performance issue in java batch

Will inheritance affect performance?

CPU Performance Software.

Is there any performance issues if we use Scanner class?