i work on sql server 2019 i have two tables table books and booksdetails
i need to delete all data from table books details that have book id related on table books
so i make it as below
books table have 20milions book id
bookdetails have 19milion book id related
after delete bookdetails will have one milion
delete d from dbo.bookpublishdetails d inner join dbo.books b on b.bookid=d.bookid
so result of applying delete will delete 18 milion from table bookdetails
so it will take too much time reach too 1 hour although i have clustered indexes on bookid on table dbo.books
and nonclusterd index on table dbo.bookpublishdetails on colunmn book id
after delete i will use table dbo.bookpublishdetails to more processes as select and delete and insert on table
bookdetails
so are there are another logic best from delete to avoid too much time consumed on delete ?
ddl details
create table dbo.book ( bookid int, bookname varchar(200), ISBN varchar(20), PageNumbers int, PublishedBy varchar(200) ) create clustered index book_ix on dbo.book(bookid) create table dbo.bookpublishdetails ( bookdetailsid int, bookid int, BookPublishedId int, PublishedBy varchar(300) ) create nonclustered index bookdetails_ix on dbo.bookpublishdetails(bookid)