How to save test results

There is a possibility to save JMeter test results to xml or csv file for further work (e.g. generating reports, importing to a database, etc.). It can be easy done by adding Simple Data Writer listener to a Thread Group and specifiing the filename. Of course you can specify the filename as JMeter variable or property.



You can choose the format of results file (xml or csv) and the fields to be saved clicking on Configure button and checking necessary options. As for me I prefer xml format and default options except Save URL which is useful when I need to know exact URLs of HTTP requests.

at

Testing website search

Almost every modern website has search feature and it must be carefully and thoroughly tested. You will know from this post how to do it using JMeter.


First record necessary HTTP requests (including search page and search results page) using JMeter's recording proxy and group them into separate transactions.


Then go to HTTP Request sampler which corresponds to search results page and replace the keyword (which you used during recording) with some variable name, e.g., ${KEYWORD}.


Then prepare the list of keywords you want to use for search testing, save them to the text file and add CSV Data Set Config element to Thread Group. Specify the name of just created file and the variable name which will contain your keywords from file, e.g., KEYWORD (see above).


Now you are ready to test the search on your website.
at

How to add authentication to Test Plan

Most of websites have user communities and allow users to do some actions like posting comments if they are authenticated on this website. Often the performance of a web page can be quite different depending on that fact if this page was visited by authenticated user or anonymous one. So we need to test theese pages by both anonymous users and authenticated ones. How to add authentication is described below.


The steps are mostly similar to the steps of adding website search but have some small differencies.

First record HTTP/HTTPS requests going to authentication page with login/password form in your browser and submitting this form. You will get Authentication Form Page and Authentication Process transactions. Then you need to replace login and password parameters values by some variables like ${USER} and ${PASSWORD} on Authentication Process HTTP request settings.



After that you need to prepare the text file where each line contains username and password separated by some delimiter character, e.g., comma and add new CSV Data Set Config element like below.


And now all next HTTP requests will be performed by authenticated users.
at

Template for JMeter's recording proxy

If you often need to use JMeter's recording proxy you can save your time by using templates for HTTP request filtering. Just add HTTP Proxy Server element to Workbench, than add regular expressions for URLs of your site like www\.example\.com.* to "URL Patterns to Include" list, add regular expressions for exclusion of some static HTTP requests like .*\.css$ to "URL Patterns to Exclude" list and save this template using "Save Selection As..." item of HTTP Proxy Server's context menu.




Next time you can just open this file in JMeter and you will have ready for work recording proxy.
at

How to add HTTP Request sampler with dynamic URL

If you need to add HTTP Request sampler with dynamic URL that can be different each time from testing to testing you must use one of the Post-Processors offered by JMeter. I usually use Regular Expression Extractor for this purposes.


So, let's imaging that you have "Popular News" web page which contains the links to "News Story" web pages that contains the full texts of news and you want to write a test which will include both "News" page and random "News Story" page.

For this you must include "News" page HTTP Request sampler inside Simple Controller with a Regular Expression Extractor to store random "News Story" page URL to a variable:


Then use the variable with the stored random "News Story" page URL in "News Story" HTTP sampler:

at

Checking for empty variable using IF controller

Sometimes you need to add HTTP samplers with dynamic URL or with dynamic part of the URL which are stored in some variable after HTTP sampler post-processing.




But if Regular Expression Extractor will not find any matching string result variable will be set to default value (empty value in our case). We should test this variable for emptyness before we will use it.


Now if the News page has not any news we are sure that JMeter will handle this situation correctly.
at

Advanced test plan structure

Working with JMeter for about 2 years I've come to the optimal (for me) structure of the test plan. It's divided into several suites (thread groups) depending on user type (visitor or signed in user, etc.) and is quite large to simulate real user activity. Complex business operations can be grouped using Simple Controller for better readability of the test plan. Also do not forget about pauses.


Here is an example test plan structure:


Thread Group - Visitor Suite
  Simple Controller - Homepage
    Transaction Controller - Homepage
      HTTP Request Sampler
      ...
  Random Order Controller
    Simple Controller - News page
      Transaction Controller - News page
        HTTP Request Sampler
        ...
    ...
Thread Group - User Suite

  Simple Controller - Sign In page
    Transaction Controller - Sign In page
      HTTP Request Sampler
      ...
  Random Order Controller
    Simple Controller - View User Profile page
      Transaction Controller - View User Profile page
        HTTP Request Sampler
        ...
    ...
at

Hacking the response data size limit

View Results Tree is very useful listener especially when you are debugging your test plan. You can review the response data and ensure that your test plan works good. But sometimes you you can see a message in the beginning of the response data like "Response too large to be displayed. Size: 313674 > Max: 204800, Start of message:".


JMeter - Response too large to be displayed

And unfortunately you are not able to see the whole response data. By default JMeter shows only first 200 Kb of response data. To resolve this problem you should edit the file jmeter.properties and uncomment the line:
view.results.tree.max_size=0
 This will disable response data size check at all.

Uncommented view.results.tree.max_size=0

After you have saved jmeter.properties you will never see this message again.

