删除表中选定的行(如何提高速度)

back-story我有一个存储缓存时间的表,目前有大约100万行。当我用缓存项的新版本更新表时,我需要删除旧的缓存项(大约3K项)。这些项目立即被禁止并不重要,但我更喜欢它,因为当客户机取出缓存的项目时,我希望他们得到最新的版本。

但是删除过程仍然“到”缓慢,需要几秒钟的时间让最终用户等待,有没有办法让这一过程更快?ATM IM执行简单的SQL

DELETE FROM cache where cache_event_id = X

我的问题是: 我是否可以加快查询速度(我预计缓存表的大小只会增加,所以这个问题会变得更严重)? 我应该让删除SQL运行它自己的线程,并活在这样一个事实上,用户可以旧项目一段时间?

pr请求表的其余信息。

CREATE TABLE [dbo].[cache](
    [cache_id] [int] IDENTITY(1,1) NOT NULL,
    [cache_name] [nchar](128) NOT NULL,
    [cache_event_id] [int] NOT NULL,
    [cache_encounter_id] [int] NOT NULL,
    [cache_type_id] [tinyint] NOT NULL,
    [cache_creation_date] [datetime] NOT NULL,
    [cache_data] [varbinary](max) NOT NULL
) ON [PRIMARY]

所有索引都是由SQL Server事件探查器创建的,似乎我需要手动删除旧索引。 索引1:

CREATE NONCLUSTERED INDEX [_dta_index_cache_6_366624349__K2_K3_K5_K4_7] ON [dbo].    [cache] 
(
    [cache_name] ASC,
    [cache_event_id] ASC,
    [cache_type_id] ASC,
    [cache_encounter_id] ASC
)
INCLUDE ( [cache_data]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

索引2://可能实际未使用

CREATE NONCLUSTERED INDEX [_dta_index_cache_6_366624349__K5_1_2_3_4_6_7] ON [dbo].[cache] 
(
    [cache_type_id] ASC
)
INCLUDE ( [cache_id],
[cache_name],
[cache_event_id],
[cache_encounter_id],
[cache_creation_date],
[cache_data]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

索引3(我假设这个用于删除)

CREATE NONCLUSTERED INDEX [_dta_index_cache_6_366624349__K3] ON [dbo].[cache] 
(
    [cache_event_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

数据通过BulkCopy类插入到表中

取出数据(这是最关键的部分)

SqlCommand cmd = new SqlCommand("GetPageCache", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@event_id", EventID); // int 
cmd.Parameters.AddWithValue("@encounter_id", EncounterID); // int 
cmd.Parameters.AddWithValue("@type_id", (int)CacheType); //int 
cmd.Parameters.AddWithValue("@cachename", CacheName); // Required in some cases, but 90% this is just a fallback