SQLTest系列之INSERT语句测试

场景引入

菜鸟不断又猛又持久的给老鸟惊喜以后，老鸟开始不断的折腾菜鸟：“鸟，你研究下有没有一款可以测试MSSQL Server的工具吧？”。
“这还不简单，用Red Gate的SQLTest呗”，于是菜鸟开始了工具的研究之旅：“要不，今天就分享下SQLTest之Insert语句测试吧”。

SQLTest简介

领了任务的菜鸟，由于之前对这个工具有所了解，所以还是比较轻车熟路的。让我们先来看看SQLTest是干什么的吧。
SQLTest是一款简单易用，非常容易上手的SQL Server性能、压力和单元测试工具。它既可以测试本地环境的SQL Server工作负载，也可以测试云环境的SQL Server服务。

SQLTest一键安装

SQLTest就是一个简单的SQL Server测试工具，所以，它的安装过程也简单。官方推荐一键安装，简单到令人发指的地步。

下载地址：
http://www.sqltest.org/Download

测试环境

在测试之前，菜鸟汇总自己的测试环境信息：
CPU：4 cores
Memory：4 GB
Disk: SSD
SQL Server: SQL Server 2008R2 SP2

SQLTest INSERT语句测试

老实讲，上面都不重要，看好了，这里才是本文的重点：如何使用SQLTest来测试INSERT的效率呢？如何测试INSERT语句在不同线程数量下的效率？不同的数据类型选择对INSERT效率的影响如何？
这里虚拟一个场景，假设我们有一张名为Orders的订单表，我们会根据Orders的主键数据类型的不同来测试INSERT的效率。

INT IDENTITY

创建测试数据库和Orders表

use master;

IF DB_ID('SQLTestDemo') IS NULL
    CREATE DATABASE SQLTestDemo
go

use SQLTestDemo
go
IF OBJECT_ID('Orders','U') IS NOT NULL
BEGIN
    TRUNCATE TABLE Orders
    DROP TABLE Orders
END
GO
CREATE TABLE Orders (
OrderID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
, OrderDate datetime
, CustomerID int
, SourceID int
, StatusID int
, Amount decimal (18, 2)
, OrderDetails char(7000)
)
GO

所有准备工作就绪，菜鸟迫不及待的开始测试了，开启SQLTest，设置SQLClient Connection String

Data Source=(local);Database=SQLTestDemo;Integrated Security=true;Pooling=false

SQL Command

insert into Orders values (getdate (), 1, 1, 1, 1, replicate ('a', 7000))
go

Number of Threads

点击Start Current按钮，测试时间10秒后，得到如下截图：
1个线程运行10秒钟，迭代了12471次，每次迭代消耗数据库时间0.000秒。（由于这里精确到千分之一秒，也就是一毫秒，说明每次迭代耗时少于1毫秒）。

现在我们分别将线程数调整为2,4,8,16,32,64,128,256来测试，为了测试的相对准确性，请在测试之前执行“创建测试数据库和Orders表”中的代码，重新创建Orders表。SQLTest返回结果的设置方法如下：Settings => Workload Settings

测试完毕后，我们可以得到如下表格数据：

将这些数据绘制成直方图和折线图：

从这个图中，可以很直观的得出如下结论：

  • 从吞吐量来看：无输出结果方式远远大于有输出结果方式，前者是后者的两倍还多；
  • 从数据库平均耗时来看：无输出结果效率也远远高于有输出结果方式，后者是前者的两倍；
  • 从线程数量来看：并不是线程数开得越多，SQL Server吞吐量越大，效率越高；无论是有输出结果方式还是无输出结果方式，并发8到16个线程SQL Server的吞吐量达到最大，效率最高；

注意：
最后一个结论不一定适用于所有的SQL Server，因为这个和SQL Server的版本，机器的CPU，Memory，磁盘等有密切的关系，用户在得到这个值之前需要自己严格测试。

提供INT值

完成了主键值INT IDENTITY的测试后，菜鸟陷入了疑惑：每个线程如何插入不同的值呢？于是有了这个测试方法：

use SQLTestDemo
go
IF OBJECT_ID('Orders','U') IS NOT NULL
begin
    truncate table Orders
    drop table Orders
