`

【SQL Server性能优化】删除大量数据的方法比较

阅读更多
如果你要删除表中的大量数据,这个大量一般是指删除大于10%的记录,那么如何删除,效率才会比较高呢? 而如何删除才会对系统的影响相对较小呢?



下面先做一个实验,然后对这个实验的结果进行分析,然后得出结论。



1、创建数据库

use master
go
 
if exists(select * from sys.databases where name = 'test')
   drop database test
go
 
create database test
go


2、创建表

use test
go
 
 
if exists(select * from sys.tables where name = 't')
   drop table t
go
 
create table t(i int,v varchar(100) default replicate('a',100)
                    ,vv varchar(100) default replicate('a',100),
                    vvv varchar(100) default replicate('a',100));


3、插入数据
用下面的代码添加100000条记录,消耗9秒:

declare @i int;
 
set @i = 1
 
begin tran
 
	while @i <= 100000
	begin
		insert into t(i) values(@i)
	   
		set @i = @i + 1   
	end
 
commit tran


而如果用下面的代码,添加100000条记录,消耗43秒:
declare @i int;
 
set @i = 1
 
while @i <= 100000
begin
    begin tran
		insert into t(i) values(@i)  --没执行一次就提交一次,效率较差  
    commit tran
    
	set @i = @i + 1   
end


重复插入数据,消耗1分38秒
insert into t
select *
from t
go 6


最后总共插入了640万条数据。


4、建立索引

create index idx_t_idx1 on t(i)


5、进行如下设置,是为了预防SQL Server使用太多内存,而导致死机
sp_configure 'show advanced option',1
go
reconfigure
go
sp_configure 'max server memory (MB)',3584
go
reconfigure
go

6、把上面创建的表t数据,复制成t1和t2两个表,对t1表建立索引
if exists(select * from sys.tables where name = 't1')
	drop table t1
go
 
select * into t1
from t
 
create index idx_t1_idx1 on t1(i)
go
 
 
if exists(select * from sys.tables where name = 't2')
	drop table t2
go
 
select * into t2
from t

7、对t1表进行删除操作,一次删除1000个数,每个数有64条,所以每次删除64000条。共删除1000次,所以删除640000条记录,总耗时82秒
dbcc dropcleanbuffers
go
 
declare @i int = 20000;
declare @start_time datetime;-- = getdate();
 
while @i <30000
begin
 
	set @start_time = GETDATE();
 
	delete from t1	where I>=@i  and i<=@i + 999
 
	set @i += 1000
	select DATEDIFF(second,@start_time,getdate())
 
end

8、删除t2表的数据,耗时44秒
delete from t2
where I>= 20000 and i<30000



通过上面的测试发现:

 

1、在大量插入操作时,在完成操作后再提交,比每次插入操作后马上就提交,效率要高。

 

2、在删除大量数据时,就算运用索引,甚至同时运用索引和分批操作,效率也不如不用索引,直接通过表扫描删除来的高。  



但表扫描的问题是会锁住整个表,阻塞其他事务,导致系统业务大面积瘫痪。

所以,虽然通过直接的删除方法会速度快,但如果通过索引和分批处理,那么只会锁定需要删除的一批数据,而其他的数据则不会锁定,那么导致的阻塞问题就小多了。

 

3、所以结合上面的2点,当大批量操作时,如果最后提交,那么整个操作效率更高,但是可能会导致阻塞的问题,因为不及时提交,会导致其他事务都被阻塞。



同样的,通过直接删除效率可能更高,但会锁表,会导致严重的阻塞问题,而通过索引和分批处理,虽然效率不是太高,但可以分批处理,相当于分批提交,而每一批都通过索引,只锁住需要处理的记录,而其他的记录都不会锁住,那么就不太会导致阻塞的问题。



所以,大批量的删除操作,如果通过全表扫描,适合在晚上系统比较空闲的维护时间内进行;而如果一定要在白天执行,那么可以考虑通过索引和分批处理,来减少阻塞的问题,但还是会对系统产生一定的影响,特别是内存方面。


转:https://blog.csdn.net/sqlserverdiscovery/article/details/10008689

分享到:
评论

相关推荐

    SQL SERVER性能优化综述

    总的来说,SQL Server性能优化是一个涉及多个层面的综合工程,需要在各个阶段做出明智决策,平衡规范化、冗余、主键与外键设计、字段选择、物理存储和系统架构等方面,以确保系统在处理大量并发用户和大数据量时仍能...

    MS SQL Server查询优化方法

    - **批量处理**:对于大量数据的插入、更新或删除操作,尽量使用批量处理来减少I/O操作次数。 - **查询重写**:对某些特定类型的查询进行重写,以便更好地利用现有的索引和统计信息。 #### 三、总结 通过对上述...

    SQL Server性能优化

    SQL Server性能优化是一个涉及多个层面的复杂话题,它涵盖了从系统分析、设计到实施的全过程。以下是对标题和描述中提到的知识点的详细说明: 1. **系统分析阶段**: 在此阶段,性能需求不应被忽视。实时性、响应...

    Sql Server查询优化

    本文主要探讨了Microsoft SQL Server中的查询优化技术,以及影响其性能的主要因素,并针对每一个问题提供了相应的解决方案。 首先,Microsoft SQL Server是一个关系型数据库管理系统,它使用MS-SQL和Transact-SQL...

    优化SQL Server数据库的经验总结

    SQL Server作为一款广泛使用的数据库管理系统,其优化工作对于提高系统整体性能至关重要。本文将详细介绍SQL Server数据库优化的关键知识点,包括优化数据库的注意事项、SQL语句优化的基本原则等内容。 #### 二、...

    SQLSERVER性能优化综述

    本文将结合实践经验,对SQL Server数据库性能优化进行全面总结,旨在帮助读者掌握有效提升系统性能的方法。 #### 二、分析阶段 在系统开发初期,即分析阶段,需明确性能需求,并根据需求制定相应策略。 - **实时性...

    SQL Server 2000完结篇系列之七:SQL Server 2000索引优化详解

    在SQL Server 2000中,索引是数据库性能优化的关键组成部分,它极大地影响了数据查询的速度。本文将深入探讨SQL Server 2000中的索引优化,旨在帮助数据库管理员和开发人员理解如何有效地利用索引来提升系统性能。 ...

    T-SQL性能调优秘笈 基于SQL Server 2012窗口函数_PDF电子书

    T-SQL性能调优是一项复杂但重要的任务,尤其是在处理大量数据时。合理利用窗口函数可以极大地简化查询逻辑,而通过实施有效的性能调优策略,则能够确保查询执行的高效性。此外,持续监控系统性能并通过调整索引、...

    [SQL Server 2005性能调优]

    通过以上这些方法,你可以逐步提升SQL Server 2005的性能,确保系统在处理大量数据时仍能保持高效运行。"chapter4.sql"文件可能是具体的一个优化案例或教程,通过学习和实践,你可以进一步加深对SQL Server 2005性能...

    影响SQL server性能的三个关键

    本文将详细探讨影响SQL Server性能的三个关键因素:逻辑数据库设计、索引设计以及适当的索引维护。 首先,逻辑数据库设计是优化性能的基础。合理的设计可以减少数据冗余,提高数据一致性,并降低查询复杂性。标准化...

    SQL Server资料全篇

    性能优化是数据库管理的重要环节,这份PPT涵盖了监控、分析和调优SQL Server性能的方法,包括索引优化、查询优化器的工作原理和T-SQL的性能改进策略。 7. **实战经验** "我的老师10年的工作积累"这部分,意味着...

    SQL Server2008(数据存储和数据类型).ppt

    在SQL Server 2008中,数据存储和数据类型的改进是主要的亮点,这些更新旨在提高数据库性能、节省存储空间以及支持更多样化的数据类型。 首先,让我们深入了解一下数据存储的改进。SQL Server 2008引入了两种新的...

    Win10可用的Microsoft SQL Server 2008 Native Client

    这个组件是专为SQL Server设计的,旨在提高性能、安全性和兼容性,特别是在处理大量数据时。在本场景中,"Navicat"被提及,这是一个流行的数据管理工具,用户可以使用它通过SQL Server 2008 Native Client来远程或...

    SQL Server遍历表中记录的2种方法(使用表变量和游标)

    游标允许我们按需处理数据集的一个行,但它们通常效率较低,特别是在处理大量数据时。游标的基本用法如下: ```sql DECLARE @tempId INT, @tempName VARCHAR(10); DECLARE test_Cursor CURSOR LOCAL FOR SELECT [id...

    SQL Server只能编辑前200行问题

    1. **性能优化**:大量数据的加载和渲染会显著增加内存消耗和CPU使用率,尤其是在低配置的计算机上,这可能导致系统变慢或崩溃。 2. **用户友好性**:限制编辑行数可以避免用户在不知情的情况下对大量数据进行误操作...

    从统计数据表记录总数谈SQL Server查询优化.pdf

    统计数据的准确度对于查询性能的影响极大,特别是当处理大量数据的表时。例如,如果一个表的记录总数统计信息不准确,查询优化器可能低估或高估了实际的行数。这会导致优化器做出错误的估算,比如它可能认为一个...

    SQL Server查询优化探析.pdf

    它的优化对于提升数据库的性能至关重要,尤其是在处理大量数据和复杂查询时。查询优化可以显著提升数据库的响应时间,降低网络和磁盘I/O的负载,减少CPU的消耗。 本文首先概述了SQL Server的查询优化技术,然后重点...

    sql server优化.rar

    本压缩包文件"sql server优化.rar"包含了关于SQL Server性能优化的一些实用方法,以下将详细介绍其中可能涵盖的关键知识点。 1. **索引优化**:索引是提升SQL查询速度的关键。理解如何创建和管理非聚簇索引、聚簇...

    SQL Server数据库的性能优化技术研究.pdf

    本文主要探讨了SQL Server数据库性能优化的各个方面。首先,文章指出效率问题是数据库应用开发和管理过程中经常遇到的问题,并且随着应用的扩大,性能问题变得日益突出。文章强调了解决数据库性能问题的紧迫性,包括...

Global site tag (gtag.js) - Google Analytics