Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Horrible JSP performance...

Posted on 2004-10-22
3
Medium Priority
?
409 Views
Last Modified: 2013-11-24
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.tld" prefix="nvs" %>

<%@ include file="validate.jsp" %>

<sql:setDataSource driver="com.informix.jdbc.IfxDriver"
    var="qdist"
    url="jdbc:informix-sqli://192.168.0.20:1527/dbserver:informixserver=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("internalRequest", "true");
    int stid = 0;
    try {
    stid = Integer.parseInt(request.getParameter("id"));
    } catch (NumberFormatException npe) {
        out.println("NOT A VALID ID!");
    }
    request.setAttribute("stid", "?id=" + String.valueOf(stid));
    request.setAttribute("address", ";jsessionid=" + sessionBean.getSessionId());
    request.setAttribute("categoryID", "&categoryId=2");
    request.setAttribute("subcategory", "&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:out 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" %>

0
Comment
Question by:w0lver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Expert Comment

by:petmagdy
ID: 12383951
dear sir,

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
0
 
LVL 21

Accepted Solution

by:
MogalManic earned 1500 total points
ID: 12392779
Like  petmagdy has suggested, you need to use or imlement a database connection pool.  Most servlet containers have connection pools so the easiest method is to use the servlet containers connection pool.  

What servlet container are you using?  Tomcat 5.0?

I would also consider writing a Java Servlet to do your database quering (Instead of the JSTL sql: tags).  Seperating the database logic out of the page has the folling advantages:
   1) Better conforms to the MVC (Model View  Controller) pattern
   2) Simplifies the code on the JSP page
   3) Allows for consolidation of repeated query code
   4) Allows for timing of the DB seperate from the JSP page
0
 
LVL 13

Expert Comment

by:petmagdy
ID: 12474346
their should be a split here author :)

Any way it's ok
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

609 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