How to SQLTest Insert Example with Value Substitution
In this example we will cover:
1. Inserting into the Orders table with OrderID as int with single and many threads using value substitution for OrderID.
2. Inserting into Orders table with OrderID as int with single and many threads using value substitution for all columns.
For all of the above examples create a database SQLTestDemo on the target SQL Server, where we want to run the test. If possible, keep the data and the transaction log file on different volumes.
use master
create database SQLTestDemo
go
1. Inserting into the Orders table with OrderID as int with single and many threads using value substitution for OrderID:
Create Orders table on the target SQL Server:
use SQLTestDemo
go
if exists (select * from sys . objects where name = 'Orders')
drop table Orders
go
create table Orders (OrderID int primary key clustered, OrderDate datetime, CustomerID int, SourceID int, StatusID int, Amount decimal (18, 2), OrderDetails char (350))
go
Create the following stored procedure on the SQL Server which we will use to supply values for substitution. It is recommended to use a local server, you need to enter the connection string to the server under Settings -> general Settings Menu, SQLTest Connection String TextBox. Also for this example we will use “SQLTest1” as the stored procedure prefix instead of the default “SQLTest”.
My SQLTest connection string (uses test database): “Data Source=SQLWorkshops\SQL2012;Database=test;Integrated Security=true”
use test
go
drop proc SQLTest1_SubstituteValues_Proc
go
drop table SQLTest1_SubstituteValues_Log_Table
go
create table SQLTest1_SubstituteValues_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 proc SQLTest1_SubstituteValues_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
insert into SQLTest1_SubstituteValues_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
select top 100000 row_number () over (order by t1 . column_id) + (@Thread * 100000) as OrderID from sys . all_columns t1 cross apply sys . all_columns t2
end
go
select * from SQLTest1_SubstituteValues_Log_Table
go
In the SQLClient Connection String(s) RichTextBox enter the target SQL Server connection string:
In my case it is
“Data Source=SQLWorkshops\SQL2014;Database=SQLTestDemo;Integrated Security=true;Pooling=false”.
Change the Data Source name to your target SQL Server name. If you have a default instance installed on your local computer and if you want to use that as target server, then you can leave the Data Source as “(local)”. In addition, change the Database name from “master” to “SQLTestDemo”.
In your case it might be
“Data Source=(local);Database=SQLTestDemo;Integrated Security=true;Pooling=false”
In the SQL Command enter the following statement:
insert into Orders values (@OrderID, getdate (), 1, 1, 1, 1, replicate ('a', 350))
go
Under Settinsg -> Workload Settings Menu enable “Substitute Values”.
Under Settinsg -> Workload Settings Menu, it is recommended to disable “Show Results” and execute the inserts using ExecuteNonQuery instead of ExecuteReader.
Set the number of thread to 1 initially and finally click “Start All” under Workload GroupBox to start the workload.
Below you can see the test ran for 10 seconds, executed 7152 iterations with average database response time of 1.164 milliseconds per iteration.
The statement has been substituted with values, under SQL Profiler, you will see the below statement:
insert into Orders values (100001, getdate (), 1, 1, 1, 1, replicate ('a', 350))
go
insert into Orders values (100002, getdate (), 1, 1, 1, 1, replicate ('a', 350))
go
Now we can rerun the test with number of threads set to 2. Notice the iterations completed has increased to 14288 with database response time of 1.120 milliseconds.
2. Inserting into Orders table with OrderID as int with single and many threads using value substitution for all columns:
The steps are the same as the first example, except the stored procedure that supplies values for substitution and the SQL Command has to be change as below.
Stored procedure:
use test
go
drop proc SQLTest1_SubstituteValues_Proc
go
drop table SQLTest1_SubstituteValues_Log_Table
go
create table SQLTest1_SubstituteValues_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 proc SQLTest1_SubstituteValues_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
insert into SQLTest1_SubstituteValues_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
declare @number_of_100K_rows int = 20
declare @current_date datetime = datetimefromparts (2014, 12, 4, 23, 59, 59, 0)
declare @batch_count int = @Thread
select c1 as OrderID,
dateadd (second, (c1 - (100000 * @number_of_100K_rows)), @current_date) as OrderDate,
case when (c1 % 10000) between 1 and 500 then (((c1 - 1) % 10000) + 1) else ((((c1 - 1) % 25) + 1) * 20) end as CustomerID,
case when (((c1 - 1) % 50) + 1) % 2 = 1 then (((((c1 - 1) % 50) + 1) / 2) + 1) else 1 end as SourceID,
1 as StatusID,
cast (rand (cast (newid () as varbinary)) * 1000 as decimal (18, 2)) as Amount,
replicate ('a', 350) as OrderDetails
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
select * from SQLTest1_SubstituteValues_Log_Table
go
SQL Command:
insert into Orders values (@OrderID, '@OrderDate', @CustomerID, @SourceID, @StatusID, @Amount, '@OrderDetails')
go