Issue executing Update Query via ODBC to a remote iSeries in Access 2010

Hi All,

Recently my organization migrated our local iSeries to a virtual instance in a different part of the country, it's been great thus far with no issues what so ever.  Both locations are setup with a VPN connection between them and we've had good performance.  Recently, I've been having an issue with an update query that I run in Access 2010.  I have a table of about 30,000 rows with 5 columns where I need three of the columns to update corresponding columns in another table located on the remote iSeries.  When this query was done locally with the iSeries done next to me, I had 0 issues.  Today, it takes over 1.5 hours to complete the request.  I know that I don't have a data definition issue and I know the connection is still good because I can view and update individual records when the table is linked back to the iSeries.  I was wondering if anyone has encountered this or can offer any suggestions as to how I can see what is taking the query so long?  

I've further completed a test where I used the File Transfer program from IBM to upload the physically created file from Access to a skeleton file sitting on the remote iSeries and that transfer takes a whole 10 seconds.  Before I go further and develop this work around where I complete the update directly on the iSeries, I wanted to exhaust all issues I could check for on my server side.  

Any help would be great appreciated.

Thanks.

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

Gary PattersonVP Technology / Senior Consultant Commented:
Please post the problem query.

You may also find it useful and informative to use the Performance Analyzer tool in Access and the Visual Explain tool in IBM i Navigator to see how the query is being resolved.

Odds are something in the query design is causing a very large intermediate result set or entire remote table(s) to be downloaded to Access over what is now probably a much slower connection.  If this is what is happening, it is because when the query was being run over a fast LAN connection, it wasn't as big a deal - the download happened fast enough that query runtime was tolerable.  Now that the connection spans a VPN to a remote location, the big transfer is now causing the query to take a long time.

Generally you resolve this issue by converting the query to a pass-through query that can be executed entirely on the remote system, or simply by restructuring the query.    In some cases (for example, when you need to join a local and a remote table), you'll want to upload the local (Access) table to a temporary table on the IBM i so that you can do the join there.

http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx
0

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:
Hi Gary, sorry I'm just getting back to you now.  I will review the Performance Analyzer tool and the Visual Explain tool in the iNavigator (didn't think about them).  

I'll also review the link you provided; however, here is the code for the query...

UPDATE ANTHONY_ELIGAM08
INNER JOIN ANTHONY_ELIGAM08 ON (ANTHONY_ELIGAM08_SKELETON.[ELDSS#] = ANTHONY_ELIGAM08.[ELDSS#]) AND (ANTHONY_ELIGAM08_SKELETON.[ELSS#] = ANTHONY_ELIGAM08.[ELSS#]) 
SET ANTHONY_ELIGAM08.ELVIS = [ANTHONY_ELIGAM08_SKELETON]![ELVIS], 
ANTHONY_ELIGAM08.ELVSST = [ANTHONY_ELIGAM08_SKELETON]![ELVSST], 
ANTHONY_ELIGAM08.ELVSTS = [ANTHONY_ELIGAM08_SKELETON]![ELVSTS];

Open in new window

0
Gary PattersonVP Technology / Senior Consultant Commented:
How big is the remote table ANTHONY_ELIGAM08?  

Assuming that ANTHONY_ELIGAM08_SKELETON is a local Access table, then the probably cause is what I've identified earlier.  Joining between a local and a remote table means that the remote table has to be pulled down to the local system to perform the join.  Then depending on the size of the remote and local tables, you may be sending a large volume of single-row updates over the wire to the remote system.

Based on what you're doing, you might be much better off uploading a copy of ANTHONY_ELIGAM08_SKELETON to the IBM i, and then running the update query locally on the IBM i, outside of Access.  Hard to say for sure without knowing the data volumes involved.

How many rows are there in each table, and how many matching rows in the inner join?

select count(*) from anthony_eligam08
select count(*) from anthony_eligam08_skeleton
select count(*) from ANTHONY_ELIGAM08 INNER JOIN ANTHONY_ELIGAM08 ON (ANTHONY_ELIGAM08_SKELETON.[ELDSS#] = ANTHONY_ELIGAM08.[ELDSS#]) AND (ANTHONY_ELIGAM08_SKELETON.[ELSS#] = ANTHONY_ELIGAM08.[ELSS#])

- Gary
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony6890Author Commented:
The remote table has about 50,000 records, with about 30 different columns.  

Yes, ANTHONY_ELIGAM08_SKELETON is a local Access table, it has about 20,000 records and only 5 columns.  

Your suggestion about completing the update just on the iSeries side is what I was going to try to do; however, I wasn't sure if there was something else I could do first.  

Do you think the Pass Through query would do any better?
0
Gary PattersonVP Technology / Senior Consultant Commented:
That's a pretty small record count.  Maybe it is pulling the entire file down - is it a large record size?  How fast is your VPN connection?

Yes, odds are good that a pass-through query will be much faster (but you'll need to upload the two required join columns out of the local table first in order to do that).
0
Anthony6890Author Commented:
The VPN connection is 100Mbp.  Record size isn't really that large.  

Let me look at the other options and Ill post back with the results.  

Thanks.
0
Anthony6890Author Commented:
Hi Gary,

So I used the Performance Analyzer along with the tool in the iSeries Navigator and I cannot come up with anything crazy going on.  Do you know of a way in Access, that I can launch the automatic upload of a text file to a physical file on the iSeries?  Executing a Data Transfer to the iSeries.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.