CODEWITHSUNDEEP
javamysqlsqljdbc
user3822347
user3822347

Reputation: 81

MySQL Query result is very slow

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.

Upvotes: 4

Views: 2879

Answers (2)

JimmyB
JimmyB

Reputation: 12610

The issue here was likely the

while (rs.next()) { tableModel.addRow(...)

Each call to addRow() means a "Notification of the row being added will be generated."

This notification of the Listener(s) means quite some overhead. Likely, each call indirectly involves a call to invalidate the GUI's state, and more, because the table will have to be redrawn after it changed. Doing that 1300x in ~15s means 100 of those calls per second, which is quite reasonable. The OP should probably look for a way to first gather all data and then update the table model only once with all the data. This may mean having to implement his own variation of DefaultTableModel or AbstractTableModel.

Upvotes: 2

Elliott Frisch
Elliott Frisch

Reputation: 201399

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.

Upvotes: 1

Related Questions