Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-11395

Use left join to test variable value conditions in historic process instance query

    XMLWordPrintable

Details

    Description

      Currently, a query like historyService.createHistoricProcessInstanceQuery().variableValueEquals("foo", "a").list(); creates the following SQL:

      SELECT DISTINCT RES.*
FROM (
	SELECT SELF.*
		,DEF.NAME_
		,DEF.VERSION_
	FROM ACT_HI_PROCINST SELF
	LEFT JOIN ACT_RE_PROCDEF DEF ON SELF.PROC_DEF_ID_ = DEF.ID_
	WHERE (
			1 = 1
			AND EXISTS (
				SELECT ID_
				FROM ACT_HI_VARINST
				WHERE NAME_ = ?
					AND PROC_INST_ID_ = SELF.PROC_INST_ID_
					AND (
						(
							VAR_TYPE_ IS NOT NULL
							AND VAR_TYPE_ = ?
							AND TEXT_ IS NOT NULL
							AND TEXT_ = ?
							)
						)
				)
			)
	) RES
ORDER BY RES.ID_ ASC LIMIT ? OFFSET ?

      The EXISTS part is a dependent subquery, i.e. a query that references a value of the outer result set. Such queries are slow on MariaDB and MySQL, because these systems are not able to rewrite this to a join.

      Proposed solution:

      • Rewrite the exists to a JOIN, probably LEFT JOIN

      Implementation notes:

      • variableValueEquals can be used multiple times in succession. We need to join ACT_HI_VARINST once for each of the conditions. Joining only once is not sufficient.
      • This also applies when using or queries, i.e. we must join once for any variable condition, be it in an or query or in the "main" query
      • This should be done in the core API as well as the webapp backend

      mgm-controller-panel

        This is the controller panel for Smart Panels app

        Attachments

          Issue Links

            is related to

            Bug Report - A problem which impairs or prevents the functions of the product. CAM-11179 Task query 'withoutCandidateGroups' and 'withoutCandidateUsers' slow on Postgres

            • L3 - Default - Minor issue or issue with existing workaround, feature requests and questions
            • Closed

            Activity

              People

                Unassigned Unassigned
                thorben.lindhauer Thorben Lindhauer
                Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  Salesforce