DELETE TOP(500) is taking more time

• Jampandu

  Ten Centuries

  Points: 1062

  More actions

  March 13, 2014 at 10:37 am

  #290913

  Hi experts,

  below simple delete statement is taking around 50 sec to execute.

  DELETE TOP(500) a

  FROM Table1 a

  WHERE exists (SELECT 1 FROM #table2 b WHERE a.pkcol=b.pkcol)

  Here

  There is no indexes on Table1 except primary key.

  table1 is highly transnational table

  #table2 is temp table with 5000 records

  Any suggestions please

  Thanks!

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  March 13, 2014 at 10:52 am

  #1696880

  I'd suggest trying some indexes on the table and using a JOIN to do the UPDATE.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  March 13, 2014 at 11:59 am

  #1696914

  1) Did you look at the query plan?

  2) Do you have any Foreign Keys in play?

  3) Do you have any triggers on the table you are deleting from?

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service