Hi All,

Warm Greetings!!

Just wanted to check the best option to delete the records from a table given a unique id (Delete * from Table where id=‘123’). One simplest way is writing a delete adapter service, nut not sure its advisable for mass deletion. Ex: I have a list of Unique Id’s, iterating over each records to delete.

Any suggestions are highly appreciated.

Note: Unique Id’s maybe in 1000’s.

Regards,
Niteesh

Hi Niteesh,
Is it a one time activity to delete those 1000’s of records, or needs to be executed frequently on a daily basis? If it is few thousands of records that needs to be deleted during late ‘non-peak’ business hours or weekends, you can use custom sql instead of invoking delete adapter service ‘n’ times. However, if the data is huge in database, with constraints on the table, this delete would take more time and db might hung or respond very slow that time.

-Senthil

Hi Senthil,

Thanks for the suggestion. Yes, its a daily file(But need not be 1000’s everyday). I have developed a java service which creates a string for the purpose of deletion using ‘IN’ statement Dynamic SQL(Ex: delete from table where id IN(‘id1’,‘id2’)).

This should be faster AFAIK, but let me know any bottlenecks of using this.

Regards,
Niteesh

Hi Niteesh,
There are few restrictions like, the number of values in IN clause cannot cross more than 1000. Your java service based on the volume on that day, if it returns more than 1000, your processing will fail.

Instead of IN, you can use OR if it goes beyond 1000.

If your table has some date column, you can think of an alternative of using BETWEEN clause by passing range of dates. NOT IN is surely not advisable as it scans the entire table and has some known performance issue.

Probably, some database expert can advice on best way.

HTH
Senthil

Yes even I feel you should depend on the date column for an optimized approach dealing with huge or bulk data deletion procedures.

“If your table has some date column, you can think of an alternative of using BETWEEN clause by passing range of dates.”

Would this be an option works for you?

HTH,
RMG

Yes, I am aware of IN restriction, and used OR. And process might slow,but still better than individual deletion i suppose. Since we are deleting records daily the record might not grow. Max it will be in few thousands(Less than 10,000 usually). And no, there is none other fields like date or range(Only ID’s). Lets see how it goes…

Cheers,
Niteesh

OK as a daily routine wise it shouldn’t affect it.