Performance issue with Delete command

EP.sqlplan

I am herewith attaching execution plan of query. The original name of the table is Contactbase not ABCD.

The problem is in the number of indexes/foreign keys which must be checked/updated.
If this is just one time operation then you could think about the foreign key constraints removal, deleting all related rows in all tables, and constraints recreation afterwards.
If you plan to delete rows more often then you may apply "Active" row indicator. Simply add a new bit column named Active and set its value to 0 instead of deleting the row. Of course, you have to check this column value in all queries when working with the table Contactbase.

Update: The query plan also contains many Clustered Index scans - this is operation which must go through the whole table to find all records having ContactID equal to the deleted row. If these tables are large then it takes a long time. So the first optimization should be to create index on ContactID column in all tables larger than let say 5000 rows. You may start from the biggest one.

It looks that's not a table but a View, meaning that you'll need to tune first the View.
Can you provide the View schema?

The fact it could be a view is not so relevant here. What would help more than estimated plan would be the actual query plan but first of all check the number of rows in dependent tables. Namely those which are searched by Clustered Index scan.

The fact it could be a view is not so relevant here

Yes it matters. You can improve the View if the related tables have missing indexes or if the View schema can be tuned by using a better SELECT command.

@Vitor: Could you please post any example of "better SELECT command" which will improve the DELETE command speed? We are talking about one row deletion based on table PK.  

And even when we would be talking about the view then DELETE command does not allow any related tables in the view.

@Praveen: I am still waiting for the actual query plan. But if you analyze number of rows in tables where Clustered Index scan is performed then you could help yourself.

@pcelba: Sure. Things like DISTINCT and usage of functions in the WHERE clause. Just to name few.

We are talking about one row deletion based on table PK.

Which PK? How do you know that View has a PK and even if is really the one used in the DELETE command?

And even when we would be talking about the view then DELETE command does not allow any related tables in the view.

Huh? Now, I'm confused with your statement. Can you please rephrase?

1) The DISTINCT in a view definition does not allow rows deletion from such a view.
2) Function in the view WHERE clause would be visible in the query plan.
3) PK .... well ... I would suppose if somebody names the index cndx_PrimaryKey_Contact  then we are talking about the PK. If not then the main intention of this question would be to confuse us. The PK usage is visible from the query plan.
4) Related tables in a view = JOIN in the view definition. DELETE is not allowed in such case.

So no "better SELECT command" can exist, PK is clearly stated, and we are still waiting for the Actual query plan... or number of rows in scanned tables.

Hi Praveen MSDBA,

do you have any news for us? Did you resolve the problem (by appropriate index(es) creation)?

TIA
Pavel