I have been working on doing bulk updates and inserts through sqlapi++ using a bulk udpate extention library, developed, a long time ago, internally. The database is oracle. The bulk inserts/updates are done in batches of 50K. I am facing a very peculiar performance issue with regard to these bulk operations.
The initial batch of bulk insert (50K records) is finished within 3 seconds; while the next similar batch for bulk insert or update (50 records again) takes a whopping 373 seconds. Using 'top' on a RHEL-AS-4 server, I could see that it's oracle process that takes up the whole 373 secods for completion; so sqlapi++ or the internally developed extention is not the culprit.
The third batch of 50K records in the sequence take a lot more time (913 seconds). The time goes on increasing exponentially; and there doesnt seem to be >any< pattern out of it.
Surprisingly, this is not consistent. On a good day, I can have the seconds batch going through in 3 seconds. All records intact and perfect without any sort of defect in the data. In fact, all of the next batches would finish in or around 3 seconds.
Yet more surprisingly, if I truncate the table and start the process, the performance problem would reappear. It would again start taking 370-380 seconds for 2nd batch. Again, if I had used 'delete from' query instead of 'truncate table' query for deleteing all the records from the table, there wouldn't be any problem!
So in short, I came to conclusion that the performance bottleneck occurs when the table is truncated (or is created brand new), and not when all records are deleted using 'delete from' query.
Any guesses why it could be occuring? I confess I am not very good at databases, so any help would be very much appreciated.
Thanks in advance.
-
Shreyas
The initial batch of bulk insert (50K records) is finished within 3 seconds; while the next similar batch for bulk insert or update (50 records again) takes a whopping 373 seconds. Using 'top' on a RHEL-AS-4 server, I could see that it's oracle process that takes up the whole 373 secods for completion; so sqlapi++ or the internally developed extention is not the culprit.
The third batch of 50K records in the sequence take a lot more time (913 seconds). The time goes on increasing exponentially; and there doesnt seem to be >any< pattern out of it.
Surprisingly, this is not consistent. On a good day, I can have the seconds batch going through in 3 seconds. All records intact and perfect without any sort of defect in the data. In fact, all of the next batches would finish in or around 3 seconds.
Yet more surprisingly, if I truncate the table and start the process, the performance problem would reappear. It would again start taking 370-380 seconds for 2nd batch. Again, if I had used 'delete from' query instead of 'truncate table' query for deleteing all the records from the table, there wouldn't be any problem!
So in short, I came to conclusion that the performance bottleneck occurs when the table is truncated (or is created brand new), and not when all records are deleted using 'delete from' query.
Any guesses why it could be occuring? I confess I am not very good at databases, so any help would be very much appreciated.
Thanks in advance.
-
Shreyas
Comment