Uploaded image for project: 'Artifactory Binary Repository'
  1. Artifactory Binary Repository
  2. RTFACT-27335

Database taking too long to query in the UI "builds" tab

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Resolution: Unresolved
    • Affects Version/s: 7.24.1
    • Fix Version/s: None
    • Component/s: AQL, Web REST API
    • Labels:
      None
    • Environment:

      Description

      Problem description:
      When the API endpoint "api/artifactbuilds" is triggered by viewing the builds tab for an artifact in the tree browser, the query can take a long time depending on how big the build/build_modules/build_dependencies tables are.

      select distinct b.ci_url as buildUrl,b.build_name as buildName,b.build_number as buildNumber,b.build_date as buildStarted,b.repo as buildRepo,b.created as buildCreated,b.created_by
      as buildCreatedBy,b.modified as buildModified,b.modified_by as buildModifiedBy,bd.dependency_scopes as buildDependencyScope,bm.module_name_id as moduleName from builds b
      left outer join build_modules bm on bm.build_id = b.build_id left outer join build_dependencies bd on bd.module_id = bm.module_id where (bd.sha1 = :1 or bd.md5 = :2 )

      Attached below will be the customer’s Explain Plan output and table stats.

       

      What is the impact to the customer - What are they trying to achieve?
      This query be faster as it is taking quite a while for it to return the output. In addition, it needs full scan 3 BIG tables with hash join based on the current code; it consumes 33GB temp space, and 142GB IOs.

      What is the expected behavior?
      The query should be refactored to be more efficient, and should not take a long time to return the output. Changing the 2 left outer joins to inner joins may help.

       

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            johnsonn Johnson Nguyen
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: