RE: Slow JDBC access to AS/400 (over 56k remote connection)

We have had the same problem.  We have been building a large Java
client/Server application (Swing base, ~1000 classes)  for a little over a
year now.  Due to intense GUI requirements the application was designed for
a 10 Mb/sec connection that works great with that bandwidth.  Using it at
56K is nearly impossible due to data transfer rates via JDBC with the
AS/400.  At 56K the application takes about 20-30 minutes to start compared
with approximately 20-30 seconds with a 10Mb/sec connection (this is with
jars preloaded on the client).

An IBM consultant from Rochester suggested looking into sockets or RMI and
doing more on the AS/400.  The problem with sockets is that you will need to
create your own messaging protocol to make any gains.  The secret here in to
design a messaging system that minimizes the transfer of data.  You can also
use tricks like compression to speed things up.  However, depending on the
application and its data requirements this path is non-trivial and still may
gain you very little.  In our case it would have required a very large
amount of work and the gains were very hard to predict.

We did do some experimentation with RMI (you can think of RMI in this case
as a socket protocol where the messaging is done for you) and were able to
quickly added it to parts of our application.  However, we saw no
performance gain and in some case and things actually slowed down
considerably depending on the AS/400 load. [Some of it may have been caused
by incorrect settings on the AS/400 but it was not clear to our AS/400 guys
what needed to be done to speed things up.]  Either way, we abandon the RMI
approach since it greatly increased the complexity of the application with
very little performance gains.  [Warning - a number of RMI examples I have
seen from IBM show windowing events and property changes being sent from the
client GUI to an object on the server for processing.  This will only work
for very simple GUIs.  Users quickly become frustrated with GUIs that do not
perform in very rapidly.  Since property changes and windowing events are
very common the overhead of sending them to the server will drag any complex
GUI to in knees.]


Here are a few suggestions that we are using to speed up connections.
Sadly, I suspect that non of these suggestions will help at 56K but they
will help when connecting via Cable modem or DSL.

1) Use latest version of Toolbox for AS/400.
2) Cache non-changing data at the client.
3) Use prepared statements for all repeated SQL statements.  
4) Use new JDBC features such as batching and cursoring when appropriate.
5) Delay the retrieval of data until it is really necessary.  We use lazy
initialization and retrieval and lots of different GUI tricks to make this
happen.
6) Only get the columns (fields) you really need.
7) Use a compression protocol between the client and server when bandwidth
is limiting.  
8) Make sure to use a good model/view separation. It will make it a lot
easier to see and attack trouble spots.




/Tim


+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---