Page Comparison

...

More about performance

Despite the Atlassian's API performance is good, fetching data and checking permissions might require some fractions of a millisecond. This might look like very few time, but when you are dealing with thousand of records, this fractions of time becomes noticeable and the query might require some seconds to be performed.

The ISSUES table has more than 20 columns that are always populated for each record, regardless they are read or not. This requires fractions of milliseconds, but remember that those small portions of time have a significant impact dealing with thousand of issues.

2 (milliseconds / issue) x 1000 (issues) = 2 (seconds)! 

Furthermore, the database model is strongly normalized. Therefore it is required joining a lot of tables to write useful queries. These joined tables also invoke the Atlassian's API to fetch issues and their data summarizing more and more fractions of milliseconds. As result, you might get useful (it works) but annoying (it takes a very long time) query.

Example:

SELECT
 p.name as "Project",
 i.key as "Issue",
 cfv.value  as "Custom field value"
FROM
 ISSUES i
   JOIN
 ISSUECUSTOMFIELDVALUES cfv ON cfv.issueid = i.id AND cfv.customfieldid = 10000   //(2)
   JOIN
 PROJECTS p ON p.id = i.projectid   //(3)
WHERE
 i.JQL = ' reporter = currentUser() '   //(1)

The SQL+JQL query above will:

1. Perform the JQL query. Imagine it returns 3000 issues.
2. for each issue in (1) the value of the custom field with id = 10000 will be fetched by invoking the Jira API.
3. for each issue in (1) its project object will be fetched from the API to get the project name.

Therefore, the Jira API will be invoked:

1 time to perform the JQL (~0.5 seconds)

3000 times to get the custom field values (~ 5 seconds). It requires build the issue Java object again for each id (which as done in (1) and the ask for their custom fields values).

3000 times to get the project name (~ 2.5 seconds). Just the same above here.

TOTAL ~ 8 seconds to be performed.

Too much slow? The API has been invoked ~ 6000 times, therefore it took  8000 ms / 6000 call = 1.3 ms per cal. It is not a bad performance, but fractions of milliseconds are important when dealing with thousands of records and join.

Now, have a look at the equivalent query using the JQL table

SELECT
 PROJECTNAME(JQL.ISSUE) "Project",
 KEY(JQL.ISSUE) as "Issue", 
 CF(JQL.ISSUE, 10000)  as "Custom field value" //(3)
FROM
 JQL //(2)
WHERE
 JQL.QUERY = ' reporter = currentUser() ' //(1)

The query above will take about 1.5 seconds only to be performed: 5 times faster compared with the previous using the ISSUES table.

What did it change?

1. The JQL string is just the same: ' reporter = currentUser() '
2. No joins. Only one table is invoked: it reduces the number of combinations performed by the database engine.
3. the KEY() and CF() auxiliary functions extract the data from the JQL.ISSUE (Issue Java object). These objects have a lot of cached data about the issue, therefore fetching columns is very effective as the Jira API does not have to be invoked.

The metrics above have been provided by an organization with about one million issues in their Jira instance.