Solved

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

Posted on 2009-07-02
18
169 Views
Last Modified: 2012-05-07
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
Comment
Question by:NASEEMA
  • 10
  • 8
18 Comments
 
LVL 13

Expert Comment

by:Murali Murugesan
ID: 24769648
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
 

Author Comment

by:NASEEMA
ID: 24772499
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
 

Author Comment

by:NASEEMA
ID: 24774073
hi all,
     Any solution for my problem....
0
 
LVL 13

Expert Comment

by:Murali Murugesan
ID: 24781940
@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
 

Author Comment

by:NASEEMA
ID: 24782673
Hi,
    thanks a lot for the reply. i will try now your suggestion and reply the post with the reply.



0
 

Author Comment

by:NASEEMA
ID: 24782722
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
 
LVL 13

Expert Comment

by:Murali Murugesan
ID: 24782741
It should be this way when u append a String

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

-Murali*
0
 

Author Comment

by:NASEEMA
ID: 24782811
HI,
   I tried your comment and the output is

Starting Time   1246858188977

ending Time      77797
0
 
LVL 13

Expert Comment

by:Murali Murugesan
ID: 24782819
for which part of ur code did u try this?

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

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:NASEEMA
ID: 24782951
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
 
LVL 13

Expert Comment

by:Murali Murugesan
ID: 24783122
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
 

Author Comment

by:NASEEMA
ID: 24783232
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
 
LVL 13

Expert Comment

by:Murali Murugesan
ID: 24783301
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
 

Author Comment

by:NASEEMA
ID: 24783819
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
 
LVL 13

Expert Comment

by:Murali Murugesan
ID: 24791230
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
 

Author Comment

by:NASEEMA
ID: 24791674
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
 
LVL 13

Accepted Solution

by:
Murali Murugesan earned 500 total points
ID: 24791730
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
 

Author Closing Comment

by:NASEEMA
ID: 31599389
Excellent solution
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article, I will show you HOW TO: Suppress Configuration Issues and Warnings Alert displayed in Summary status for ESXi 6.5 after enabling SSH or ESXi Shell.
In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now