The problem with the response data size limit has been resolved.

at

How to save test results

In the previous part I wrote how to save the testing results using Simple Data Writer. There is one more way about how to do it - using the command line options.


So, if you want to run the test plan from file test.jmx and save the testing results to the file log.jtl, use the following command:
jmeter -n -t test.jmx -l log.jtl
You can configure how the result data will be saved by editing the file jmeter.properties (section "Results file configuration"). For example, if you want to save the results in CSV format you should edit the following property:
jmeter.save.saveservice.output_format=csv
This way is more powerful than the one described in the previous part (Simple Data Writer) because it allows you to set some additional options like CSV delimiter, timestamp format, etc. Sometimes it could be very helpful.
at

Dive into Oracle : Data Grouping and Merging

In this post we are going to discuss about commonly used Oracle database queries for manipulate data mainly grouping and merging. If the database structure is normalized and when it comes to visualization of data, it is required to group data based on their common characteristics. Similarly in most of the database systems including Oracle, Merging is the functionality provided in order to migrate data between two databases, schema or tables.


Let's create a simple database structure as follows. We can use a tool like SQL Fiddle to run queries and see how it works. Make sure you have selected Oracle 11g R2 as DB type before running any queries.

?
1
2
3
4
5
6
CREATE TABLE SEMISTER_GPA(
    STUDENT_ID VARCHAR2(8),
    SEMISTER_ID VARCHAR2(4),
    GPA NUMBER(5,4),
    PRIMARY KEY (STUDENT_ID,SEMISTER_ID)
);

Adding some values to above tables.

?
1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000001','L1S1',3.5245);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000002','L1S1',3.6100);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L1S1',3.0589);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000001','L1S2',3.7241);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000002','L1S3',3.8180);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L1S2',3.2589);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000002','L1S4',3.8675);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000002','L2S1',3.2369);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L1S4',3.6523);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000001','L1S4',3.2675);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L2S1',3.4389);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L2S2',3.0523);

See in SQL Fiddle.

01. Simple grouping: Get the no. of semesters each student has completed.

?
1
SELECT STUDENT_ID,COUNT(*) FROM SEMISTER_GPA GROUP BY STUDENT_ID;









02. DENSE_RANK()and PARTITION BY

'PARTITION BY' is used partition a table into different segments which makes easier to manipulate each segment individually. Here 'SEMISTER_GPA' table can be partition by STUDENT_ID. In order to get the row which has the highest 'GPA' for each student, each segment  is ordered by 'GPA' in descending order. Then the row which has the DENSE_RANK()=1 gives the expected result.

?
1
2
3
SELECT DT.STUDENT_ID,DT.SEMISTER_ID, DT.GPA FROM (
    SELECT STUDENT_ID,SEMISTER_ID,GPA, DENSE_RANK() OVER (PARTITION BY STUDENT_ID ORDER BY GPA DESC) RNK FROM SEMISTER_GPA
    ) DT WHERE (DT.RNK = 1 )


 
 03. LISTAGG () function

LISTAGG () function gives a single row, concatenated using specified column within each group ordered. To get the list of semesters each student has completed, we can group table using STUDENT_ID order each group by GPA and concatenate column values using above function. 

?
1
SELECT STUDENT_ID, LISTAGG(SEMISTER_ID,',') WITHIN GROUP (ORDER BY SEMISTER_ID) AS SEMISTER_LIST FROM SEMISTER_GPA GROUP BY STUDENT_ID

Below mentioned is another sample data set. Timezone data is stored in two tables  TIME_ZONE andTIMEZONE_DESCRIPTIONS. In order to migrate data into a single denormalized table 'MERGE INTO .. USING.. ' statement can be used. Merging can handle insert or update unconditionally without knowing it's presence in target table. This is a more flexible way of moving large amounts of data extracted from one or more tables. See in SQL Fiddle.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE TIME_ZONE (
     TIMEZONE_ID NUMBER(3) NOT NULL,
     TIMEZONE_OFFSET VARCHAR2(10),   
     MODIFIED_DATE DATE DEFAULT SYSDATE  NOT NULL,
     PRIMARY KEY (TIMEZONE_ID)
);
 
