SQL 数据插入、删除 大数据
2024-10-16 07:13:14
--测试表
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;
最新文章
- monkey工具使用中遇到的问题之二：尝试了各种方法通过adb都无法找到设备
- 使用samba实现linux与windows共享（测试成功）
- C# 方法返回值的个数
- Weblogic的安装与配置
- Web性能测试工具JMeter
- [原创]IBM BLM模型思维导图
- javaScript基础练习题-下拉框制作(CSS)
- Blend操作入门: 别站在门外偷看，快进来吧！(转)
- 递归算法实现10进制到N进制的转换
- [iOS 多线程 & 网络 - 4.0] - AFN框架简单使用
- js的MVC结构设计
- redis 中文手册
- LeetCode 81. Search in Rotated Sorted Array II（在旋转有序序列中搜索之二）
- arcgis api 3.x for js 共享干货系列之一自写算法实现地图量算工具（附源码下载）
- orcal -学习准备
- linux之sed的使用
- 现代程序设计 homework-02
- R语言编程艺术#02#矩阵（matrix）和数组（array）
- C#-1-2-C#基础
- jQuery获取动态产生的html内标签或元素