?
Solved

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

Posted on 2014-08-25
7
Medium Priority
?
649 Views
Last Modified: 2014-08-28
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
0
Comment
Question by:Anthony6890
  • 4
  • 3
7 Comments
 
LVL 36

Accepted Solution

by:
Gary Patterson earned 2000 total points
ID: 40283240
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 40283697
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
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 40283731
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 1

Author Comment

by:Anthony6890
ID: 40283747
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
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 40283826
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 40283838
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 40285833
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question