How to SQLTest Insert ~ Example with Parameterization
In this example we will cover:
1. Inserting into the Orders table with OrderID as int with single and many threads using parameterization for OrderID.
2. Inserting into Orders table with OrderID as int with single and many threads using parameterization 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 parameterization 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 parameters. 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_ParameterValues_Proc
go
drop table SQLTest1_ParameterValues_Log_Table
go
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 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
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
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_ParameterValues_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 “Parameterize Query”.
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 23851 iterations with average database response time of 0.218 milliseconds per iteration.
The statement has been parameterized, under SQL Profiler, you will see the below statement:
exec sp_executesql N'insert into Orders values (@OrderID, getdate(), 1, 1, 1, 1, replicate(''a'', 350))
', N'@OrderID bigint', @OrderID = 100001
go
exec sp_executesql N'insert into Orders values (@OrderID, getdate(), 1, 1, 1, 1, replicate(''a'', 350))
', N'@OrderID bigint', @OrderID = 200002
go
Now we can rerun the test with number of threads set to 2. Notice the iterations completed has increased to 40459 with database response time of 0.236 milliseconds.
2. Inserting into Orders table with OrderID as int with single and many threads using parameterization for all columns:
The steps are the same as the first example, except the stored procedure that supplies parameter and the SQL Command has to be change as below.
Stored procedure:
use test
go
drop proc SQLTest1_ParameterValues_Proc
go
drop table SQLTest1_ParameterValues_Log_Table
go
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 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
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
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_ParameterValues_Log_Table
go
SQL Command:
insert into Orders values (@OrderID, @OrderDate, @CustomerID, @SourceID, @StatusID, @Amount, @OrderDetails)
go