'Slow return of query resultset using JT400 driver & JDBC over WAN
I have the following situation. We are switching from a locally managed AS400/DB2 System-i machine, to a cloud based/hosted solution.
The real problem now occurs when executing a JDBC query (using the JT400 toolbox driver), that it takes enormous time longer while retrieving the result set, then before. The setup & versions of both machines are identically, only difference is network (one in LAN, other Remote). The connection time, latency, bandwidth is all ok.
To illustrate, timings on the old and new server:
OLD (local LAN):
SimpleConnectionTest: TimeSpent :
SimpleConnectionTest: - connection : 140 ms
SimpleConnectionTest: - query : 282 ms
SimpleConnectionTest: - results : 1140 ms
SimpleConnectionTest: |============================================|
SimpleConnectionTest: | Total Test Duration: 1422 ms |
SimpleConnectionTest: |============================================|
NEW (WAN):
SimpleConnectionTest: TimeSpent :
SimpleConnectionTest: - connection : 312 ms
SimpleConnectionTest: - query : 346 ms
SimpleConnectionTest: - results : 16497 ms
SimpleConnectionTest: |============================================|
SimpleConnectionTest: | Total Test Duration: 16843 ms |
SimpleConnectionTest: |============================================|
As you can see, there is almost a factor x15 slower in returning the resultset back to the client. (connection times are a little slower, but not that much, query times are also not that big).
Is it possible that for some reason something is preventing big resultsets to be returned when not sitting on the same Local LAN ? Is there an optimization ?
It has nothing to do with the query itself, data and setup are both the same on both machines, also did an inspection with the iseries explain tools. nothing there to learn...
All suggestions are welcome !
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|