Delete via 'IN' list vs 1 by 1

miked's profile image miked posted 11 years ago in Feature discussion Permalink
working in remote db over slow connection so the way that deletes currently happens jumps out at me.

Right now each record selected for delete is handled in its own query

So if I delete 20 rows there are 20 delete statements run. On a remote system this can take awhile. Each delete takes about a second to complete. Deleting 200 records is a long wait....

delete from <table> where id = 1;
delete from <table> where id = 2;
delete from <table> where id = 3;
...

Possible to build a list of the primary key values for the rows selected, and then add those to a list of id's passed to a single delete statement?

ie:

delete from <table> where id in (1,2,3,4,5);

This style of delete would really speed things up in this non-standard use case I have going on that I find myself in quite a bit lately
ansgar's profile image ansgar posted 11 years ago Permalink
I'm afraid that the complex logic to detect the primary key/unique key values for a WHERE clause prevents us from using the IN() syntax for automatic DELETEs.

Please login to leave a reply, or register at first.