CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
M_Idrees
M_Idrees

Reputation: 2182

MS SQL - Delete query taking too much time

I have the following query script:

declare @tblSectionsList table
(
    SectionID int,
    SectionCode varchar(255)
)
--assume @tblSectionsList has 50 sections- rows

DELETE 
    td
from 
    [dbo].[InventoryDocumentDetails] td
    inner join [dbo].InventoryDocuments th 
          on th.Id = td.InventoryDocumentDetail_InventoryDocument
    inner join @tblSectionsList ts 
          on ts.SectionID = th.InventoryDocument_Section

This script contains three tables, where @tblSectionsList is a temporary table, it may contains 50 records. Then I am using this table in the join condition with the InventoryDocuments table, then further joined to the InventoryDocumentDetails table. All joins are based on INT foreign-keys.

On the week-end I put this query on server and it is still running even after 2 days,4 hours... Can any body tell me if I am doing something wrong. Or is there any idea to improve its performance? Even I don't know how much more time it will take to give me the result.

Before this I also tried to create an index on the InventoryDocumentDetails table with following script:

CREATE NONCLUSTERED INDEX IX_InventoryDocumentDetails_InventoryDocument
    ON dbo.InventoryDocumentDetails (InventoryDocumentDetail_InventoryDocument);

But this script also take more than one day and did not finish so I cancelled this query.

Additional info:

Query Execution Plan (1): enter image description here

2nd Part: enter image description here

The following query give one row for this and showing status='suspended', and wait_type='LCK_M_IX'

SELECT r.session_id as spid, r.[status], r.command, t.[text], OBJECT_NAME(t.objectid, t.[dbid]) as object, r.logical_reads, r.blocking_session_id as blocked, r.wait_type, s.host_name, s.host_process_id, s.program_name, r.start_time
FROM sys.dm_exec_requests AS r LEFT OUTER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.session_id <> @@SPID AND r.session_id > 50

Upvotes: 0

Views: 2048

Answers (3)

Ross Bush
Ross Bush

Reputation: 15185

It sometimes can be more efficient time-wise to truncate a table and re-import the records you want to keep. A delete operation on a large tables is incredibly slow compared to an insert. Of course this is only an option if you can take your table offline. Also, only do this if your logging is set to simple.

  1. Drop triggers table A.
  2. Bulk copy table A to B.
  3. Truncate table A
  4. Enable Identity Insert.
  5. Insert Into A From B Where A.ID Not in ID's to delete.
  6. Disable Identity Insert.
  7. Rebuild indexes.
  8. Enable triggers

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

What happens when you change the Inner Join to EXISTS 

DELETE td
FROM   [dbo].[InventoryDocumentDetails] td
WHERE  EXISTS (SELECT 1
               FROM   [dbo].InventoryDocuments th
               WHERE  EXISTS (SELECT 1
                              FROM   @tblSectionsList ts
                              WHERE  ts.SectionID = th.InventoryDocument_Section)
                      AND th.Id = td.InventoryDocumentDetail_InventoryDocument)

Upvotes: 1

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Try like the below. It might give you some idea at least.

     DELETE FROM [DBO].[INVENTORYDOCUMENTDETAILS] WHERE INVENTORYDOCUMENTDETAILS_PK IN (
     (SELECT INVENTORYDOCUMENTDETAILS_PK FROM
     [DBO].[INVENTORYDOCUMENTDETAILS] TD
     INNER JOIN [DBO].INVENTORYDOCUMENTS TH ON TH.ID = TD.INVENTORYDOCUMENTDETAIL_INVENTORYDOCUMENT
     INNER JOIN @TBLSECTIONSLIST TS ON TS.SECTIONID = TH.INVENTORYDOCUMENT_SECTION
      )

Upvotes: 0

Related Questions