0

Insert and update queries are taking long time as compared to select queries. Here are some stats for the table -

Table is storing monthly data. For current month it is MTD. The data for current month is getting deleted and loaded every day.

Daily insert row count - 2.5 millions, Daily delete time taken - less than one minute, Table has 13 months of data, Time taken to insert 2.5 millions rows - 2 hrs 20 minutes, Time take to select records for a month - less than a minute

The tool used for insertion of data is Informatica Power center. The Informatica is using query push down to DB approach.

Please suggest on how I can reduce time of insertion for data in table at database level.

v83rahul
  • 283
  • 2
  • 7
  • 20

2 Answers2

0

Sounds like informatica is doing row by row processing. Ideally a direct path parallel loading approach should be used. 2.5 million rows should load in seconds.

BobC
  • 4,208
  • 1
  • 12
  • 15
0

usual strategy for speeding up inserts is to drop indexes as presql, use bulk load strategy and then recreate indexes after load completes

Daniel Machet
  • 615
  • 1
  • 5
  • 7