Handling consumable parameter data in LoadRunner

Imagine that you are load testing the website for a new loyalty card scheme. Millions of people will be mailed a loyalty card, and they will need to register their details online, along with their card number. Each card number may only be used once. So, every time you run your test, you will consume a large amount of card numbers, and you must be sure to use unregisterd card numbers for each test.

This seems like an obvious time to use the File parameter type, with the parameter properties set to “Select next row: Unique”, and “When out of values: Abort vuser”.

So you request a whole lot of unused card numbers from your DBA, insert them into your File parameter and run your LoadRunner scenario. LoadRunner ensures that all the card numbers are only used once (even though you have hundreds of virtual users), and all of your Register Card business processes run successfully. BUT, when you run your test for the second time, all of your Register Card business processes fail, with the website displaying an error message of “We’re sorry, this card has already been registered”.

You have just discovered that LoadRunner does not make the parameter unique between tests; the parameter will only be unique within the LoadRunner scenario that is currently running. This is a problem anytime you have consumable data. To work around this problem, you really have a few options…

1. Restore/rollback the database: This is hugely impractical, as it means hours of environment outage when done in a real test environment with Production-sized data volumes. Funnily enough, this solution is seriously proposed in the official HP training for LoadRunner. Maybe the person who wrote the training hadn’t done much real load testing.
2. Get new data: Each time you wanted to run a test, you could ask your DBA to provide you with new data, or you could run a query against the database and do it yourself (always best to be as independent as possible). Then you could delete the current parameter values, and cut and paste the new values into your parameter file.
3. Remove the used parameter values from your parameter file: This is the option that we will examine further…

Removing the used data from the parameter file is less straight-forward than you might think.

When a File parameter is unique, LoadRunner will divide the data evenly into blocks and assign each block to a virtual user when it starts running. Each vuser will use the data in their assigned block sequentially.

This means that the used values aren’t all at the start of the file; they appear in chunks, spread evenly throughout the file (unless your scenario has a single virtual user).

Removing the used values from the parameter file by hand in Notepad is not really practical (unless there is only a few values). I know of three ways to make this task a little easier…

Method 1: Write used values to a text file. Remove used values using Excel

Using the standard library functions for writing to files (fopen, fprintf, fclose), modify your script to save the parameter values it consumes to a file.

After your test, you can remove the used values from your VuGen script’s parameter file by following these steps in Excel:

1. Cut and paste all the values from your parameter data file into column A.
2. Cut and paste your used values into column C.
3. Enter this formula into cell B1:

   =IF(ISERROR(MATCH(A1,C:C,0)),A1,"")

   The Excel functions used above are:
   MATCH( value, array, match_type ): value is the value to search for in the array. array is a range of cells that contains the value that you are searching for. match_type is optional. It is the type of match that the function will perform. Using 0 for this argument means that the MATCH function will find the first value that is equal to value. The array can be sorted in any order.
   ISERROR( value ): value is the value that you want to test. If value is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL), this function will return TRUE. Otherwise, it will return FALSE.
   IF( condition, value_if_true, value_if_false ): condition is the value that you want to test. value_if_true is the value that is returned if condition evaluates to TRUE. value_if_false is the value that is return if condition evaluates to FALSE.

4. Fill all the cells in column B with your formula. Click in B1, and move the mouse cursor to the bottom right corner of the cell until the white cross turns into a black cross. This is called the fill handle. Click and drag the fill handle down the column to fill it with your formula. Alternatively, double click on the fill handle and it will fill all cells in the column until it runs out of rows in the column to its left (this is the reason for the order of the columns that I have specified).
5. Remove the blank rows with a filter. Select column B and click on Data > Filter. Change the filter to exclude blanks.
6. Cut and paste all the values from column B back into your parameter data file.

Note: If you are going to write used data to a text file, it is important to write the value to the file at the start of your iteration rather than at the end, otherwise you may leave the data in an unusable state if your vuser stops half way through the business process.

Method 2: Write used values to a text file. Remove used values from parameter file with command-line tools

After using the Excel method for a while, I realised that I could probably do the same thing with a 1-line shell script, using pipes and some combination of sort, uniq, diff, and awk; instead I found that *nix has a neat tool called comm, which finds lines that are common to two files. Perfect.

Note that this solution requires you to find Unix command-line utilities that have been ported to Windows. Here is the solution in a batch file…

REM This batch file will remove used {CardNumber}s from the cards.dat parameter file.
REM Note that the parameter file will be overwritten each time this batch file is run.
REM Be sure to update the script path if you change the name of the script!
SET used_file="C:\LoadRunner\data\used_cards.txt"
SET param_file="C:\LoadRunner\scripts\register_loyalty_card_20120512_01\cards.dat"
REM comm arguments:
REM -1 : suppress lines unique to FILE1
REM -2 : suppress lines unique to FILE2
REM -3 : suppress lines that appear in both files
comm -3 %param_file% %used_file% > %param_file%

If you are really clever, you could make this batch file run automatically by putting it inside a system() call in a VuGen script that runs at the end of your scenario (i.e. after all the other groups have finished).

Sidenote: If you job involves processing text (data, logs, etc) and you aren’t familiar with the various Unix tools that you can glue together to manipulate data, then you are probably making life unnecessarily hard for yourself.

Method 3: Use a database to store values. Mark data as “used” at runtime

One of the frustrating things about the solutions which involve removing values from parameter files is when you run your test for 2 minutes and then realise that you forgot to change a critical runtime setting back to its correct value the last time your ran your script in VuGen. Now you need to stop your test and clean out your used values before you run it again.

You can avoid this problem entirely by removing the values as they are used (i.e. at runtime), with no manual steps. To do this, you must abandon LoadRunner File parameters, and use some kind of data store, which you will access using function calls in your script (note that some other performance testing tools handle this problem automatically with a “Data Pool” feature).

I prefer to use MySQL, and have written various functions to add and remove data. Using MySQL involves quite a bit of work upfront to set up your database and the tables to store your parameters, but this effort is repaid if you have to run more than a few tests. The official (but unsupported) solution from HP/Mercury is their Virtual Table Server (VTS2), which is easier to set up than MySQL, but is clunky to solve this “consumable data” problem with, and has performance problems under high load. Note that both of these solutions are for slightly more advanced LoadRunner users.