Hi there,
We are in the middle of a migration from BO Data Services 3.1 to Bo Data Services 4.2, and are also changing our target database from SQL Server to SAP IQ (16.0).
In our old system we had several data flows where a number of rows from a table would be deleted from a target table by making a selection and then using a Map Operation where all incoming rows were set to Delete. For 5000 rows this would take approximately 5 seconds.
While migrating to the new BODS version and the IQ database we set each of our targets to Binary Bulk loading, and the delete data flows were set to Append. This seems to work, but loading these 5000 rows now takes very long, over 20 minutes (I broke off after that time).
I was able to reproduce this several times, so it seems consistent.
Running the delete seperately in Interactive SQL returns results in a couple of seconds, so the problem seems to bee in the bulkloading.
We have found a work around, but it has taken us a lot of time to implement this work around (using a temporary table and a post load command).
To illustrate our problem, I have added an ATL file with the way we are working. The first (and third) data flow inserts 500000 records in a table, the second and fourth select 10000 records to delete from the table. The fourth dataflow is how we used to work, i.e. using Map Operation and setting all rows to delete. The second data flow is what we do now (temp table and a post load command).
Executing this gives 2 seconds for the second dataflow, and 6 minutes for the fourth.
Does anyone know why this happens? Is there a better way around this?
Cheers,
Bert
Problem_with_delete.zip (5.0 KB)
BertB (BOB member since 2008-11-05)