wood burning stoves 2.0*
The moose likes JDBC and the fly likes 6,000-Row View to JTable Painfully Slow Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "6,000-Row View to JTable Painfully Slow" Watch "6,000-Row View to JTable Painfully Slow" New topic
Author

6,000-Row View to JTable Painfully Slow

Brian Batson
Greenhorn

Joined: Jun 15, 2011
Posts: 11
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:



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
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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.
 
 
subject: 6,000-Row View to JTable Painfully Slow