w0lver
asked on
Horrible JSP performance...
I have a page that makes about a 5 to 12 queries against a database and builds the page. The page is very slow... I thought the issue was in the database side of things so I concentrated on indexes and the like to speed up the queries. The good news is the raw queries each take about 0.03 seconds to execute down from about 0.10 per query. However, all was for naught, the page is still slow. Is there a better / faster way to do what I'm doing? Maybe use a SQL GroupBy? What do you think?
Thanks in advance,
Ross
Here's the page:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ page import="java.util.Calendar " %>
<%@ taglib uri="/WEB-INF/jsp/taglib.t ld" prefix="nvs" %>
<%@ include file="validate.jsp" %>
<sql:setDataSource driver="com.informix.jdbc. IfxDriver"
var="qdist"
url="jdbc:informix-sqli:// 192.168.0. 20:1527/db server:inf ormixserve r=iwc"
user="appuser"
password="informix"
/>
<%@ include file="template_top.jsp" %>
<sql:query var="series"
dataSource="${qdist}">
select unique(series) from printers_ink where manufacturer='HP';
</sql:query>
<table cellSpacing="0" cellPadding="0" width="580" border="0" id="table6">
<tr>
<td colspan="3">
<img src="/images/Ink_19.jpg">
</td>
</tr>
<%
request.setAttribute("inte rnalReques t", "true");
int stid = 0;
try {
stid = Integer.parseInt(request.g etParamete r("id"));
} catch (NumberFormatException npe) {
out.println("NOT A VALID ID!");
}
request.setAttribute("stid ", "?id=" + String.valueOf(stid));
request.setAttribute("addr ess", ";jsessionid=" + sessionBean.getSessionId() );
request.setAttribute("cate goryID", "&categoryId=2");
request.setAttribute("subc ategory", "&subcategoryId=");
%>
<c:forEach items="${series.rows}" var="row">
<tr>
<td bgColor="#ead143" colSpan="3" height="20" style="font-family: verdana, arial, helvetica, sans-serif; font-size: 12px; color: #666666">
<font class="cartridgeName"><b>& nbsp;<c:ou t value="${row.series}" /></b></font></td>
</tr>
<sql:query var="names"
dataSource="${qdist}">
SELECT * FROM printers_ink WHERE series = ? ORDER BY printer_name
<sql:param value="${row.series}" />
</sql:query>
<c:set var="count" value="0" />
<c:forEach items="${names.rows}" var="col">
<c:if test="${col.category_id != null}">
<c:set var="url" value="http://www.domain.com/arw/subcategory_select.jsp"/>
</c:if>
<c:if test="${col.category_id == null}">
<c:set var="url" value="http://www.domain.com/arw/outofstock.jsp"/>
</c:if>
<c:if test="${count == '2'}">
<td width="33%" style="font-family: verdana, arial, helvetica, sans-serif; font-size: 10px;">
<a href="<c:out value="${url}" /><c:out value="${address}" /><c:out value="${stid}" /><c:out value="${categoryID}" /><c:out value="${subcategory}" /><c:out value="${col.category_id}" />">
<c:out value="${col.printer_name} " /> </a></td>
</tr>
<c:set var="count" value="3" />
</c:if>
<c:if test="${count == '1'}">
<td width="33%" style="font-family: verdana, arial, helvetica, sans-serif; font-size: 10px;">
<a href="<c:out value="${url}" /><c:out value="${address}" /><c:out value="${stid}" /><c:out value="${categoryID}" /><c:out value="${subcategory}" /><c:out value="${col.category_id}" />">
<c:out value="${col.printer_name} " /> </a></td>
<c:set var="count" value="2" />
</c:if>
<c:if test="${count == '0'}">
<tr>
<td width="33%" style="font-family: verdana, arial, helvetica, sans-serif; font-size: 10px;">
<a href="<c:out value="${url}" /><c:out value="${address}" /><c:out value="${stid}" /><c:out value="${categoryID}" /><c:out value="${subcategory}" /><c:out value="${col.category_id}" />">
<c:out value="${col.printer_name} " /> </a></td>
<c:set var="count" value="1" />
</c:if>
<c:if test="${count == '3'}">
<c:set var="count" value="0" />
</c:if>
</c:forEach>
</c:forEach>
</table>
<%@ include file="template_bottom.jsp" %>
Thanks in advance,
Ross
Here's the page:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ page import="java.util.Calendar
<%@ taglib uri="/WEB-INF/jsp/taglib.t
<%@ include file="validate.jsp" %>
<sql:setDataSource driver="com.informix.jdbc.
var="qdist"
url="jdbc:informix-sqli://
user="appuser"
password="informix"
/>
<%@ include file="template_top.jsp" %>
<sql:query var="series"
dataSource="${qdist}">
select unique(series) from printers_ink where manufacturer='HP';
</sql:query>
<table cellSpacing="0" cellPadding="0" width="580" border="0" id="table6">
<tr>
<td colspan="3">
<img src="/images/Ink_19.jpg">
</td>
</tr>
<%
request.setAttribute("inte
int stid = 0;
try {
stid = Integer.parseInt(request.g
} catch (NumberFormatException npe) {
out.println("NOT A VALID ID!");
}
request.setAttribute("stid
request.setAttribute("addr
request.setAttribute("cate
request.setAttribute("subc
%>
<c:forEach items="${series.rows}" var="row">
<tr>
<td bgColor="#ead143" colSpan="3" height="20" style="font-family: verdana, arial, helvetica, sans-serif; font-size: 12px; color: #666666">
<font class="cartridgeName"><b>&
</tr>
<sql:query var="names"
dataSource="${qdist}">
SELECT * FROM printers_ink WHERE series = ? ORDER BY printer_name
<sql:param value="${row.series}" />
</sql:query>
<c:set var="count" value="0" />
<c:forEach items="${names.rows}" var="col">
<c:if test="${col.category_id != null}">
<c:set var="url" value="http://www.domain.com/arw/subcategory_select.jsp"/>
</c:if>
<c:if test="${col.category_id == null}">
<c:set var="url" value="http://www.domain.com/arw/outofstock.jsp"/>
</c:if>
<c:if test="${count == '2'}">
<td width="33%" style="font-family: verdana, arial, helvetica, sans-serif; font-size: 10px;">
<a href="<c:out value="${url}" /><c:out value="${address}" /><c:out value="${stid}" /><c:out value="${categoryID}" /><c:out value="${subcategory}" /><c:out value="${col.category_id}"
<c:out value="${col.printer_name}
</tr>
<c:set var="count" value="3" />
</c:if>
<c:if test="${count == '1'}">
<td width="33%" style="font-family: verdana, arial, helvetica, sans-serif; font-size: 10px;">
<a href="<c:out value="${url}" /><c:out value="${address}" /><c:out value="${stid}" /><c:out value="${categoryID}" /><c:out value="${subcategory}" /><c:out value="${col.category_id}"
<c:out value="${col.printer_name}
<c:set var="count" value="2" />
</c:if>
<c:if test="${count == '0'}">
<tr>
<td width="33%" style="font-family: verdana, arial, helvetica, sans-serif; font-size: 10px;">
<a href="<c:out value="${url}" /><c:out value="${address}" /><c:out value="${stid}" /><c:out value="${categoryID}" /><c:out value="${subcategory}" /><c:out value="${col.category_id}"
<c:out value="${col.printer_name}
<c:set var="count" value="1" />
</c:if>
<c:if test="${count == '3'}">
<c:set var="count" value="0" />
</c:if>
</c:forEach>
</c:forEach>
</table>
<%@ include file="template_bottom.jsp"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
their should be a split here author :)
Any way it's ok
Any way it's ok
The bad news is
1- that u r accessing the data base using custom tags, hence u r not using the Application server Datasource which provide connection pooling services, connection pooling will make sure to optimize the Database connection open / close operation, forward the request to a servlet that will be responsible for accessing the database and forwarding the results as memory collections (like Vectors) for the JSP
2- If the custom tags u r using to access database each time will open / close database connection then that means that to process this page multiple Open/close DB connection is done based on the number of SQL statment, if a servlet is responsible to do the required DB operations it will make sure that those multiple SQL operations will happen within one Open/close Connection
so here is the bad news change the Code in the way I mentioned