I'm guessing it would be faster to set up your left joins of the names and categories as mapping tables instead, and just map the IDs to names as you read them in. For instance:
Payer:
MAPPING LOAD * INLINE [
Payer ID, Payer Name
1, Aetna
2, United Healthcare
3, Cigna Healthcare
];
MAP [Payer Name] using Payer;
LOAD ... [Payer ID] as [Payer Name] ...
I doubt that's where the time is being spent, though. Where does the log say the time is being spent?
I'm guessing most of it is in the crosstable loads. They look very inefficient to me. Like on the first one, for instance, it looks like you'd be duplicating 30+ fields eight times each. All that duplication and extra data is what I'm guessing is taking the time. I think you'd want to establish some sort of row ID FIRST. Keep fields that are associated with that original row ID in one table. Then do the crosstable load into another table, with ONLY the ID and the fields you're crosstabling. Denormalization is often good, but I'm thinking probably not in this case.
Not sure I'm really understanding it fully, though.
