asked on

Optimizing JSP load time

Hi all,
I have a jsp page thats displaying a table fetched from the Database. To get the desired output as presentation, I am using two SQL queries. This Page is taking too long to get load, atleast 34 seconds. Please suggest me a way to make it faster.
The code along with the Page snap shot and DB Schema are attached.
Someone suggested me a way to do it, but I couldnt figure out. heres the link for that:
https://www.experts-exchange.com/questions/23985310/Oracle-Query-Optimization.html

<%@ page import="java.text.DateFormat,java.text.SimpleDateFormat,java.util.*,java.sql.*" %>
<body>
<%
SimpleDateFormat df = new SimpleDateFormat("EEE - MMM d, yyyy");
Calendar origDay = Calendar.getInstance();
Calendar prevDay = (Calendar) origDay.clone();
prevDay.add (Calendar.DAY_OF_YEAR, -1);
%>
 
<%! public ArrayList getDays(){
 
ArrayList dateList = new ArrayList();
DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
Calendar cal =  Calendar.getInstance();
dateFormat.format(cal.getTime());
 
for(int i =0; i <=6; i++)
{
cal.add(Calendar.DATE, -1);
dateList.add(dateFormat.format(cal.getTime()));
 
}
//System.out.println("Last seven date"  + dateList );
return dateList;
}
%>
<%
java.util.ArrayList days= getDays();
//out.print(days);
%>
<h5><b>Current Status as of <%= df.format(prevDay.getTime()) %></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
<tr>
<th><b>Channel Group</b></th>
<th><b> Metric </b></th>
<% for( int i=0; i<days.size(); i++){%>
<th ><%=days.get(i)%></th>
<%}%>
<th><b>Minimum SLA </b></th>
</tr>
<%
Connection conn= null;
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@sd.xyz.com:T02","user", "pass");
 
String sql= "",sqldata="" ;
PreparedStatement pstmt= null,pstmtdata=null;
ResultSet rs = null , rsdata=null;
 
 
try{
sql ="SELECT CHANNELGROUP FROM  SLAND.METRICPAGE_TABLE GROUP BY CHANNELGROUP ORDER BY CHANNELGROUP ";
 
pstmt= conn.prepareStatement(sql);
rs= pstmt.executeQuery(sql);
 
while(rs.next()){
ArrayList success = new ArrayList();
ArrayList volume  =  new ArrayList();
ArrayList hightlight  =  new ArrayList();
// here
sqldata="select SUCCESS_RATE , VOLUME, NVL(to_char(D, 'mm/dd/yyyy'),' ') EASTCOAST_DATE from  SLAND.METRICPAGE_TABLE, ( SELECT TRUNC(SYSDATE) - LEVEL + 1 D FROM DUAL CONNECT BY LEVEL <=7) where CHANNELGROUP(+) ='"+rs.getString(1)+"' and EASTCOAST_DATE(+) = D order by D desc";
pstmtdata= conn.prepareStatement(sqldata);
//pstmtdata.setString(1,rs.getString(1));
rsdata= pstmtdata.executeQuery(sqldata);
%>
 
<tr>
<th rowspan=2 ><%=rs.getString(1)%></th>
 
<td> success </td>
<%
while(rsdata.next()){
 
float f = rsdata.getFloat(1);
if( f < 87 )
hightlight.add(new Integer(1)); // true
else
hightlight.add(new Integer(0)); // false
success.add(new Float(f)) ;
 
volume.add( new Integer(rsdata.getInt(2)));
//System.out.println(rs.getString(1)+"---"+ rsdata.getInt(1)+ "----"+ rsdata.getInt(2)+ "----"+ rsdata.getDate(3)   );
}
for(int s=0; s < success.size(); s++  ){
 
int h = ((Integer)hightlight.get(s)).intValue();
if(h==1)
out.print("<td style='background-color:#E33740'>"+success.get(s)+"%</span></td>")  ;
else
out.print("<td>"+success.get(s)+"%</td>")  ;
 
}
out.print("<td>"+87+"</td>");
%>
<td></td>
</tr>
<tr>
<td>volume </td>
 
<% for(int v=0; v < volume.size(); v++  ){
 
out.print("<td>"+volume.get(v)+"</td>")  ;
}
 
volume=null;
success=null;
 
}
 
//System.out.println(mdata);
conn.close();
rs.close();
rsdata.close();
pstmt.close();
pstmtdata.close();
}catch(Exception e){
 
e.printStackTrace();
conn=null;
 
}%>
 
