SQL 数据插入、删除大数据-白红宇

SQL 数据插入、删除大数据

阅读量：5166 次

发布时间：2019-06-13

本文共 2588 字，大约阅读时间需要 8 分钟。

--测试表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 <= 100000BEGIN    INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_' + CAST(@Index AS CHAR(6)), 'system', GETDATE());    SET @Index = @Index + 1;ENDSELECT 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 <= 100000BEGIN    INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_' + CAST(@Index AS CHAR(6)), 'system', GETDATE());    SET @Index = @Index + 1;ENDSELECT 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 C2ORDER 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 = 1BEGIN    BEGIN TRAN    DELETE FROM [dbo].[Employee];    COMMIT    IF @@ROWCOUNT = 0        BREAK;ENDSET 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;

转载于:https://www.cnblogs.com/lenovo_tiger_love/p/3719989.html

你可能感兴趣的文章

支付宝移动支付之IOSApp调用支付宝钱包