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

场景引入

上文说书到“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