Wants to know which one would be faster BTEQ Vs ML from loading and Retrieving perspective.
Presently in my organisation we are using FE-ML to load the data into final target table from work table.Definition of both target and Work table are similar.Data volumn would not be less then 100000 and moving forward it will grow.All tables have PI and Set/Multiset(Depends).
One of the advantage which I think of using bteq is it will be on one server where as in case of FE-ML first we need to FE to diff platform n then load using ML. Whereas from ML perspective there is no rollback n it will capture duplicate record.
Would appreciate any help and whether it would be feasible to move to BTEQ.
You need to be more specific about the kinds of logical operations you require. Evidently they are not simple inserts, or you could just insert directly from your work table. If the operations include updates and/or upserts, then Teradata 12.0 (now in beta) provides another possible solution, with the MERGE statement.
In general, any SQL solution that avoids exporting and re-importing the source data will probably be faster than Multiload. The new MERGE statement, in particular, beats Multiload performance-wise.