how to query from many AS400 tables to add to one SQL table?

[Joss]

AS400 data that is spread across 7 tables needs to be collated to populate
one table in an SQL database. Fresh data is to taken from the AS400 several
times each day and added to the SQL table. Some long text fields from the
AS400 have to be processed to create several smaller SQL fields, whereas
some AS400 fields are merged to make one SQL text field. These processes,
plus having to join so many tables together means that the updates take
ages. I am looking for suggestions as to what would be the best strategy to
increase performance. I first tried using a .MDB with linked tables to the
AS400 and SQL server, and various combinations of queries, but it is SOOOO
SLOOOOW!

One culprit seems to be that the append query that adds new records to the
SQL table uses an inner join and tests for null values of the SQL table
primary keys to determine which records from the AS400 have to be added. I
prefer this method because it seems a bit untidy to create a temporary table
of all AS400 records' primary leys then subtract the SQL keys to find which
ones need to be added. May be this is the best way?

The other big problem seems to be the large number of AS400 table being
linked, but I need fields from all of them. I have created indexes on all
of them and these have helped.

Finally, I can't decide whether it is better to perform the text operations
in a module or by using built in functions in the query design grid . (Left,
Right, Mid etc)

I tried adding the AS400 to an Access project as a linked server, and
creating views the table, but this failed when I could not work out how to
create indexes (pseudo indexes?) to them.

Am I better off using a .ADP to do the job for me or is a .MDB with linked
tables OK?

If anybody can give me a nudge, push or kick in the right direction, it
would be really appreciated. I have learnt a lot during the past week or so,
but as it gets more involved, the questions seem to get more complex and the
answers harder to find.

thanks for your time,
JO

 

[John Vinson]

One culprit seems to be that the append query that adds new records to the
SQL table uses an inner join and tests for null values of the SQL table
primary keys to determine which records from the AS400 have to be added.

Can't comment knowledgably on the rest but... to prevent the addition
of duplicate records, consider just throwing them at SQL/Server in the
append query. If the table has the appropriate unique indexes, the
database engine will discard them with a (trappable) warning message,
probably much faster than the cross-platform join.

John W. Vinson[MVP]

 

[Joss]

Now there's an idea......

thanks.
Jo

Can't comment knowledgably on the rest but... to prevent the addition
of duplicate records, consider just throwing them at SQL/Server in the
append query. If the table has the appropriate unique indexes, the
database engine will discard them with a (trappable) warning message,
probably much faster than the cross-platform join.

John W. Vinson[MVP]