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

xiaoxiao2025-11-12 10

场景引入

上文说书到“SQLTest系列之INSERT语句简单测试”，于是，菜鸟想深入了解：“在现实业务场景中，实际的表数据不可能是完全一样的。所以，我们需要完全模拟实际场景，如何将表数据完全参数化的方式来INSERT到表中呢？”。带着问题来研究SQLTest，问题快速的迎刃而解。

环境准备

随着研究的深入，菜鸟了解到SQLTest的强大，SQLTest支持将一个存储过程的输出结果集做为插入表数据的来源，先来初始化测试环境吧。

创建测试对象

首先，我们需要创建一系列测试对象，参见下面的脚本。

--create SQLTestDemo database USE master IF DB_ID('SQLTestDemo') IS NULL CREATE DATABASE SQLTestDemo GO --create test table: orders 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 --create test database for save Store Procedure USE master GO IF DB_ID('Test') IS NULL CREATE DATABASE Test ; GO USE test GO IF OBJECT_ID('SQLTest1_ParameterValues_Proc','P') IS NOT NULL DROP PROC SQLTest1_ParameterValues_Proc GO IF OBJECT_ID('SQLTest1_ParameterValues_Log_Table','U') IS NOT NULL BEGIN TRUNCATE TABLE SQLTest1_ParameterValues_Log_Table DROP TABLE SQLTest1_ParameterValues_Log_Table END GO --create test table for calling log record CREATE TABLE SQLTest1_ParameterValues_Log_Table ( id int identity primary key, ctime datetime , SQLTestInstanceGUID uniqueidentifier , SQLTestWorkloadGUID uniqueidentifier , SQLTestWorkloadName nvarchar (256) , Workload int, Connection int , ConnectionString nvarchar (max) , Thread int, BatchIndex int , CommandText nvarchar (max) , Iteration int) GO --create Store Procedure CREATE PROC SQLTest1_ParameterValues_Proc ( @SQLTestInstanceGUID uniqueidentifier , @SQLTestWorkloadGUID uniqueidentifier , @SQLTestWorkloadName nvarchar (256) , @Workload int , @Connection int , @ConnectionString nvarchar (max) , @Thread int , @BatchIndex int , @CommandText nvarchar (max) , @Iteration int ) AS BEGIN --logging INSERT INTO SQLTest1_ParameterValues_Log_Table (ctime , SQLTestInstanceGUID , SQLTestWorkloadGUID , SQLTestWorkloadName , Workload , Connection , ConnectionString , Thread , BatchIndex , CommandText , Iteration ) SELECT getdate () , @SQLTestInstanceGUID , @SQLTestWorkloadGUID , @SQLTestWorkloadName , @Workload , @Connection , @ConnectionString , @Thread , @BatchIndex , @CommandText , @Iteration --Output data set: will be saved to Orders table under SQLTestDemo database. DECLARE @number_of_100K_rows int = 20 , @current_date datetime = getdate() , @batch_count int = @Thread ; SELECT OrderID = t1.c1 , OrderDate = dateadd (second, (c1 - (100000 * @number_of_100K_rows)), @current_date) , CustomerID = case when (c1 % 10000) between 1 and 500 then (((c1 - 1) % 10000) + 1) else ((((c1 - 1) % 25) + 1) * 20) end , SourceID = case when (((c1 - 1) % 50) + 1) % 2 = 1 then (((((c1 - 1) % 50) + 1) / 2) + 1) else 1 end , StatusID = 1 , Amount = cast (rand (cast (newid () as varbinary)) * 1000 as decimal (18, 2)) , OrderDetails = replicate ('a', 7000) FROM ( SELECT TOP (100000) ((@batch_count - 1) * 100000) + row_number () over (order by t1 . column_id) as c1 FROM sys.all_columns t1 cross apply sys.all_columns t2 )as t1 END GO

Server Driven Test设置

这个SQLTest GUI的设置是告诉SQLTest数据来源存储过程的连接字符串和存储过程名称前缀。设置方法如下：Settings => General Settings

Workload Settings

这个设置是告诉SQLTest我们是做参数化查询，不需要输出结果集。设置方法是：Settings => Workload Settings

Workload1 Setting

再到具体的Workload比如Workload1主界面做如下设置：SQL Client Connection String

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

SQL Command

insert into Orders values (@OrderID, @OrderDate, @CustomerID, @SourceID, @StatusID, @Amount, @OrderDetails) go

结果展示

完成上面的所有设置后，我们点击Start Current，等待10秒后，测试完毕。

SQL Profiler

测试过程中，我们使用SQL Profiler跟踪SQLTest的测试执行语句：

其中一条插入表数据的脚本代码

exec sp_executesql N'insert into Orders values (@OrderID, @OrderDate, @CustomerID, @SourceID, @StatusID, @Amount, @OrderDetails) ',N'@OrderID bigint,@OrderDate datetime,@CustomerID bigint,@SourceID bigint,@StatusID int,@Amount decimal(19,2),@OrderDetails varchar(7000)',@OrderID=13628,@OrderDate='2016-10-29 11:14:17.797',@CustomerID=60,@SourceID=1,@StatusID=1,@Amount=42.98,@OrderDetails='aaa...'

从这个插入语句，我们可以很清楚的知道，所有的插入语句已经被SQLTest参数化了。

检查调用日志

检查存储过程的调用日志

SELECT * FROM test.dbo.SQLTest1_ParameterValues_Log_Table WITH(NOLOCK)

结果如下：

检查Orders表数据

看看Orders表中的数据，由于数据太多，我们看100条数据好了。

SELECT TOP 100 * FROM SQLTestDemo.dbo.Orders WITH(NOLOCK) ORDER BY OrderID DESC

写在最后

菜鸟完成了SQLTest的参数化INSERT语句测试后，终于长长的松了一口气。看来SQLTest工具果然比较强大，来看看参考的链接。

参考链接http://www.sqltest.org/Documentation/HowToSQLTestInsertExampleWithParameterization