CODEWITHSUNDEEP
sqlsql-serversql-delete
praveen
praveen

Reputation: 111

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

 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...

Upvotes: 1

Views: 192

Answers (3)

Louis Ricci
Louis Ricci

Reputation: 21106

TRUNCATE TABLE TableName;

Truncate should be faster than 

DELETE FROM TableName;

Upvotes: 0

gzaxx
gzaxx

Reputation: 17600

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

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

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

Upvotes: 2

Related Questions