SQL+JQL is designed for massive users and not for massive data |
For a thousand users working in parallel for their daily work basis, SQL+JQL will perform well.
For many thousands of records, the performance could degrade a lot.
Based on our experience and after having analyzed a lot of reported users' queries, our conclusion is that SQL+JQL is very flexible, and there many alternatives to writing a query and there are great performance differences among them. I.e: we have optimized users' queries taking minutes to take a few seconds only.
Take into consideration that the underlying Jira API is always invoked and this consumes time and resources. A cache has been implemented to reduce the calls to the Jira API as much as possible, however simple queries like:
SELECT COUNT(*) FROM ISSUES WHERE JQL = <JQL query returning thousands of issues>
might take a lot of time to be answered because the ISSUES table must be populated first with all the records (thousands) before count all of them and return a simple number.
The main tricks to write the best performing queries possible are (already explained in sections before):
Rely on Jira JQL queries as much as possible to do the work even building JQL queries in SQL queries dynamically. This is an advanced topic as very few users are used to joining tables in this way and it is critical in many queries for the real world.
Reduce the number of joins as much as possible by using the JQL instead of the ISSUES table. The ISSUES table is very normalized and it requires joining other tables to write a simple useful query. However, the JQL table is designed to avoid joining and extract the data from a special column named ISSUE (which is really a Java object) with built-in functions created specifically in SQL+JQL for that.
The built-in cache guarantees that a method of Atlassian’s APIs is invoked once and only once with the same parameters in the same active connection. That may produce a significant performance gain when the ISSUES table is used. However, with the JQL table, the cache loses its benefits because it spends time writing all the results in the cache before sending them to the user… data that will not be read later again. Once your query is working as expected, run it with the cache disabled and measure the difference between the two execution times to make the right decision.
Type EXPLAIN before you query and run it again and see the execution plan. This is how the query will be resolved by the SQL engine and some tables might have been changed the order from how there were written in design-time to how will be executed in run-time. It may result in very useful to improve your query performance issues.
Finally, if you get stuck with your query reach our support team for help. This always works!