Hello everyone,

I am populating a dropdown list in my webapp, from a MySQL database table. There are around 160,000 records in the table. When I start my webapp and when populating the content of the drop-down, it takes incredible amount of time, and eventually the browser crashes. I am not sure if this is normal for 160k records, or I maybe doing something incorrect?
HEre's what I have done. **Note: If I choose to load a small amount of records, say 100, then there's no problem at all.

My servlet:

protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setAttribute("ClientInfo", (ArrayList<ClientInfo>)getClientResult());
        request.getRequestDispatcher("/myJSP.jsp").forward(request, response);
    }

    public ArrayList<ClientInfo> getClientResult() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        ArrayList<ClientInfo> ClientInfoList = new ArrayList<ClientInfo>();

        try {

            conn = cltUtils.getMySQLConnection();

            st = conn.createStatement();
            rs = st.executeQuery("SELECT client_id, client_name FROM client_info where client_title != ''");

            while (rs.next()) {

                String client_id = rs.getString("client_id").trim();
                String client_name = rs.getString("client_name");               
                ClientInfo ci = new ClientInfo(client_id, client_name);
                ClientInfoList.add(ci);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
        return ClientInfoList;
    }

Here's my JSP portion, where it reads the data and populates it.

<% ArrayList<ClientInfo> cil=(ArrayList<ClientInfo>)request.getAttribute("ClientInfo"); %>
<select id="client" class="form-control">
<% 
for (int i=0; i < cil.size(); i++) {
    ClientInfo ci = (ClientInfo)cil.get(i);
%>
    <option value="<%=bi.getClientId() %>"><%=ci.getClientName() %></option>
<%} %>
</select>

Is this an optimal way of doing this? or there's a better way to improve the performance?

Appreciate input/feedbacks. Thanks in advance.

Any suggestions, please?

Could you explain the point of 160k items in a dropdown.

i am planning to use jquery to use an editable drop-down, as the user types, the drop-down filters the result. I have a collection of records, ~160k. The requirements to make the drop-down loaded with these items and make it filterable [similar to how google search works, when user starts typing, the items can be filtered as the user types in. I can make that part.] however, the items need to be loaded initially.

I'd be open to other ideas...

jQuery UI has an autocomplete control. On typing it requests new items from the server so there is no need to load them all at first.

160k items is way too many for a dropdown. Indeed use an autocomplete or find some other means to reduce the size of the list before you try to fill it.
Something like a master-detail chain, where you select categories, subcategories, etc. etc., all the while building a reduction clause for your table.
Eventually you should end up with a small set that people can sensibly scroll through to find the final value they want. For that, even a hundred is really too much, a dozen is more like it.

Also, make sure you have properly indexed your table(s), that makes selecting records from them a lot faster.

As you're using Java, a good thing to do would be to use JSF and Primefaces, which contains a very nice autocomplete component which will work with your POJOs.
Here's an example from code I am working on for a customer:

<p:autoComplete id="naamTweedeInspecteur" value="#{waarnemingBean.waarneming.tweedeInspecteur}" 
                rendered="#{waarnemingBean.waarneming.soortWaarneming eq strings.SoortWaarnemingFysiek}"
                completeMethod="#{waarnemingBean.zoekInspecteurs}" readonly="#{waarnemingBean.waarneming.id != null}"
                var="insp" itemLabel="#{insp.displayName}" disabled="#{waarnemingBean.waarneming.id != null}"
                itemValue="#{insp}" forceSelection="true" converter="#{nvwaInspecteurConverter}">
  <p:ajax event="itemSelect" listener="#{waarnemingBean.onInspecteurSelect}" update="messages"/>  
  <p:column>#{insp.displayName}</p:column>
</p:autoComplete>                                    

Use EJBs and JPA to get the data, rather than write raw JDBC code.

IOW bring your application development into the 21st century...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.