end
go
create table Orders (OrderID int primary key clustered
, OrderDate datetime
, CustomerID int
, SourceID int
, StatusID int
, Amount decimal (18, 2)
, OrderDetails char (7000)
)
go

让每个线程生成不同的OrderID，我们可以使用SQLTest_Thread来代替线程数，SQLTest_Iteration代替迭代次数，最终将SQL Command修改为：

insert into Orders values (({SQLTest_Thread} * 100000) + {SQLTest_Iteration}, getdate(), 1, 1, 1, 1, replicate ('a', 7000))
go

UNIQUEIDENTIFIER with NEWID()

测试方法类似于“INT IDENTITY”章节，只是Orders表结构和SQL Command不一致。

use SQLTestDemo
go
IF OBJECT_ID('Orders','U') IS NOT NULL
begin
    truncate table Orders
    drop table Orders
end
go
create table Orders (
OrderID uniqueidentifier not null default newid () primary key clustered
, OrderDate datetime
, CustomerID int
, SourceID int
, StatusID int
, Amount decimal (18, 2)
, OrderDetails char (7000))
go
SQL Command
insert into Orders values (NEWID(),getdate (), 1, 1, 1, 1, replicate ('a', 7000))
go

UNIQUEIDENTIFIER with NEWSEQUENTIALID()

同上，测试方法类似于“INT IDENTITY”章节，只是Orders表结构和SQL Command不一致。

use SQLTestDemo
go
IF OBJECT_ID('Orders','U') IS NOT NULL
BEGIN
    TRUNCATE TABLE Orders
    DROP TABLE Orders
END
GO
CREATE TABLE Orders (
OrderID uniqueidentifier default newsequentialid () primary key clustered
, OrderDate datetime
, CustomerID int
, SourceID int
, StatusID int
, Amount decimal (18, 2)
, OrderDetails char (7000))
GO
SQL Command
insert into Orders(OrderDate,CustomerID,SourceID,StatusID,Amount,OrderDetails) values (getdate (), 1, 1, 1, 1, replicate ('a', 7000))
go

总结

将四种数据类型在No Result输出情况汇总统计如下表：

做一个漂亮炫酷的图表出来对比下：

从这个图标，我们可以发现如下规律：

  • 从吞吐量角度来看：所有数据类型，并发量聚集在8到16时，INSERT操作吞吐量达到最大值；
  • 吞吐量表现最好的是int identity数据类型和uniqueidentifier + newsequentialid做为主键的表；
  • 从数据库平均耗时角度：所有数据类型，并发量在8到16时，INSERT操作的平均时间消耗最小，接近64个线程时，平均耗时会急剧上升；
  • 平均耗时表现最好的是int identity和Newsequentialid类型。

从结果来看，UNIQUEIDENTIFIER + NEWSEQUENTIALID和INT IDENTITY性能和吞吐量表现都非常好，我们到底该选择哪一个更好一些呢？ 我的结论是选择IDENTITY属性的数字类型字段做为主键，因为它占的空间更小，INT为4个字节，BIGINT为8个字节而UNIQUEIDENTIFIER 占了36个字节。

写在最后

老鸟看完菜鸟的研究报告，赞不绝口：“不错啊，今天的最好表现就是明天对你的最低要求，SQLTest INSERT如何做参数化测试啊？”。
菜鸟卖起了关子：“鸟哥，预知后事如何，且听下回分解”。

时间： 2024-09-08 10:23:07

SQLTest系列之INSERT语句测试的相关文章

SQLTest系列之参数化INSERT语句测试

场景引入 上文说书到"SQLTest系列之INSERT语句简单测试",于是,菜鸟想深入了解:"在现实业务场景中,实际的表数据不可能是完全一样的.所以,我们需要完全模拟实际场景,如何将表数据完全参数化的方式来INSERT到表中呢?".带着问题来研究SQLTest,问题快速的迎刃而解. 环境准备 随着研究的深入,菜鸟了解到SQLTest的强大,SQLTest支持将一个存储过程的输出结果集做为插入表数据的来源,先来初始化测试环境吧. 创建测试对象 首先,我们需要创建一系列

Mysql insert语句的优化总结

