首页 > 代码库 > SQL 数据插入、删除 大数据
SQL 数据插入、删除 大数据
--测试表 CREATE TABLE [dbo].[Employee] ( [EmployeeNo] INT PRIMARY KEY, [EmployeeName] [nvarchar](50) NULL, [CreateUser] [nvarchar](50) NULL, [CreateDatetime] [datetime] NULL );
--1、循环插入 SET STATISTICS TIME ON; DECLARE @Index INT = 1; DECLARE @Timer DATETIME = GETDATE(); WHILE @Index <= 100000 BEGIN INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, ‘Employee_‘ + CAST(@Index AS CHAR(6)), ‘system‘, GETDATE()); SET @Index = @Index + 1; END SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; SET STATISTICS TIME OFF;
--2、事务循环 BEGIN TRAN; SET STATISTICS TIME ON; DECLARE @Index INT = 1; DECLARE @Timer DATETIME = GETDATE(); WHILE @Index <= 100000 BEGIN INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, ‘Employee_‘ + CAST(@Index AS CHAR(6)), ‘system‘, GETDATE()); SET @Index = @Index + 1; END SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; SET STATISTICS TIME OFF; COMMIT;
--3、批量插入
SET STATISTICS TIME ON; DECLARE @Timer DATETIME = GETDATE(); INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJECT_ID]), ‘Employee_‘, ‘system‘, GETDATE() FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2 ORDER BY C1.[OBJECT_ID] SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; SET STATISTICS TIME OFF;
--4、CET插入
SET STATISTICS TIME ON; DECLARE @Timer DATETIME = GETDATE(); ;WITH CTE(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) AS( SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJECT_ID]), ‘Employee_‘, ‘system‘, GETDATE() FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2 ORDER BY C1.[OBJECT_ID] ) INSERT [dbo].[Employee] SELECT EmployeeNo, EmployeeName, CreateUser, CreateDatetime FROM CTE; SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; SET STATISTICS TIME OFF;
--5、循环删除
SET STATISTICS TIME ON; DECLARE @Timer DATETIME = GETDATE(); DELETE FROM [dbo].[Employee]; SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; SET STATISTICS TIME OFF;
--6、批量删除
SET STATISTICS TIME ON; DECLARE @Timer DATETIME = GETDATE(); SET ROWCOUNT 100000; WHILE 1 = 1 BEGIN BEGIN TRAN DELETE FROM [dbo].[Employee]; COMMIT IF @@ROWCOUNT = 0 BREAK; END SET ROWCOUNT 0; SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; SET STATISTICS TIME OFF;
--6、Truncate删除
SET STATISTICS TIME ON; DECLARE @Timer DATETIME = GETDATE(); TRUNCATE TABLE [dbo].[Employee]; SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; SET STATISTICS TIME OFF;
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。