SQL Server DELETE performance issues

Question

I have a table with approximately 1 million rows. Part of our maintenance involves deleting old row each day, but this is taking about 40 minutes.

The delete statement is:

DELETE 
  FROM [dbGlobalPricingMatrix].[dbo].[tblPricing]
  WHERE type = 'car'
  AND capid NOT IN
  (SELECT cder_id  FROM PUB_CAR.dbo.CapDer WHERE cder_discontinued 
IS NULL OR DATEDIFF(dd,cder_discontinued,GETDATE()) <= 7)
  AND source = @source

Is there anything I can do to improve the performance?

Thanks

As Requested:

CREATE TABLE [dbo].[tblPricing](
[id] [int] IDENTITY(1,1) NOT NULL,
[type] [varchar](50) NULL,
[capid] [int] NULL,
[source] [varchar](50) NULL,
[product] [varchar](50) NULL,
[term] [int] NULL,
[milespa] [int] NULL,
[maintained] [bit] NULL,
[price] [money] NULL,
[created] [datetime] NULL,
[updated] [datetime] NULL,
[notes] [varchar](1000) NULL,
[painttype] [char](1) NULL,
[activeflag] [bit] NULL,
[DealerId] [int] NULL,
[FunderId] [int] NULL,
[IsSpecial] [bit] NULL,
[username] [varchar](50) NULL,
[expiry] [datetime] NULL,
 CONSTRAINT [PK_tblPricing] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =   OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CAPDer](
[cder_ID] [int] NOT NULL,
[cder_capcode] [char](20) NULL,
[cder_mancode] [int] NULL,
[cder_rancode] [int] NULL,
[cder_modcode] [int] NULL,
[cder_trimcode] [int] NULL,
[cder_name] [varchar](50) NULL,
[cder_introduced] [datetime] NULL,
[cder_discontinued] [datetime] NULL,
[cder_orderno] [int] NULL,
[cder_vehiclesector] [tinyint] NULL,
[cder_doors] [tinyint] NULL,
[cder_drivetrain] [char](1) NULL,
[cder_fueldelivery] [char](1) NULL,
[cder_transmission] [char](1) NULL,
[cder_fueltype] [char](1) NULL,
 CONSTRAINT [PK_CapDer] PRIMARY KEY CLUSTERED

( [cder_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY]

How many rows affected by this query? Try to delete in portions. Look at recovery model. — Hamlet Hakobyan
It is not possible to provide a specific answer to this question without much more information. — Rhys Jones
When you run a delete statement, engine checks all the tables that have a foreign key to this table, and that can take some time. In other words, the number of foreign key relationships to this table, can have impact on the performance. If this is the case, then you may want to use soft delete. — Sparrow

Mike Nakis Mike Nakis · Accepted Answer · 2015-02-05T11:39:22

Okay, as I suspected, you do not seem to have indexes for the fields that you reference in your DELETE query. So, add indexes for type, capid, cder_discontinued, and source.

Additionally, you might want to try AND capid IN (SELECT cder_id FROM PUB_CAR.dbo.CapDer WHERE cder_discontinued IS NOT NULL AND DATEDIFF(dd,cder_discontinued,GETDATE()) > 7). The optimizer of MS-SQL-Server should actually be doing this for you, but you never know, it is worth trying.

SQL Server DELETE performance issues

1 Answers