Page 1 of 1
Improving performance of deletes
Posted: Wed Feb 19, 2014 6:49 am
by akarsh
Hi All,
I am facing the same issue
as seen here.
I have 3.7L record in file which i need to delete from table which contains 90M.I Am using below query
DELETE
FROM
Table
WHERE
Field1 = ORCHESTRATE.Field1
Design is simple
DS -> Tfm -> Oracle connector
in designer till connector data is moving as 877962 record/sec but from oracle its moving with the rate of 450 record/sec.
i have kept transaction size as well as array size both as 40000. and also did hash partition on field1 which is key field.
Rest settings are default.
Please help how to improve the performance.
Posted: Wed Feb 19, 2014 8:08 am
by chulett
I needed to clean up the quintuple posting you managed somehow ( I think that's a new record 
) and then moved you off to your own post. Similar != same, so best to post your own details and link back to anything older you think may be relevant. As I've done here.
First question is easy - do you have an index over "Field1"? Without it you are doing a full table scan for each delete.
Oh... and please don't ask the same question in multiple threads.
Posted: Wed Feb 19, 2014 10:23 am
by chulett
No need to ask your question again, it's right here... safe and sound.
Posted: Wed Feb 19, 2014 12:42 pm
by asorrell
That's a really massive array / transaction size. Have you tried with a smaller array / transaction size?
Posted: Wed Feb 19, 2014 11:21 pm
by PaulVL
I wonder if you insert your Field1 into an empty temp.table, you could then execute an after sql statement of "delete from table where filed1 in temp.table"
might be overkill.
But that field one column better sure be a index/key for speed.
I am not a database savvy guy.
reorgs depending on the database type are a good idea.
pre-sorting the field1 would be handy in most cases.
Improving performance of deletes
Posted: Fri Feb 21, 2014 5:12 am
by akarsh
Thanks chulett for deleting multiple post. :D
Even was not able to find how come multiple post i made
Well yes table is indexed on field one, and am trying to run using DRS connector in place of oracle connector. will update you if any thing positive as so far facing space issue in DB.
Re: Improving performance of deletes
Posted: Sun Mar 09, 2014 1:18 pm
by akarsh
Hi All,
I am back with the same issue
.
Now job is giving deadlock issue. Let me give details
Ds -> Tfm -> DRS connector
Ds has single field, on which data is hash partitioned and index is present in table on same field(this field is part of composite key, so composite index is present). In DRS connector have kept commit interval is 80000 as data is huge.
DS has non duplicate records. and has huge data. table also has app 100M records on which delete opearation needs to be done.
Please suggest what needs to be done so that job completes with out giving performance issue.
Posted: Sun Mar 09, 2014 6:02 pm
by chulett
Deadlocks are not a 'performance issue'. And I'd wager if you took the error you're getting here and searched for it, you'd get the answer you're looking for. Failing that, post the error.
Posted: Mon Mar 10, 2014 2:58 am
by akarsh
Hi Chulett,
We updated the job to remove deadlock issue by running DRS connector in seq mode and it took close to 12 hr to finish, which is not expected.
Please suggest if any change we can do now?
Posted: Mon Mar 10, 2014 8:17 am
by chulett
Search. Post the error.