Populate Dropdown from DB - Performance

[new_2_java]

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.

[new_2_java]

Any suggestions, please?

[pritaeas]

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

[new_2_java]

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...

[pritaeas]

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.

[jwenting]

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...