Derby
  1. Derby
  2. DERBY-4007

Optimization of IN with nested SELECT

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.4.2.0
    • Fix Version/s: None
    • Component/s: SQL
    • Environment:
      Linux
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Performance

      Description

      The problem is with the following query:

      UPDATE summa_records SET base='foobar' WHERE id IN ( SELECT parentId FROM summa_relations WHERE childId='horizon_2615441');

      It takes in the order of 30s to run when we expect something in the order of 1-2ms.

      We have a setup with two tables

      summa_records: 1,5M rows
      summa_relations: ~350000 rows

      summa_records have and 'id' column that is also indexed and is the primary key. The summa_relations table holds mappings between different ids.

      In our case the nested SELECT produces 2 hits, say, 'foo' and 'bar'. So the UPDATE on these two hits should be quite snappy. If we run the SELECT alone it runs in an instant, and also if we run with hardcoded ids for the IN clause:

      UPDATE summa_records SET base='foobar' WHERE id IN ('foo', 'bar');

      We have instant execution. I'll attach a query plan in a sec.

      1. derby.log
        4 kB
        Mikkel Kamstrup Erlandsen
      2. dblook.log
        1.0 kB
        Mikkel Kamstrup Erlandsen
      3. derby_p_index.log
        5 kB
        Mikkel Kamstrup Erlandsen
      4. dblook_p_index.log
        1.0 kB
        Mikkel Kamstrup Erlandsen
      5. CreateDatabase4007.java
        1 kB
        Knut Anders Hatlen

        Activity

        Mikkel Kamstrup Erlandsen created issue -
        Mikkel Kamstrup Erlandsen made changes -
        Field Original Value New Value
        Attachment derby.log [ 12397432 ]
        Mikkel Kamstrup Erlandsen made changes -
        Affects Version/s 10.4.2.0 [ 12313345 ]
        Affects Version/s 10.3.1.4 [ 12312590 ]
        Mikkel Kamstrup Erlandsen made changes -
        Attachment dblook.log [ 12397781 ]
        Mikkel Kamstrup Erlandsen made changes -
        Attachment derby_p_index.log [ 12397787 ]
        Mikkel Kamstrup Erlandsen made changes -
        Attachment dblook_p_index.log [ 12397788 ]
        Dag H. Wanvik made changes -
        Component/s Performance [ 11709 ]
        Dag H. Wanvik made changes -
        Derby Categories [Performance]
        Dag H. Wanvik made changes -
        Component/s SQL [ 11408 ]
        Knut Anders Hatlen made changes -
        Attachment CreateDatabase4007.java [ 12412490 ]
        Knut Anders Hatlen made changes -
        Issue Type Bug [ 1 ] Improvement [ 4 ]
        Urgency Normal
        Issue & fix info [Repro attached]
        Mamta A. Satoor made changes -
        Labels derby_triage10_10
        Gavin made changes -
        Workflow jira [ 12449762 ] Default workflow, editable Closed status [ 12796749 ]

          People

          • Assignee:
            Unassigned
            Reporter:
            Mikkel Kamstrup Erlandsen
          • Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development