Taking too much time to display records from db?is it because i use vector ?

1. Count of total records on your table.?  >>  20 to 25 Records
2. Why is the return type of SQLManager.select(sql); - a vector ? to provide synchronized access?.
If Possible - paste the code for SQLManager.select  -->please find the sqlmanger code
3. Are you using hibernate? >> i am not using hibernte

-----------------------------

public class SQLManager {
	
	/**
	 ****************************************************
	 * getTrans
	 * @return
	 * Transaction
	 ****************************************************
	 */
	public static Vector select(String sql) {
		Connection conn = ConnectionManager.getConnection();
		Vector vecRow = new Vector();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int colCount = rs.getMetaData().getColumnCount();
			System.out.println("colCount="+colCount);
			String data = new String();
			while (rs.next()) {
				for (int i = 0; i < colCount; i++) {
					vecRow.add(rs.getString(i + 1));
				}
			}
			return vecRow;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (!conn.isClosed()) {
					ConnectionManager.releaseConnection(conn);
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}
	
	
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:

hi all,
     Any solution for my problem....

@Naseema: Sorry for the late reply , since it was weekend.

I just gone through your code and tried simulating ur issue with one of my table holding 34 records and the results returned in 15 msec.

i would like you to do this on your machine. Before that Short answer for ur question is "No vector doesn't delay anything in ur code".


long strt =System.currentTimeMillis();
                    System.out.println(strt); // give this while entering into ur code.


System.out.println(System.currentTimeMillis()-strt); // this one before returning out of ur code.

Similarly same set u can use it inside ur SQLManager.select method to identify the msec it takes to execute the query execution part.

By this we can identify which causes the delay.

-Murali*

Hi,
    thanks a lot for the reply. i will try now your suggestion and reply the post with the reply.

hi ,
  i tried your comment i am getting error  operator - cannot be applied to java.lang.String,long }System.out.println("ending Time"+System.currentTimeMillis()-strt);

It should be this way when u append a String

System.out.println("ending Time"+ (System.currentTimeMillis()- strt));

-Murali*

HI,
   I tried your comment and the output is

Starting Time   1246858188977

ending Time      77797

for which part of ur code did u try this?

Post the time for the SQLManger.select() as well.

hi,
  i tried your comment please find the output as below

Starting Time1246859501110  (In jsp)
Starting Time in SQLManager Select 1246859512844

ending Time in SQLManager Select0
ending Time95422(in jsp)

Fine , then SQLManager doesn't cause any delay to execute your query.
Apply the same time settings to see how much time it takes to execute the below for loop.

Are you sure the record count is just 20 -25 rows? Can u print the size of the vRow vector to ensure the size?.


for (Iterator iter = vRow.iterator(); iter.hasNext();) {
                                                        pendingOrderInfo = new COrders();
                                                        element = (String)iter.next();
                                                        pendingOrderInfo.setOrd_no((element == null ? "" : element));
                                                        element = (String)iter.next();
                                                        pendingOrderInfo.setOrd_dt((element == null ? "" : element));
                                                        element = (String)iter.next();
                                                        pendingOrderInfo.setOrd_rec_dt((element == null ? "" : element));
                                                        pendingOrders.add(pendingOrderInfo);
                                                        }

hi ,
  i tried your comment please find output as below


Starting Time            1246864946467 (in jsp)
Starting Time in SQLManager Select 1246864951530
vRow.size()              2360
Starting Time in Loop 1246864951780

ending Time in SQLManager Select   16
ending Time     55673(in jsp)
ending Time in Loop  0

Where as i executed the query from SQL prompt the record count is 118 and it took 1 second to execute the query from sql prompt

Whats wrong with my code?

1. First thing to check is the row count discrepancy between ur code execution and the SQL prompt execution. Print the query and copy paste the same in the SQL prompt. Similary add a int variable and increment inside the for loop in SQLManager.select() to see the record count like this below,

int index=0;
while (rs.next()) {
                                for (int i = 0; i < colCount; i++) {
                                        vecRow.add(rs.getString(i + 1));
                                }
                                index++;
                        }

System.out.println(index);

2.
Time between the JSP and the start of the SQLManager.select is  5063 msec , so what other steps do u perform in between?. I hope ur JSP time statement is placed right after line 12  ( means before calling the function. If not add one after line 12 and after line 13 to see the how much total time it takes to execute that function.

3. What is the total count of records shown on ur page?  2360  or 118.


Main reason for the delay could be any loop running recursively. If possible attach ur jsp and java file.. just the set of methods which are called.

hi,
 i copied  your code and i found it  
System.out.println(index); --->output is ---> 1

2.I attached my JSPs and Java Classes

3. What is the total count of records shown on ur page?  --->  118.

Please let me know where i am wrong?

Naseema:

Just took a quick look in ur code.

You should calculate the time for this part of ur code. Print start time before if condition and print end time after end of if statement.

<%if (sortBy!=null)
                                          {
                                                pendingOrders=new SupportPendingOrders();
                                              pOrders=new Vector();
                                              pOrders=pendingOrders.getPendingOrders(supplier_id,sortBy,sortOrder);
                                                System.out.println("sortValue values is "+sortValue);
                                          }
                                          else
                                          {                                          
                                          pendingOrders=new SupportPendingOrders();
                                            pOrders=new Vector();
                                            pOrders=pendingOrders.getPendingOrders(supplier_id);
                                          }                                          
                                          %>
I am sure this would be much faster.


I want you to check few areas,

1. fileprocess.writeToLogFile("[OrderList.jsp]-----Start Pending Orders=","blue");

>> what is the function of this?.. R u writing to a log file? if Yes why dont you use log4j instead of having ur own API.

2. countFHSLog.getLogCounter

>> this is taking the records from log table .Find out how many records does it fetch?

3. <%for (Iterator iter1 = lOrders.iterator(); iter1.hasNext();)
                            {

                          countFHSLog=(SupportPendingOrders) iter1.next();
                 
                      }    
             %>

>> what this particular piece of code is expected to do in the jsp file?. For every iteration of for loop the object value is just overridden. I think you missed some logic here.

4. More over if u need to find the time of execution of a for loop then you need to print the start time before for loop statment and get the end time after the forloop statement. What u have done in ur code is u have placed them inside the for loop which will give the time for each iteration.


Overall , You have lot of iterations running on ur jsp page , and i suspect the count of records for few should be very high which contributes to the delay.Once u find the longest running for loop then u need to dig into that to make sure you fetch only the required records and not all.

-Murali*

hi,
   i found where exactly the problem.The problem is here as below


<%for (Iterator iter = pOrders.iterator(); iter.hasNext();)
          {
            
               pendingOrders=(SupportPendingOrders) iter.next();
               if (pendingOrders !=null)
                  {
                       
                                             
                     lOrders=countFHSLog.getLogCounter(pendingOrders.getOrd_no                      (),pendingOrders.getSeq_nr());//problem is here
                   }
          }

The problem at lOrders=countFHSLog.getLogCounter(pendingOrders.getOrd_no(),pendingOrders.getSeq_nr()); and in the iteration
for (Iterator iter1 = lOrders.iterator(); iter1.hasNext();)

if i remove this line lOrders=countFHSLog.getLogCounter(pendingOrders.getOrd_no(),pendingOrders.getSeq_nr()); and the second iteration
the execution time is  as below
 ========================================================
 Starting Time Before For Loop  1246943581827
 ========================================================
 ========================================================
 ending Time after for loop  19063
 ========================================================

if i include the code lOrders=countFHSLog.getLogCounter(pendingOrders.getOrd_no(),pendingOrders.getSeq_nr());
and
<%for (Iterator iter1 = lOrders.iterator(); iter1.hasNext();)  //Problem is here too
                            {
                          countFHSLog=(SupportPendingOrders) iter1.next();
                               
                 
                      }    
below is the time is as below

 ========================================================
 Starting Time Before For Loop1246943748922
 ========================================================
 ========================================================
 ending Time after for loop  83079
 ========================================================
 ========================================================



The code lOrders=countFHSLog.getLogCounter(pendingOrders.getOrd_no(),pendingOrders.getSeq_nr());

This code will take the ordernumber and sequence number from the first iteration and get the log counter for each order number.

So how can we make it faster execution.