Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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

hi all,
      it took too much long time to display the records fetching from oracle DB.
      No idea where i am wrong. is it sow is because i use vector or do i have any problem in my code.?


Wating for an expert comment


COrders pendingOrders = null;
Vector pOrders=new Vector();
 
 
 
String id="";
id=session.getAttribute("ID").toString();
 
 
//Creating instance of the Java Class
pendingOrders=new COrders();
pOrders=new Vector();
pOrders=pendingOrders.getPendingOrders(id);
 
 
  <%for (Iterator iter = pOrders.iterator(); iter.hasNext();)
          {
	       
                pendingOrders=(COrders) iter.next();
                if (pendingOrders !=null)
        	    {
   		     <td    align='center' ><font face='Arial' size="1">
                                                 <%=(pendingOrders.getOrd_no()== null? 
"":pendingOrders.getOrd_no())%></td>
	     <td   align='center' ><font face='Arial' size="1">
                           <%=(pendingOrders.getOrd_dt()== null? "":pendingOrders.getOrd_dt())%></td>
	     <td   colspan="2" align='center' ><font face='Arial' size="1">
                          <%=(pendingOrders.getOrd_rec_dt()== null? "":pendingOrders.getOrd_rec_dt())%></font></td>	     
	....etc displaying more columns like above.
 
<%}%>
 
<%}%>
 
 
<!------ Java Class --->
package com.mycompany.myclass;
import java.util.Iterator;
import java.util.Vector;
 
 
public class COrders {
	private String ord_dt="";
	private String ord_no="";
	private String ord_rec_dt="";
		 * @return
		 */
		public String getOrd_dt() 
		{
			return ord_dt;
		}
 
		/**
		 * @return
		 */
		public String getOrd_no() 
		{
			return ord_no;
		}
 
		/**
		 * @return
		 */
		public String getOrd_rec_dt() 
		{
			return ord_rec_dt;
		}
 
		
 
		
		/**
		 * @param string
		 */
		public void setOrd_dt(String string) 
		{
			ord_dt = string;
		}
 
		/**
		 * @param string
		 */
		public void setOrd_no(String string) 
		{
			ord_no = string;
		}
 
		/**
		 * @param string
		 */
		public void setOrd_rec_dt(String string) 
		{
			ord_rec_dt = string;
		}
 
	
 
	
		public Vector getPendingOrders(String id) {
			Vector vRow = new Vector();
			Vector pendingOrders=new Vector();
		  COrders pendingOrderInfo = null;
								 			
					 String sql="SELECT " 
								+"tpr_req_id ord_no, " 
								+"to_char(req_open_dt,'dd-mm-yyyy') ord_dt, "
								+"to_char(visit_date,'dd-mm-yyyy')  ord_rec_dt,"
								+" FROM  tablename "
								+" WHERE id="+id;			
				
          
					vRow = SQLManager.select(sql);
					String element;
				
					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);
							}	
					return pendingOrders;
					
				}
		
					
 
 
}

Open in new window

0
NASEEMA
Asked:
NASEEMA
  • 10
  • 8
1 Solution
 
Murali MurugesanFull stack Java developerCommented:
Please respond with the below details,

1. Count of total records on your table.?
2. Why is the return type of SQLManager.select(sql); - a vector ? to provide synchronized access?.
If Possible - paste the code for SQLManager.select
3. Are you using hibernate?

-Murali*







0
 
NASEEMAAuthor Commented:
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();
			}
		}
	}
	
	

Open in new window

0
 
NASEEMAAuthor Commented:
hi all,
     Any solution for my problem....
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Murali MurugesanFull stack Java developerCommented:
@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*
0
 
NASEEMAAuthor Commented:
Hi,
    thanks a lot for the reply. i will try now your suggestion and reply the post with the reply.



0
 
NASEEMAAuthor Commented:
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);
0
 
Murali MurugesanFull stack Java developerCommented:
It should be this way when u append a String

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

-Murali*
0
 
NASEEMAAuthor Commented:
HI,
   I tried your comment and the output is

Starting Time   1246858188977

ending Time      77797
0
 
Murali MurugesanFull stack Java developerCommented:
for which part of ur code did u try this?

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

0
 
NASEEMAAuthor Commented:
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)
0
 
Murali MurugesanFull stack Java developerCommented:
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);
                                                        }
0
 
NASEEMAAuthor Commented:
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?
0
 
Murali MurugesanFull stack Java developerCommented:
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.

0
 
NASEEMAAuthor Commented:
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?

SQLManager.txt
OrderList.txt
SupportPendingOrders.txt
0
 
Murali MurugesanFull stack Java developerCommented:
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*
0
 
NASEEMAAuthor Commented:
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.
0
 
Murali MurugesanFull stack Java developerCommented:
You need to scrap this whole method getLogCounter(String tprID,String seq_nr)

You are just getting count(*) for which why you need unnecessary Vector. Do a normal jdbc code and just return the int values/ or any object. Something like this...


public SupportPendingOrders getLogCounter(String tprID,String seq_nr) {
                                    SupportPendingOrders logCountInfo = null;
                                     String sql="SELECT COUNT(*)"
                                                       +" FROM   log"
                                                       +" WHERE  opn_id="+tprID
                                                       +" AND opn_flag ='TPR'"
                                                       +" AND opn_seq_nr="+seq_nr
                                                         +" AND upd_flag  IS NULL";      
                               //PreparedStatement - implement this
                                         //resultset - implement this

                                    if(rs.next()){
                                          logCountInfo = new SupportPendingOrders();
                                          logCountInfo.setLogCount(rs.getInt(1));                                                 
                                    }      
                              return logCountInfo ;                              
                              }

Additionally u can pass all ids as a string to the above qery and take the output in a Map and then iterate in a single loop.


I think already we have deviated a long distance from the actual question in this thread. I suggest you open a new post for further questions in this regard.

-Murali*
0
 
NASEEMAAuthor Commented:
Excellent solution
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now