这可能是阻塞的问题。按照现有的写法,该查询将始终需要完整扫描。在 SSMS 中执行只需 2 秒的事实表明它并不是一个昂贵的查询。您是否可能在另一个会话中对该表进行了未提交的更新?
您可以尝试重构它以可能使用索引,或者调查 SQL Server 上的阻塞情况。
例如:
USE TEMPDB
GO
drop table if exists [TABLE]
go
create table [TABLE]
(
id int primary key,
data char(200) not null default replicate('z',200),
INSERT_DATE datetime,
LAST_MODIFIED_DATE datetime
)
create index ix_T_id on [TABLE](INSERT_DATE)
create index ix_T_lm on [TABLE](LAST_MODIFIED_DATE)
go
with q as
(
select top 1000000 row_number() over (order by (select null)) i
from sys.messages m, sys.messages m2
)
insert into [TABLE] (id,insert_date,LAST_MODIFIED_DATE)
select i, dateadd(minute,-1*i,getdate()),dateadd(minute,-1*i,getdate())
from q
go
set statistics time on
go
SELECT *
FROM [TABLE]
WHERE [INSERT_DATE] >= CONVERT(DATE, GETDATE() - 1) AND [INSERT_DATE] < CONVERT(DATE, GETDATE())
OR [LAST_MODIFIED_DATE] >= CONVERT(DATE, GETDATE() - 1) AND [LAST_MODIFIED_DATE] < CONVERT(DATE, GETDATE())
SELECT *
FROM [TABLE]
WHERE (SELECT CONVERT(DATE, MAX(v))
FROM (VALUES ([INSERT_DATE]), ([LAST_MODIFIED_DATE])) AS value(v)) = CONVERT(DATE, GETDATE() - 1);
go
set statistics time off
go