Archive for July, 2009

Testlink Database SQL query

July 27, 2009 2 comments

Objective # 1: Filter all testcases under Globe Portal Capabilities testplan (testplan_id = ‘9007’) and include summary, steps and expected result in the query


field description
field description
id internal id for testcase versioning id unique id per testcase
testplan_id id that determines the testplan tc_external_id external id
tcversion_id unique id per testcase version latest version
node_order order of the node summary summary of testcase
urgency degree of urgency steps steps of testcase
expected_results expected result
importance degree of importance
author_id author
creation_ts date of creation
updater_id updater
modification_ts date of modification
active is active?
is_open is open?
execution_type type of execution 1-manual;2-automated

SQL query:

SELECT * FROM testplan_tcversions LEFT JOIN tcversions ON = testplan_tcversions.tcversion_id

WHERE testplan_tcversions.testplan_id = '9007'

Problem: Testcases have does not indicate its component/testsuite (Contacts, Search, Widgets etc.)

Objective # 2: Include Component(from nodes_hierarchy table) for each testcase in the query

nodes_hierarchy node_types
field description
field description
id unique id per testcase id id of node
name description / name description description of node
parent_id id of the parent node
node_type type of node node_types table
id description
1 testproject
2 testsuite=Component
3 testcase=Testcase Summary
4 testcase_version=Testcase Steps
5 testplan
6 requirement_spec
7 requirement

SQL query:

SELECT a.*,  b.*, c.parent_id, c.node_type FROM testplan_tcversions a LEFT JOIN tcversions b ON = a.tcversion_id LEFT JOIN nodes_hierarchy c ON a.tcversion_id =

WHERE testplan_tcversions.testplan_id = '9007'

Problem: parent_id for testcases with node_type = ‘4’ is not sufficient to get the testcase component/testsuite


id parent_id node_type description value
9068 9067 4 testcase steps < steps >
9067 9010 3 testcase summary User can view his Contacts
9010 9006 2 testsuite/component Personalizations: Contacts
9006 1 testproject Gportal Capabilities

In our result query for id = ‘9068’, ‘9067’ will only give us the summary/title of the testcase but not its component/testsuite , our next objective is to get the parent_id of ‘9067’

Objective # 3: Include the parent_id of  each parent_id for all the testcases in the query. Just like the following

id parent_id parent_id2 name
9068 9067 9010 Personalizations: Contacts

To get the parent_id for each parent_id from the nodes_hierarchy table, we create a subquery:

SELECT,, x.node_type_id, x.parent_id, y.parent_id as parent_id2

FROM nodes_hierarchy x left join nodes_hierarchy y ON x.parent_id =

Then add our subquery to our major query in Objective # 1:

SELECT a.*, b.*, d.*
FROM testplan_tcversions a
LEFT JOIN tcversions b on a.tcversion_id =
LEFT JOIN (<em>select,, x.node_type_id, x.parent_id, y.parent_id as parent_id2 from nodes_hierarchy x left join nodes_hierarchy y on x.parent_id =</em>) d on a.tcversion_id =
WHERE a.testplan_id = '9007'

😀 Hats off Kuya Ed!

Categories: database Tags: , , , , ,

Understanding Performance Test and its Metrics

July 20, 2009 3 comments

There are a lot of definition that you could draw out of the concept “Performance testing,” one of these, which I found brief and simple:

Performance testing is the process by which software is tested and tuned with the intent of realizing the required performance.

Regardless of the many terms that you could relate to “Performance,” like load, stress, spike, soak etc. There are three major categories that you should focus on when you do performance test:

Speed — Does the application respond quickly enough for the intended users?

Scalability — Will the application handle the expected user load and beyond?

Stability — Is the application stable under expected and unexpected user loads?

And in order for you to objectively measure the above categories, you need to carefully identify the suitable performance metrics to be used. To give you an overview of performance metrics, here are some of useful information from RadView Software’s White Paper:  Test Metrics – Which Are Most Valuable?

During a test session, virtual clients generate result data (metrics) as they run scenarios against an application. These metrics determine the application’s performance, and provide specific information on system errors and individual functions. Understanding these different metrics will enable you to match them to the application function and build a more streamlined test plan.

Scalability and Performance

1. Hits per Second

– a Hit is a request of any kind made from the virtual client to the application being tested. The higher the Hits Per Second, the more requests the application is handling per second. A virtual client can request an HTML page, image, file, etc.

2. Pages per Second

– measures the number of pages requested from the application per second. The higher the Page Per Second the more work the application is doing per second.

3. Throughput

– this is an important baseline metric and is often used to check that the application and its server connection is working.  Throughput measures the average number of bytes per second transmitted from the application being tested to the virtual clients running the test agenda during a specific reporting interval.  This metric is the response data size (sum) divided by the number of seconds in the reporting interval.

4. Rounds

– tells you the total number of times the test agenda was executed versus the total number of times the virtual clients attempted to execute the Agenda. The more times the agenda is executed, the more work is done by the test and the application.

Responses and Availability

1. Hit Time

– hit time is the average time in seconds it took to successfully retrieve an element of any kind (image, HTML, etc).  The time of a hit is the sum of the Connect Time, Send Time, Response Time and Process Time. It represents the responsiveness or performance of the application to the end user.

2. Time to First Byte

– this measurement is important because end users often consider a site malfunctioning if it does not respond fast enough.  Time to First Byte measures the number of seconds it takes a request to return its first byte of data to the test software’s Load Generator.

3. Page Time

– page time calculates the average time in seconds it takes to successfully retrieve a page with all of its content.  This statistic is similar to Hit Time but relates only to pages. In most cases this is a better statistic to work with because it deals with the true dynamics of the application.

With regards to choosing the performance metric, you should always consider the type of application that you’re testing. Say for an open and public web application where you expect that many concurrent users will hit the system at the same time, HIT PER SECOND would be a valuable metric to use, compared to an in-house application like accounting system where you could explicitly tell how many client will be using the application, HITS PER SECOND would be irrelevant.

Jmeter: Monitor Results

July 17, 2009 3 comments

Monitor Results is one of the many listeners that Jmeter offers free at its best.
This is a reporting tool that allows us to view the status of our server while the test is being executed.
But note that this listener works only for Tomcat5 or newer versions.

Once you have created/recorded your scripts:

1. Add Listener>Monitor Results
2. Add Sampler>HTTP Request and placed it before all your other samplers
3. Change the Name field to “Server Status”.
4. Enter the IP address or Hostname
5. Enter the port number
6. Set the Path field to “/manager/status” if you’re using Tomcat.
7. Add a request parameter named “XML” in uppercase. Give it a value of “true” in lowercase.
8. Check “Use as Monitor” at the bottom of the sample.
9. Add Config element>HTTP Authorization Manager
10. In the HTTP Authorization Manager, leave the base URL blank
11. Enter your web server’s login(username and password)

These should make your Monitor Results works and allows you to view the Health, Load, Memory and Thread lines as you run your load test. FTW!