1) 如果你同时从同一客户插入很多行,使用多个值表的INSERT语句.这比使用分开INSERT语句快(在一些情况中几倍).  代码如下 复制代码 Insert into test values(1,2),(1,3),(1,4)- 一条SQL语句插入多条数据. 常用的插入语句如:  代码如下 复制代码 INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content

INSERT语句的速度

INSERT语句的速度 插入一个记录需要的时间由下列因素组成,其中的数字表示大约比例: 连接:(3) 发送查询给服务器:(2) 分析查询:(2) 插入记录:(1x记录大小) 插入索引:(1x索引) 关闭:(1) 这不考虑打开表的初始开销,每个并发运行的查询打开. 表的大小以logN (B树)的速度减慢索引的插入. 加快插入的一些方法: ·         如果同时从同一个客户端插入很多行,使用含多个VALUE的INSERT语句同时插入几行.这比使用单行INSERT语句快(在某些情况下快几倍).如

将表里的数据批量生成INSERT语句的存储过程 增强版_MsSql

有时候,我们需要将某个表里的数据全部或者根据查询条件导出来,迁移到另一个相同结构的库中 目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的,只有借助第三方工具(third party tools) 这种脚本网上也有很多,但是网上的脚本还是欠缺一些规范和功能,例如:我只想导出特定查询条件的数据,网上的脚本都是导出全表数据 如果表很大,对性能会有很大影响 这里有一个存储过程(适用于SQLServer2005 或以上版本) -- Author: <桦仔> -- Blog

一条insert语句导致的性能问题分析（一)

今天早上开发找我看一个问题,说他们通过程序连接去查一个表的数据的时候,只查到了8条记录,这个情况着实比较反常,因为从业务上的数据情况来说,不可能只有8条. 但是开发没有太多的权限做线上环境的数据检查,就让我帮忙看一下. 语句大概是下面这样的形式. select count(*) from TEST_VIP_LOG t where t.flag in(2,3) and insert_time >= to_date('2016-03-10','YYYY-MM-DD') and insert_time

一条insert语句导致的性能问题分析（二)

今天对之前描述的问题一条insert语句导致的性能问题分析(一) 进行了进一步的补充. 有一条insert语句的主要性能瓶颈在于insert子句中的查询语句,查询中的主要资源消耗在于对两个表进行了多次关联 语句主要的结构如下: insert into xxxxx   (select * from TEST_vip_new minus select * from TEST_vip_new_bak         ) a left join TEST_vip_new_bak b         on

MySQL中insert语句没有响应的问题分析(r11笔记第21天)

 今天开发的一个同学问我一个MySQL的问题,说在测试数据库中执行一条Insert语句之后很久没有响应.我一看语句是一个很常规的insert into xxx values形式的语句.看起来有些不太合乎常理啊,我对这类问题立马来了兴趣,准备好好看看到底是什么原因.  向开发同学了解了环境之后,我登录到服务端,首先查看是否可能是磁盘空间不足导致的问题.结果df -h的结果显示,空间还绰绰有余. 使用show proceslist查看线程情况. 可以看到大量的线程是Waiting for table

11g中利用listagg函数实现自动拼接INSERT语句

本来今天想继续写另一篇外传,但总是熬这么晚不是个事儿,况且今儿北京又输了,恨铁不成钢,堵得慌... 白天工作忙,晚上看娃睡了之后才有一些时间可以随便写一些,总结一下,记录一下,算是让自己内心的各种问题抒发释放一下.碰巧打开电脑,有位测试的同事下午留言问了一个问题,一想干脆今儿休息一下,写篇短小精悍的,更接地气一些的文章,至少还是工作中可以用到的,这位同事的留言是这样, 我怎么从一个表中提取 所有字段 一个表字段太多 我要写insert的语句 一个个粘字段 好费劲... 首先,11.2版本中限制每

将表里的数据批量生成INSERT语句的存储过程 增强版

有时候,我们需要将某个表里的数据全部或者根据查询条件导出来,迁移到另一个相同结构的库中 目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的,只有借助第三方工具(third party tools) 这种脚本网上也有很多,但是网上的脚本还是欠缺一些规范和功能,例如:我只想导出特定查询条件的数据,网上的脚本都是导出全表数据 如果表很大,对性能会有很大影响 这里有一个存储过程(适用于SQLServer2005 或以上版本) -- Author: <桦仔> -- Blog