Using Variables in Benchmark Runner Scenario

You can create parametric SQL statements in your Benchmark Runner load test scenarios by substituting hard-coded values for any SQL statement (such as: SELECT, INSERT or UPDATE) with values dynamically retrieved from external text data files. You can define data retrieval rules for the load test scenario variables to simulate any application logic.

For example, to simulate a load a large "order" table that is fragmented across multiple physical chunks; you can create a data file with several hundreds of order IDs taken from beginning, middle and end of table's data range, define a variable representing an order ID and choose random retrieval method for this variable. When you use this variable in the WHERE clause of the SELECT statement defined against the "order" table within load test scenario's task, the SELECT statement will randomly hit different physical chunks, simulating what usually happens in real-life. Because of the different chunks might have different performance characteristics, you will get a much better estimation of your SQL SELECT statement performance than if you would try it with just hard-coded order IDs.

To define a load test scenario's variable:

 

  1. While defining an SQL script for a given load test scenario's task, press the Edit Variables button. The Edit Script Variable dialog box will open.
  2. Press the New Variable button
  3. Choose logical variable name, for example: "ID_Order"
  4. Choose the Read Order option. If you select the Random option, the values will be read from the text data file randomly. If you select the Sequential option, the values will be read sequentially and restarted from the beginning when the end of the file is reached.
  5. Select the external data file that contains the test values. It should be text file with one column of data separated by end of line or tab character. For string values, do not use quotes. For datetime and interval values, use any supported string representation that can be converted to a corresponding datatype.
  6. Repeat from step 2 to add additional variables.

 

You can use defined variables anywhere in the SQL script where actual data constants are used. Use $variable_name syntax to reference the variable. For example you can use them in WHERE clause of SELECT statement or in VALUES clause of INSERT statement.

 

Example,

 

SELECT id_payment, amount, discount

FROM tbl_payments

WHERE id_order = $ID_Order