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?
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?
PatHartman
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks again.
-Anthony