How to speed up the delete operation from a table that has millions of records?

Question

 BEGIN TRANSACTION
   BEGIN TRY 
      ;WITH CTE_TQUOTEWO
          AS
          (
            SELECT WORKID,QUOTE_NO
            FROM ABC_TQUOTEWO WITH(INDEX(PK_TQUOTEWO_ID))
            WHERE TQUOTEWO_ID != '' 
          )
        DELETE CA
        FROM CTE_TQUOTEWO CA  
        JOIN I_ABC_TQUOTEWO AT WITH(INDEX(PK_ITQUOTEWO_ID))
        ON AT.WORKID = CA.WORKID
        AND AT.QUOTE_NO = CA.QUOTE_NO
        AND AT.TQUOTEWO_ID != '';
     COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
    ROLLBACK TRANSACTION
   END CATCH

I have a table with millions of records and no indexes defined on it, still it takes much time for execution. Can anyone suggest to do the delete operation much faster? using sql server...

Are you deleting all the rows in the table? Can you show the delete command you are using? — Gordon Linoff, Aug 05 '13 at 13:55
refer http://stackoverflow.com/questions/2126434/bulk-delete-on-sql-server-2008-is-there-anything-like-bulk-copy-bcp-for-delet — aads, Aug 05 '13 at 13:57

score 2 · Answer 1 · answered Aug 05 '13 at 13:54

2

If you need to delete all rows.

USE EXTRA CARE....And i mean EXTRA because i have used it on a table that i dind't want to delete ALL rows. Thankfully it was on a test environment.

SQL TRUNCATE

answered Aug 05 '13 at 13:54

Giannis Paraskevopoulos

18,261
1
49
69

score 0 · Answer 2 · answered Aug 05 '13 at 13:55

0

You can use Truncate if you do not need to logging.

answered Aug 05 '13 at 13:55

gzaxx

17,312
2
36
54

score 0 · Answer 3 · answered Aug 05 '13 at 13:55

0

TRUNCATE TABLE TableName;

Truncate should be faster than

DELETE FROM TableName;

answered Aug 05 '13 at 13:55

Louis Ricci

20,804
5
48
62

But truncate requires DROP privilege, and DELETE does not. In general, you're correct, TRUNCATE is faster, of cause (since it simply drops a table and then creates it again). – Alma Do Aug 05 '13 at 14:00

How to speed up the delete operation from a table that has millions of records?

3 Answers3