6,000-Row View to JTable Painfully Slow

[Brian Batson]

Hello,

I have created a view on SQL Server 2008, which I am accessing through ODBC in my Java code. The view has around 20 columns, and around 6,000 records. It runs in under a second within SQL Server.

However, when I loop through the ResultSet to populate a DefaultTableModel, which feeds a JTable, it is horribly slow. It actually takes about 8 to 12 seconds just to return 100 records or so, which renders the application just about useless.

I tried the approach outlined here: http://tips4java.wordpress.com/2009/03/12/table-from-database/ , but it actually seemed to run even slower.

My approach/pseudo code right now is:

String Data[][] = null;
String Col[] = {column headers...};
DefaultTableModel dtm = new DefaultTableModel(Data, Col);
JTable jtbl = new JTable(dtm);
String sql = "SELECT...";
Statement stmt = myConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
Resultset rs = stmt.executeQuery(sql);
While (rs.next()) {dtm.addRow(new Object[]{rs.get...}}
rs.close();
stmt.close();

I think this is a pretty standard approach, right? Or hopefully I am doing something very wrong to cause a 1-second view to take more than 10 minutes to load into a JTable? I'm pretty stuck. Any advice/guidance is very much appreciated.

Cheers,
Brian

[Paul Clapham]

It's hard for the uninformed observer to guess where your performance problem is. I have code which loads up several JTrees with 10,000 nodes each in much less than a minute, so there's something wrong with your 10 minutes.

I would start by using a forward-only ResultSet (I never use any other kind) since you don't ever actually scroll through it.

And I would suggest profiling your application to see what is taking up most of the time.

[Martin Vashko]

1) Do not use ODBC, use some Type 4 driver. There are Microsoft's own Type 4 drivers , and someone uses jTDS. Perhaps there are other possibilities. This might not be connected to your problem though.

2) Are you sure you've got the SQL Server timing right? Most SQL clients measure the time it takes to execute the query and obtain the first row (or first few rows). Fetching all rows in a resultset is a completely different matter.

3) You're inserting the rows into the table model which is already part of the table. For every inserted row, the table has to update itself. This is probably not the problem, but my advice would be to load all the data using a separate method (say, into a list) and then populate the table model with it (and then set the model to the JTable). That way you can separate the database code and Swing code (which is good design in itself), and also measure the performance of these two independently, thus being able to tell with certainty which part of the code takes long time.