CREATE TABLE TIMEZONE_DESCRIPTIONS (
     TIMEZONE_ID NUMBER(3) NOT NULL,
     LANGUAGE_ID VARCHAR2(2) DEFAULT 'EN'  NOT NULL,
     DESCRIPTION NVARCHAR2(100),
     MODIFIED_DATE DATE DEFAULT SYSDATE  NOT NULL,
     PRIMARY KEY (TIMEZONE_ID,LANGUAGE_ID)
);
 
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (1,'+0300',SYSDATE);
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (2,'+0200',SYSDATE);
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (3,'+0400',SYSDATE);
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (5,'-0400',SYSDATE);
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (6,'+0400',SYSDATE);
 
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (1,'EN','Saudi Arabia',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (2,'EN','Egypt',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (3,'EN','UAE',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (5,'EN','USA',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (6,'EN','Oman',SYSDATE);
 
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (1,'FR','Arabie Saoudite',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (2,'FR','Egypte',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (3,'FR','Émirats arabes unis',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (5,'FR','USA',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (6,'FR','Oman',SYSDATE);

Denormalized table structure.

?
1
2
3
4
5
6
7
CREATE TABLE TIME_ZONE_MASTER (
    TIMEZONE_ID NUMBER(3) NOT NULL,
    TIMEZONE_OFFSET VARCHAR2(10),
    DESCRIPTION NVARCHAR2(100),
    MODIFIED_DATE DATE DEFAULT SYSDATE  NOT NULL,
    PRIMARY KEY (TIMEZONE_ID)
);

Select data set from TIMEZONE_DESCRIPTIONS table to join with TIME_ZONE table. In the target table, language wise descriptions will be stored in the same row according to a predefined format.

?
1
2
3
4
5
6
7
8
9
SELECT  X.TIMEZONE_ID,
    MAX(X.MODIFIED_DATE) AS MODIFIED_DATE,
    'EN:'
    || MAX(DECODE(X.LANGUAGE_ID, 'EN', X.DESCRIPTION))
    || ',FR:'
  || MAX(DECODE(X.LANGUAGE_ID, 'FR', X.DESCRIPTION)) TIME_ZONE_DESC
FROM (
    SELECT TZD.TIMEZONE_ID, TZD.LANGUAGE_ID, TZD.DESCRIPTION,TZD.MODIFIED_DATE FROM TIMEZONE_DESCRIPTIONS TZD
) X GROUP BY X.TIMEZONE_ID;

To get the full select statement, join with TIME_ZONE table using TIMEZONE_ID.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT tz.TIMEZONE_ID,
  tz.TIMEZONE_OFFSET,
  v.TIME_ZONE_DESC,
  GREATEST(v.MODIFIED_DATE,tz.MODIFIED_DATE) AS MODIFIED_DATE
FROM TIME_ZONE tz
LEFT OUTER JOIN
  (SELECT x.TIMEZONE_ID,
    MAX(x.MODIFIED_DATE) AS MODIFIED_DATE,
    'EN:'
    || MAX(DECODE(x.language_id, 'EN', x.DESCRIPTION))
    || ',FR:'
    || MAX(DECODE(x.language_id, 'FR', x.DESCRIPTION)) TIME_ZONE_DESC
  FROM
    (SELECT tzd.TIMEZONE_ID,
        tzd.LANGUAGE_ID,
        tzd.DESCRIPTION,
        tzd.MODIFIED_DATE
    FROM TIMEZONE_DESCRIPTIONS tzd
    ) x
  GROUP BY x.TIMEZONE_ID
  ) v
ON (tz.TIMEZONE_ID     = v.TIMEZONE_ID);

Merge statement syntax,

?
1
2
3
4
5
6
7
8
9
10
MERGE INTO TARET_TABLE USING
      (SELECT
    )ON (JOIN)
WHEN MATCHED THEN UPDATE
  SET
WHEN NOT MATCHED THEN
  INSERT
    ()
    VALUES
    ();

Finally, we can create the full merge statement to update TIME_ZONE_MASTER as follows.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
MERGE INTO TIME_ZONE_MASTER t USING
(SELECT tz.TIMEZONE_ID,
  tz.TIMEZONE_OFFSET,
  v.TIME_ZONE_DESC,
  GREATEST(v.MODIFIED_DATE,tz.MODIFIED_DATE) AS MODIFIED_DATE
FROM TIME_ZONE tz
LEFT OUTER JOIN
  (SELECT x.TIMEZONE_ID,
    MAX(x.MODIFIED_DATE) AS MODIFIED_DATE,
    'EN:'
    || MAX(DECODE(x.language_id, 'EN', x.DESCRIPTION))
    || ',FR:'
    || MAX(DECODE(x.language_id, 'FR', x.DESCRIPTION)) TIME_ZONE_DESC
  FROM
    (SELECT tzd.TIMEZONE_ID,
      tzd.LANGUAGE_ID,
      tzd.DESCRIPTION,
      tzd.MODIFIED_DATE
    FROM TIMEZONE_DESCRIPTIONS tzd
    ) x
  GROUP BY x.TIMEZONE_ID
  ) v
ON (tz.TIMEZONE_ID     = v.TIMEZONE_ID)
) td ON (t.TIMEZONE_ID = td.TIMEZONE_ID)
WHEN MATCHED THEN UPDATE
  SET t.DESCRIPTION  = td.TIME_ZONE_DESC,
    t.TIMEZONE_OFFSET   = td.TIMEZONE_OFFSET,
    t.MODIFIED_DATE = td.MODIFIED_DATE
WHEN NOT MATCHED THEN
  INSERT
    (
      TIMEZONE_ID,
      TIMEZONE_OFFSET,
      DESCRIPTION,
      MODIFIED_DATE
    )
    VALUES
    (
      td.TIMEZONE_ID,
      td.TIMEZONE_OFFSET,
      td.TIME_ZONE_DESC,
      td.MODIFIED_DATE
    );

Final outcome would be like this.

at
Subscribe to: Posts (Atom)