DTM DB Stress: Dynamic SQL Statements

Table of Content

Dynamic SQL Statements

The program supports dynamic statements as well as static statements. Their final state will be defined at the moment of execution. To make it possible, special tags (like ?1, ?2, etc.) are inserted into the text of a statement. Those tags will be replaced with the values from the file or generated randomly. They are always replaced, even inside string constants.

The file with values is a text file. Each line in it corresponds to one statement (single thread execution), while values are separated by the separator character specified in the task properties. By default, columns should be separated by <tab> sign but professional and enterprise versions users can select or define custom columns separator. If there are more values in the file than in a statement, extra values are ignored. If there are more tags than values, tags with bigger numbers will not be replaced with any values.

There are three ways to share values file between task's threads. They are:

Independently. In this case, each thread uses rows line by line.
Sequentially. A thread uses next row after last used by same or another task's thread.
Fixed. Each thread uses the unique line: the first thread uses first line, the second uses second, etc.

Examples:

SQL statement template	Values file content	Actual statements
select * from ?1	table1 table2 table3	select * from table1 select * from table2 select * from table3 select * from table1 etc.
select ?1 from ?2 order by ?1	field1<tab>table1 field2<tab>table2	select field1 from table1 order by field1 select field2 from table2 order by field2 select field1 from table1 order by field1 etc.
?1	select * from table1 insert into table2 values(getdate()) sp_helptext 'dbo.tr12_t'	select * from table1 insert into table2 values(getdate()) sp_helptext 'dbo.tr12_t' select * from table1 insert into table2 values(getdate()) sp_helptext 'dbo.tr12_t' etc.