时间戳

首页 > 解决方案 > 删除大量行后T-SQL查询变得很慢

sql - 删除大量行后T-SQL查询变得很慢

问题描述

如果我从表中插入然后删除大量行，则在表上运行的查询会变得非常慢。该表没有索引，但创建一个可以解决问题。但是我不明白的是为什么性能首先会下降。我还尝试在删除后更新表上的统计信息，但这并没有提高性能。有什么想法吗？

这是设置：

use tempdb;
go

drop table if exists dbo.MAvg;
CREATE TABLE dbo.MAvg
(id  INT   NOT NULL IDENTITY(1,1) ,
 val FLOAT NULL);
GO
INSERT INTO dbo.MAvg(val) VALUES
(1), (2), (3), (4), (1), (2), (3), (4), (1), (2);
GO

--A sample query which calculates moving average using a loop. I can do it using recursive CTE and cursor as well both of which will also show performance degradation
--It takes a few milliseconds to execute the query
declare @EMA_results as table(id int, val float, EMA float)
insert into @EMA_results
select top (1) id, val, val as EMA
from MAvg 
order by id asc

while @@ROWCOUNT>0
begin
insert into @EMA_results(id, val, EMA)
    select nxt.id, nxt.val, round((.7*nxt.val + .3*prev.EMA),2) as EMA 
    from MAvg as nxt inner join @EMA_results as prev
    on prev.id + 1 = nxt.id and prev.id = (select max(id) from @EMA_results)
end

select * 
from @EMA_results

--Now insert 1000000 rows. 
delete from MAvg;
DBCC CHECKIDENT ('MAvg', RESEED, 0);
;with cte(n) as
(
select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)
insert into MAvg(val)
    select ABS(CHECKSUM(NEWID())%6)+1
        from cte as num1
        cross join cte as num2
        cross join cte as num3
        cross join cte as num4
        cross join cte as num5
        cross join cte as num6;

--now delete these rows and insert the 10 original rows
delete from MAvg;
DBCC CHECKIDENT ('MAvg', RESEED, 0);
INSERT INTO dbo.MAvg(val) VALUES
(1), (2), (3), (4), (1), (2), (3), (4), (1), (2);

--After deleting large number of rows, the same query takes 13 seconds to execute
declare @EMA_results as table(id int, val float, EMA float)
insert into @EMA_results
select top (1) id, val, val as EMA
from MAvg 
order by id asc

while @@ROWCOUNT>0
begin
insert into @EMA_results(id, val, EMA)
    select nxt.id, nxt.val, round((.7*nxt.val + .3*prev.EMA),2) as EMA 
    from MAvg as nxt inner join @EMA_results as prev
    on prev.id + 1 = nxt.id and prev.id = (select max(id) from @EMA_results)
end

select * 
from @EMA_results

标签： sqlsql-servertsqlindexing

解决方案

不同之处在于对 执行的逻辑读取MAvg。

最初，表的 IO 统计信息是

Scan Count  Logical Reads   
        31  86

sp_spaceused shows rows 10, reserved 72KB

select * from MAvg显示逻辑读取 = 1

删除行并重新插入后，统计显示

Scan Count  Logical Reads   
        31  145,518

sp_spaceused shows rows 10, reserved 192KB

select * from MAvg现在显示逻辑读取 = 16

这可能是因为仅仅删除行并不能解除表现在pages占用的分配，并且表是heap.

如果您truncate不删除该问题则不存在。

如果创建唯一聚集索引

create unique clustered index Idx_Id on mavg(id) with(fillfactor=100)

该问题也不存在，因为重新插入的行都存在于单个页面上。

推荐阅读