Uploaded image for project: 'Syncope'
  1. Syncope
  2. SYNCOPE-1502

Find Anys using FIQL: SQL improvements

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.5
    • Fix Version/s: 2.1.6, 3.0.0
    • Component/s: core
    • Labels:
      None

      Description

      PR contains improvements when Anys are searched using FIQL queries:

      The resulted SQL query that finds any_to_keys can have a huge list with OR clauses for effective realms. This can be replaced to the IN clause.

      
      SELECT u.any_id,sv.username FROM (SELECT DISTINCT any_id FROM user_search WHERE (realm_id=? AND any_id IN ( SELECT DISTINCT any_id FROM user_search WHERE lastChangeDate<=?))) u,user_search sv WHERE u.any_id=sv.any_id AND u.any_id IN (SELECT any_id FROM user_search WHERE realm_id IN (SELECT id AS realm_id FROM Realm WHERE id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=?)) ORDER BY sv.username ASC
      
      

      When anys are searched by keys a lot of single sql queries are executed. This can be improved using single SQL query with In clause.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                ilgrosso Francesco Chicchiriccò
                Reporter:
                DmitriyB. Dmitriy B.
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 10m
                  10m