Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-5169

Avoid generating useless SQL rollback and commit queries

XMLWordPrintable

    • We collect Jira feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      JIRA generates vast amounts of useless SQL for most operations. For every valid line of SQL, there are:

      • three SET autocommit statements
      • a commit
      • a rollback immediately after

      Typical output looks like:

      1757 Query commit
      1757 Query rollback
      1757 Query SET autocommit=1
      1757 Query SET autocommit=1
      1757 Query SET autocommit=0
      1757 Query SELECT ID, LINKTYPE, SOURCE, DESTINATION, SEQUENCE FROM issuelink WHERE SOURCE=10020
      1757 Query commit
      1757 Query rollback
      1757 Query SET autocommit=1
      1757 Query SET autocommit=1
      1757 Query SET autocommit=0
      1757 Query SELECT SOURCE_NODE_ID, SOURCE_NODE_ENTITY, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE FROM nodeassociation WHERE SOURCE_NODE_ID=10000 AND SOURCE_NODE_ENTITY='Project' AND SINK_NODE_ENTITY='VersionControl' AND ASSOCIATION_TYPE='ProjectVersionControl'
      1757 Query commit
      1757 Query rollback
      1757 Query SET autocommit=1
      1757 Query SET autocommit=1
      1757 Query SET autocommit=0
      1757 Query SELECT ID, issueid, AUTHOR, actiontype, actionlevel, actionbody, CREATED, actionnum FROM jiraaction WHERE issueid=10020 AND actiontype='comment'
      1757 Query commit
      1757 Query rollback
      1757 Query SET autocommit=1
      041109 10:01:28 1757 Query SET autocommit=1
      1757 Query SET autocommit=0

      Creating an issue generates 657 SQL queries, of which 540 are useless (commits, rollbacks, sets). Of the remaining 117, 44 are duplicates of queries made earlier (a separate issue).

      It is hard to tell what effect on performance all this has. One hopes that databases run these statements very quickly. MySQL takes 0.3ms per set of (commit,rollback,3 x set), amounting to 32ms for the insert. The increase in network overhead is probably a lot more significant.

            Unassigned Unassigned
            7ee5c68a815f Jeff Turner
            Votes:
            1 Vote for this issue
            Watchers:
            11 Start watching this issue

              Created:
              Updated:
              Resolved: