Java Servlet DB Query with Ajax - slow query time and querystring not always fully passed to the servlet

Question

I'm trying to create a AJAX based SQL query with Java EE and Servlets. I'm using Glassfish 3.01 and MS SQL server with Jquery on the client side. I put everything together, and bind my ajax function to the textfield's onkeyup event. But sometimes When I put 'teststring' into the textbox only "teststrin" passed to the Servlet. So basically the last char disappears and therefore the query result is not correct. Not to mention when the resultset contains large amount of data the query is pretty slow. Could you please check if I'm doing something wrong on the server and client side?

On the client side I have this JQuery function:

  function ajaxSearch(sstring) {

if (sstring.length < 3)
  {
      $("#external").html("
at least 3 chars please....")
  }
  else
  {
    $('#loading').ajaxStart(function() {
      $(this).show()
      $("#external").hide()
    });

$('#loading').ajaxComplete(function() {
  $(this).hide()
  $("#external").show()
});

$.ajax({
    type:"GET",
    url: "/myApp/getStd",
    dataType: "application/x-www-form-urlencoded",
    data: "sstring="+escape(sstring),
            async: true,
    success: function(data){
        $("#external").html(data);

    }
 })


}
}

On the server side I have this:


@WebServlet(name="getStd", urlPatterns={"/getStd"})
    public class getStd extends HttpServlet {
   @Override
   public void doGet (HttpServletRequest req, HttpServletResponse res) throws           ServletException, IOException {
            Connection conn = null;
            Statement stmt = null;  
            ResultSet rs = null;
            ArrayList rows = new ArrayList();
            res.setCharacterEncoding("UTF-8");
            res.setContentType("text/html");
            PrintWriter out =  res.getWriter();
            String sql=null;
            String test= req.getParameter("sstring");
    try{
            InitialContext cxt = new InitialContext();
            if (cxt == null) {
             throw new Exception("Uh oh -- no context!");}
           DataSource ds = (DataSource) cxt.lookup( "jdbc/Sample" );
           conn = ds.getConnection();
           stmt = conn.createStatement();
           sql="Select * from MYDB.dbo.testdb where myField like '%"+req.getParameter("sstring")+"%';";
           rs = stmt.executeQuery(sql);

           while(rs.next()){
            stdRecord cols = new stdRecord();
            cols.setTeljel(rs.getString("Field1"));
            cols.setTitle(rs.getString("Field2"));
            cols.setICS(rs.getString("Field3"));
            cols.setREF(rs.getString("Field4"));
            rows.add(cols);
           }

           req.setAttribute("std", rows);
           req.setAttribute("query",test );
           req.getRequestDispatcher("/showRes.jsp").forward(req, res);
           // close everything to free up resources
           rs.close();
           rs = null;
           stmt.close();
           stmt = null;
           conn.close(); /
           conn = null;  
           rows=null;
        } catch (SQLException e) {
            e.printStackTrace(out);
        } catch (Exception e) {
            e.printStackTrace(out);
       } finally {
            if (rs != null) {
              try { rs.close(); } catch (SQLException e) { ; }
              rs = null;
    }
           if (stmt != null) {
            try { stmt.close(); } catch (SQLException e) { ; }
            stmt = null;
    }
          if (conn != null) {
           try { conn.close(); } catch (SQLException e) { ; }
           conn = null;
    }

       }
    }
  }

Thanks in advance.

BalusC BalusC · Accepted Answer · 2011-02-21T19:34:37

As to the lag in keyup, I think this is related to the performance issue, so let's fix that first and then review afterwards.

As to the performance, you've given very little information about your setup, but two common solutions which are often overlooked by starters are the following:

Use a connection pooled DataSource instead of DriverManager. This saves the cost of connecting the DB on every query (which can take over 200ms while a pooled connection is returned in no-time). Consult the JNDI resource config documentation of the application server in question for details (hint: admin console).
Limit the resultset size in SQL side instead of in Java side. This saves the cost of transferring irrelevant data over network. Just return the top 10 results or something instead of the entire table. Consult the SQL manual of the database in question for details (hint: SET ROWCOUNT).

Java Servlet DB Query with Ajax - slow query time and querystring not always fully passed to the servlet

1 Answers