2

Suppose my source system is a flat file and it is having more than 50M records.it is taking 2hrs time to run. How to reduce run time. What performance tuning techniques you can implement at source level

nithin
  • 39
  • 8
  • Please explain what you are actually doing with the flat file – NickW Jul 16 '21 at 03:00
  • Please attach session log or mapping details for better input from us. There can be many parameters which can cause slowness. If its a simple mapping with only expressions etc., shouldnt take much time. But if it has agg, lookup, rank, upd tr etc. it can be slow. – Koushik Roy Jul 16 '21 at 04:37
  • It's an interview question. Mapping has source,exp and target – nithin Jul 16 '21 at 13:26

1 Answers1

3

Let me try based on answer above. First of all check into session log to check where is the bottleneck. Near to the end of log you will get statistics for below three parameters.
Which part - source read, expression processing, target load is taking time.

  1. If source read taking time - since this is a flat file, we can not do many improvements. i. we can partition the source extraction so it reads faster. ii. Avoid adding quotes or escape characters in the format properties for a delimited flat file. iii. if its a simple text file, its fast but if its complex file like xml, vsam - it may take time depending on the structure of source. iv. preprocess the file and remove data thats not needed. iv. number of bytes the Data Integration Service reads per line. Configure the Line Sequential Buffer Length property.
  2. If expression is taking time - i. This will be tricky, but since mapping is simple then try putting less calculations etc. ii. increase DTM buffer size in memory. iii. remove property if session is set to use high precission.
  3. If target load is taking time - i. if target is flat file, amount of data can be a problem and using improvements in point #1 will be fine. ii. de-link unwanted columns. iii. if target is table, check if table has indexes, if yes, drop them and recreate after load. iv. if table is updated, check if key columns has index in database.
Shawn
  • 90
  • 6