Wht would an Insert Into query be so much slower to my local iSeries vs my remote iSeries when running through MS Access, with the exact same configurations...

Anthony6890 used Ask the Experts™
HI All,

I've been trying to figure out why there would be such a difference in doing an Insert Into query to my local iSeries vs my remote iSeries through MS Access.  I have a simple query that has about 3800 rows of data and 4 columns.  There are keyed values in both the table on the iseries and in access.  When I do the Insert Into query to the iSeries in my building, it is super fast, just as quick as doing a file transfer.  When I do the same query to my remote iSeries, it takes super long and appears to do it in junks.  Both ODBC configurations are the same and I'm puzzled as to what is going on.  My current work around is to do a file transfer outside of Access, but I'd really like to just put the data right into the table through Access without opening another table.  

Has anyone dealt with this before?  Any suggestions on things I can try or look at to make the data transfer quicker through Access?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Access and ODBC are intended to work over a LAN.  They do work over the internet but as you can see, performance is an issue.  The average LAN is 100 times faster than the average internet connection.  That may give you some insight.


Thanks Pat.  So what is the difference between doing a Transfer via odbc and doing one via file transfer. Why wouldn't I see the same performance issue when I do the same upload via IBM's file transfer tool?
VP Technology / Senior Consultant
If I understand the question, you have three scenarios:

1) INSERT INTO from local Access table to local iSeries DB2 via ODBC - 3800 rows of 4 columns - Fast
2) INSERT INTO from local Access table to remote iSeries DB2 via ODBC - 3800 rows of 4 columns - much slower than scenario #1.
3) IBM i Access File Transfer upload (of a local file extracted from Access) to remote iSeries DB2 - 3800 rows of 4 columns - Much faster than scenario #2.

In order to troubleshoot, we'd need to know what Access is doing.  But I'm willing to risk a guess, based on what you're seeing:  my guess is that Access is mapping you one INSERT INTO with a local subselect (I assume) to 3,800 individual INSERTs, and sending them one at a time via ODBC to IBM i DB2 - with all the overhead that goes with that.  

When you are local, the round trip communications time is very fast, so you don't see a big delay versus some sort of more efficient bulk upload method (like the File Transfer function).  But when you are remote, the round trip response time greatly extends the amount of time it takes to send each row, wait for IBM i DB2 to process the insert, and then wait for the acknowledgement to come back.

One "quick and dirty" way to test this theory is to PING the iSeries when you are local and remote.  Jot down the response time you see on the ping, and calculate the difference.  In the example below, I ping a local IP address, and then a remote one.  I did this on my network:

Local ping:
ping 192.168.x.1
Ping statistics for 192.168.x.1:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 10ms, Maximum = 16ms, Average = 12ms

Remote ping:
Ping statistics for
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 159ms, Maximum = 253ms, Average = 208ms

So we went from an average of 16ms local, to 208ms remote.  Let's apply that overhead to 3,800 transactions:

3800 * 16ms  = 60,800ms = 60.8s ~= 1 minute communications overhead
3800 * 208ms = 790,400ms = 790.4s ~= 13 minutes 10seconds communications overhead

So 1 minute vs over 13 minutes, for just 3800 transactions, and that assumes just one round-trip is required per row.  Could take more - I'd need to see a trace to know for sure.

Anyway, the bigger the difference, the bigger the penalty, and the longer it will take to perform a remote upload.

You can also validate this by running an IBM i DB2 SQL Performance Monitor (Navigator is the easiest way to do it, but be careful, on a busy system this can create an enormous load if you don't get the monitor restricted to just the job you are interested in.  If you've never run one, get an experienced DBA to help you the first time.  I've seen improperly configured monitors bring down entire systems).  

The output from the monitor session will show you all of the queries that were sent to the IBM i via ODBC.

On the client side, you could also use ODBC tracing, or even a Wireshark trace on the client side to see what is happening during your Access session.

Generally, the File Transfer tool in IBM i Access is going to give you the best transfer times.  It will transfer data in an efficient manner, minimizing round trips.  


Have you considered just automating exporting data and using the IBM File Transfer function directly from Access:  dump to a CSV out of Access, for example, and then execute the "rfrompcb.exe" command to automatically run the transfer session?


If you want to do it all in Access without relying on an outside utility like rfrompcb, or if you are unable to do that due to restrictions, then you'd probably need to figure out how to force Access to generate a bulk INSERT statement (or several bulk INSERT statements), instead of 3,800 individual INSERTs.  

You could probably dynamically build a bulk INSERT in your Access program, and then execute as a pass-through query:


You will need to make sure you don't exceed whatever length limit Access imposes on a pass-though query (a quick Google turned up mention of a 64K limit in the past, don't know if that still applies or not).  You might end up having to break it down into multiple INSERTS - but even if you have to do it 500 rows at a time, I suspect it would be substantially faster to run eight 500-row bulk inserts vs 3,800 single row inserts when you have high latency (big ping time).


Gary, I did execute the ping tests locally and remote and noticed a difference of 26 ms slower to hit the remote system.  I'm going to look at the suggestions you provided.  I currently do the export from Access to a text file and then have a file transfer run to do the upload, but I'm going to look into the rfrompcb.exe command to see if that will alleviate some of the manual prompts I get when doing the transfer.  

Thanks again.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial