Delete删除思路1

适用于表中有大量数据，用Delete语句删除时慢的处理思路

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

DECLARE @DeleteRows INT
DECLARE @DeleteFlag INT = 1

WHILE @DeleteFlag > 0 BEGIN

	BEGIN TRANSACTION

	DELETE TOP (5000) orm
	FROM dbo.C_OrderRawMaterial orm 
		INNER JOIN dbo.C_Order od ON orm.OrderId = od.Id
	WHERE od.OrderStatus IN(200, 300) AND orm.OrderId IN (
		SELECT Id FROM dbo.C_Order o WHERE o.OrderStatus IN(200, 300) --AND orm.OrderId = o.Id
	)

	SET @DeleteFlag = @@ROWCOUNT
	--SET @DeleteRows = @DeleteRows + @DeleteFlag

	COMMIT TRAN
	--PRINT @DeleteRows
END

--PRINT @DeleteRows

先删除或禁用无关索引（无关索引，这里指执行计划不用到的索引，这里是指对当前DELETE语句无用的索引）。因为DELETE操作属于DML操作，而且大表的索引一般也非常大，大量DELETE将会对索引进行维护操作，产生大量额外的IO操作。
用小批量,分批次删除（批量删除比一次性删除性能要快很多）。不要一次性删除大量数据。一次性删除大量记录。会导致锁的粒度范围很大，并且锁定的时间非常长，而且还可能产生阻塞，严重影响业务等等。而且数据库的事务日志变得非常大。执行的时间变得超长，性能非常糟糕。

Delete删除思路2

这种情况一般适用于，要删除的数据大于表中的数据，只保留一小部份表中的数据

可以将要保留的小部份数据插入临时表tempA，然后用TRUNCATE TABLE命令快速删除Table中的数据，删除完成后，再将tempA表中的数据插入至Table。

sql server 中使用内存优化表

默认情况下，内存优化表具有完全持久性。与（传统）基于磁盘的表上的事务一样，内存优化表上的事务具有完全原子性、一致性、隔离性和持久性 (ACID)。内存优化表和本机编译的存储过程仅支持一部分 Transact-SQL 功能。

内存优化表的主存储器是主要内存。从内存读取表中的行和将这些行写入内存。表数据的另一个副本维护在磁盘上，但仅用于持续性目的。

MEMORY_OPTIMIZED 选项为ON（off为普通磁盘表，off为默认选项） DURABILITY 选项为SCHEMA_AND_DATA（schema_only是单纯的内存表，没有持久化），此选项是针对持久表的。

步骤：

（1）创建数据库和MEMORY_OPTIMIZED_DATA文件组（alter database add filegroup，每个数据库只能有一个MEMORY_OPTIMIZED_DATA文件组！！）

（2）添加一个存放数据文件的文件夹到文件组（请不要预先创建好这个文件夹，用alter database add file（） to filegroup）

（3）创建持久化/非持久化的内存优化表（sql server2014默认是持久化的），同时你还必须定义一个基于非聚集哈希索引的主键。哈希索引是在内存优化表中唯一支持的索引类型。

（4）使用本地编译存储过程：内存优化表支持本地编译存储过程，只要那些存储过程只引用内存优化表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

--启用数据库内存优化数据功能
ALTER DATABASE zkteco2021
ADD FILEGROUP Memory
CONTAINS MEMORY_OPTIMIZED_DATA;

GO

-- 增加数据库的内存文件夹
ALTER DATABASE zkteco2021
ADD FILE
    (
        NAME = 'zkteco2021_InMemory',
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\zkteco2021_InMemory'
    )
TO FILEGROUP Memory;

GO

--创建内存优化表，使用MEMORY_OPTIMIZED = ON来启用内存优化：
CREATE TABLE [dbo].[CachedData]
(
    [Key] [VARCHAR](900) NOT NULL,
    [Data] [VARBINARY](MAX) NOT NULL,
    [Expiry] [DATETIME] NOT NULL,
    PRIMARY KEY NONCLUSTERED ([Key] ASC)
)
WITH (MEMORY_OPTIMIZED = ON);

GO

限制：

（1）内存优化表必须放在有 CONTAINS MEMORY_OPTIMIZED_DATA 文件组的数据库下。

（2）内存优化表必须定义一个基于非聚集哈希索引的主键，且该表不支持外键或约束检查

（3）内存优化表必须要有足够的内存，否则插入更新等操作会失败。

（4）SQL Server 2014对这些表有着很多限制。例如，它们不支持外键或约束检查（感觉类似于MySQL的memory存储引擎），它们也不支持IDENTITY 字段或DML触发器。

参考：

https://learn.microsoft.com/zh-cn/sql/relational-databases/in-memory-oltp/defining-durability-for-memory-optimized-objects?view=sql-server-ver16

https://www.cnblogs.com/gered/p/12925354.html

Delete删除思路1#

Delete删除思路2#

sql server 中使用内存优化表#

Delete删除思路1

Delete删除思路2

sql server 中使用内存优化表