I am new to this forum, so I hope I am posting this in the right place.
We are running a purchased financial program on our as400. We use FileMaker to keep track of all other information pertaining to our business. Every night, a script runs that queries new data added to the as400 and imports it into FileMaker. We feel like we are outgrowing our FileMaker solution and would like to avoid having duplicate data.
The choice is either to build an MS SQL backend database that references data on the iSeries or to create a new library on the as400 and relate the data to the existing tables in another library.
I did a test using ASP and I found that when I queried the data using an INNER JOIN on 2 libraries on the as400 it was slightly slower than when I retrieved the data from SQL and the as400.
Do you know why this would be the case? I'd think it would be quicker to query data that is on the same server.
Also, what would you recommend? Would it be better to build the back-end on the as400 or MS sql? I'm thinking that the front end will be a web interface built in ASP.