ahmed salah

ahmed salah

  • 1.1k
  • 507
  • 31.4k

how to remove 18 milion bookid from table without using delete ?are th

Aug 24 2022 6:27 PM

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)

 


Answers (2)