SQL Server删除性能问题

Question

我有一张大约有一百万行的桌子。我们的部分维护包括每天删除旧行，但这需要大约40分钟。

delete语句是：

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

我能做些什么来提高性能吗？

谢谢

根据要求：

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\u ID]ASC）在[PRIMARY]上具有（PAD\u INDEX=OFF，STATISTICS\u NORECOMPUTE=OFF，IGNORE\u DUP\u KEY=OFF，ALLOW\u ROW\u LOCKS=ON，ALLOW\u PAGE\u LOCKS=ON，FILLFACTOR=90）]

Answer 1

好的，正如我所怀疑的，您似乎没有在删除查询中引用的字段的索引。因此，添加类型、capid、cder\u中断和源的索引。

此外，您可能想要尝试AND capid IN（SELECTcder_idfromPUB_CAR. dbo. CapDer WHEREcder_discontinued不是NULL AND DATEDIFF（dd，cder_discontinued， GETDATE（））