MySQL Query result is very slow

Question

I am executing a simple query on a small table

SELECT * FROM SYSTEM

System table only has three columns(Id, Name, Progress) and 1300 rows.

My code for getting the data is:

    try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = (Connection) DriverManager.getConnection(
                    "jdbc:mysql://192.168.0.107:3306/my_database",
                    username.getText(), password.getText());
            String query = "select * from system";
            stmt = (Statement) conn.createStatement();
            rs = (ResultSet) stmt.executeQuery(query);
            while (rs.next()) {
                tableModel.addRow(new Object[] { rs.getInt("Number"),
                        rs.getString("Name"), rs.getFloat("Progress") });
            }
        } catch (Exception e) {
            // some other code
        }`

This code takes around 15 seconds to display the date in my JTable, while if I execute the query in phpmyadmin it takes less than one second.

Profile the code or add timing statements to see where the time is being spent. I think a table named SYSTEM is a bad idea. — duffymo
Is it the executeQuery or the time it takes to process the ResultSet that's slow? — MadProgrammer
First get the actual time from the beginning of the executeQuery statement and after to determine the actual time spent executing the query. — Rai
I just checked. The execution of the query is fine(~1s), the process of the results set is very slow. Besides using a thread, what should I do for the while loop to finish faster? — user3822347
@user3822347 Read my answer. The most important part is probably the last two sentences. — Elliott Frisch

Elliott Frisch Elliott Frisch · Accepted Answer · 2014-07-26T01:35:38

I would use a PreparedStatment and I would increase the default ResultSet fetchSize, and I would limit the query to the three columns (note id != Number) -

String query = "select Number, Name, Progress from system";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
rs.setFetchSize(250);

Finally, establishing a Connection like that is probably the wrong way to go for performance. You should look for a Connection pool like BoneCP or c3p0 or HikariCP.

MySQL Query result is very slow

2 Answers