How to find the issues with most issue links or comments in the database
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Fisheye および Crucible は除く
目的
It's very common to look for ticket outliers that can impact system performance such as issues with thousands of links or comments that might've been added by faulty automation or simply kept growing overtime. It is not unusual for issues with lots of associated links or comments cause threads to hang or connection to timeout before all the data is fetched.
ソリューション
We recommend testing these queries in your staging environment. They were created on PostgreSQL and may need tuning depending on your database. These will help you to identify issues that can be expensive when fetching their information from the database, so you can review them and potentially take actions to remediate the problem (e.g. deleting or archiving those issues, deleting excessive comments, links or attachments).
Issues with most comments
- Query tested on PostgreSQL, MySQL, and Oracle.
SELECT
count(*),
ji.id,
p.pkey || '-' || ji.issuenum AS issuekey
FROM
jiraaction ja
JOIN jiraissue ji ON ja.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
ji.id,
ji.issuenum,
p.pkey || '-' || ji.issuenum,
p.pname
ORDER BY
count(*)
DESC
LIMIT 50;- Query tested on Microsoft SQL Server.
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
jiraaction ja
JOIN jiraissue ji ON ja.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
ji.id,
ji.issuenum,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(*)
DESC;Issues with most attachments
- Query tested on PostgreSQL, MySQL, and Oracle.
SELECT
issueid,
count(*),
p.pkey || '-' || ji.issuenum AS issuekey
FROM
fileattachment fa
JOIN jiraissue ji ON fa.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
issueid,
ji.issuenum,
p.pkey || '-' || ji.issuenum,
p.pname
ORDER BY
count(issueid)
DESC
LIMIT 50;- Query tested on Microsoft SQL Server.
SELECT TOP 50
issueid,
count(*),
CONCAT(p.pkey,'-',ji.issuenum)
FROM
fileattachment
JOIN jiraissue ji ON fa.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
issueid,
ji.issuenum,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(issueid)
DESC;Issues with most INWARD links
- Query tested on PostgreSQL.
SELECT
count(*),
ji.id,
p.pkey||'-'||ji.issuenum as issuekey
FROM
issuelink il
join jiraissue ji on il.destination=ji.id
join project p on ji.project=p.id
GROUP BY
il.destination,
ji.id,p.pkey||'-'||ji.issuenum,p.pname
ORDER BY
count(*)
DESC
LIMIT 50;- Query tested on Microsoft SQL Server.
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
issuelink il
join jiraissue ji on il.destination=ji.id
join project p on ji.project=p.id
GROUP BY
il.destination,
ji.id,CONCAT(p.pkey,'-',ji.issuenum),p.pname
ORDER BY
count(*)
DESC;Issues with most OUTWARD links
- Query tested on PostgreSQL.
SELECT
count(*),
ji.id,
p.pkey||'-'||ji.issuenum as issuekey
FROM
issuelink il
join jiraissue ji on il.source=ji.id
join project p on ji.project=p.id
GROUP BY
il.source,
ji.id,
p.pkey||'-'||ji.issuenum,
p.pname
ORDER BY
count(*)
DESC
LIMIT 50;- Query tested on Microsoft SQL Server.
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
issuelink il
join jiraissue ji on il.source=ji.id
join project p on ji.project=p.id
GROUP BY
il.source,
ji.id,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(*)
DESC;