I am testing columnstore table with 1 crore rows having the following structure,
create table tags ( id INT not null, sub_id int, key_name varchar(150) not null, key_value varchar(501), appid varchar(50), KEY (`appid`,`key_name`) USING CLUSTERED COLUMNSTORE , SHARD KEY (`id`) );
and I need to delete rows from tags table , I tried deleting with 1000 OR conditions to delete 1000 rows using the following query
DELETE FROM tags where ( appid=XXXX and key_name=XXXX ) OR ( appid=YYYY and key_name=YYYY) OR ....
and it took 102 sec to complete.
I tried deleting individual rows, for 1000 individual deletes it took 7.7 sec.
My questions are :
- Why it is taking a long time to delete while its fetching data with lightning speed?
- Is there any other way to delete rows more efficiently?
Thanks in advance.