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...

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?
LVL 1
Anthony6890Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.
Anthony6890Author Commented:
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?
Gary PattersonVP Technology / Senior Consultant Commented:
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 184.168.221.24
...
Ping statistics for 184.168.221.24:
    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.  

Alternatives

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?

http://www-01.ibm.com/support/docview.wss?uid=nas8N1010161

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:

INSERT INTO DEPARTMENT VALUES
  ('B11’,’PURCHASING’,’B01’),
  (’E41’,’DATABASE ADMINISTRATION’,’E01’)
...

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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony6890Author Commented:
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.

-Anthony
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.