Delete query is taking long time to delete 7449 rows

• rajendra-232973

  Grasshopper

  Points: 18

  More actions

  March 24, 2006 at 5:22 am

  #114036

  Hi All,

  This is Rajendra, one of my team members issued the following delete statement to delete 7449 rows. but it has been running since morning.

  Delete PE_ITM_T where CONVERT(CHAR(10), UPD_TS,120)= '2006-03-22' and UPD_ID = 'LOADXCLI'

  but while selecting it is taking 40 seconds to run....

  Please can any one help me in this.

  Thanks & Regards,

   

  Rajendra.

   

• Jo Pattyn

  SSC-Dedicated

  Points: 32352

  More actions

  March 24, 2006 at 10:41 am

  #628692

  Hello.

  *Applying a function on the restrictive column is really nasty for your performance as you force out a full table scan instead of an index scan.

  This also leads to additional blocking. (escalating locks)

  You can convert to: (assuming UPD_TS  is (small)datetime)

  SET NOCOUNT ON /*avoid roundtrip*/

  DELETE dbo.PE_ITM_T /*specify owner*/

  WHERE UPD_TS >= convert(datetime,'2006-03-22') /*excellent ISO date*/

  AND UPD_TS <dateadd(d,1,convert(datetime,'2006-03-22')) /*add a day*/

  AND UPD_ID = 'LOADXCLI'

  *Are there any delete triggers specified on the table?

  *all direct relational integrity fields are indexed?