</table>

Open in new window

md.bmp
md-sch.JPG

Kuldeepchaturvedi

I have not tested the code yet so chances are that it will blow up.. but the logic should be correct..
I have used the query from your previous question and then have tried to make two loops without firing 2 queries... try it and see how it goes..

<%@ page import="java.text.DateFormat,java.text.SimpleDateFormat,java.util.*,java.sql.*" %>
<html>
<body>
<%
SimpleDateFormat df = new SimpleDateFormat("EEE - MMM d, yyyy");
Calendar origDay = Calendar.getInstance();
Calendar prevDay = (Calendar) origDay.clone();
prevDay.add (Calendar.DAY_OF_YEAR, -1);
%>
 
<%! public ArrayList getDays(){
 
ArrayList dateList = new ArrayList();
DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
Calendar cal =  Calendar.getInstance();
dateFormat.format(cal.getTime());
 
for(int i =0; i <=6; i++)
{
cal.add(Calendar.DATE, -1);
dateList.add(dateFormat.format(cal.getTime()));
 
}
return dateList;
}
%>
<%
java.util.ArrayList days= getDays();
%>
<h5><b>Current Status as of <%= df.format(prevDay.getTime()) %></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
<tr>
<th><b>Channel Group</b></th>
<th><b> Metric </b></th>
<% for( int i=0; i<days.size(); i++){%>
<th ><%=days.get(i)%></th>
<%}%>
<th><b>Minimum SLA </b></th>
</tr>
<%
Connection conn= null;
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@sd.xyz.com:T02","user", "pass");
 
String sql= "",sqldata="" ;
PreparedStatement pstmt= null,pstmtdata=null;
ResultSet rs = null , rsdata=null;
 
 
try{
sql ="SELECT chgroup, (rate || '%') SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EASTCOAST_DATE from  SLAND.METRICPAGE_TABLE, (    SELECT TRUNC(SYSDATE) - LEVEL + 1 d
                            FROM DUAL
                      CONNECT BY LEVEL <= 7)
   WHERE  date(+) = d
ORDER BY chgroup, d DESC"; 
pstmt= conn.prepareStatement(sql);
rs= pstmt.executeQuery(sql);
ArrayList success = new ArrayList();
ArrayList volume  =  new ArrayList();
ArrayList hightlight  =  new ArrayList();
if(rs.next()){
String chggrp =rs.getString(1);
%>
boolean loop=true;
boolean innerloop=true;
while(loop){
boolean innerloop=true;
<tr>
<th rowspan=2 ><%=chggrp%></th>
<td> success </td>
 
while(innerloop)
{
	float f = rs.getFloat(2);
if( f < 87 )
	hightlight.add(new Integer(1)); // true
else
	hightlight.add(new Integer(0)); // false
success.add(new Float(f)) ;
volume.add( new Integer(rsdata.getInt(3)));
if(!rs.next())
{
innerloop=false;
loop=false;
}
if(!rs.getString(1).equals(chggrp))
innerloop=false;
chggrp=rs.getString(1);
}
for(int s=0; s < success.size(); s++  ){
 
int h = ((Integer)hightlight.get(s)).intValue();
if(h==1)
out.print("<td style='background-color:#E33740'>"+success.get(s)+"%</span></td>")  ;
else
out.print("<td>"+success.get(s)+"%</td>")  ;
}
out.print("<td>"+87+"</td>");
%>
<td></td>
</tr>
<tr>
<td>volume </td>
 
<% for(int v=0; v < volume.size(); v++  ){
 
out.print("<td>"+volume.get(v)+"</td>")  ;
}
 
volume=null;
success=null;
 
}
 
//System.out.println(mdata);
conn.close();
rs.close();
rsdata.close();
pstmt.close();
pstmtdata.close();
}catch(Exception e){
 
e.printStackTrace();
conn=null;
 
}%>
 
</table>
</body>
</html>

Open in new window

humanonomics

I don't get the purpose of the first query, I feel you can join the two queries and get the data in one go rather than hitting the database twice