ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Performance of an UPDATE with value from another file

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance of an UPDATE with value from another file

    Am I missing something really obvious here?

    I need to do an update of a field (CODE) in the customer file, based on a value in another table (SEGMENT). There is not an entry in this table for every customer.

    Code:
    update  CUSTFILE_file A
    set CODE = (select CODE from SEGMENT B
                    where B.CUSTNO=A.CUSTNO)
    where SOCI='03' and exists (select * from SEGMENT D
                       where D.CUSTNO=A.CUSTNO)
    I had created an SQL index on CUSTNO for the table SEGMENT before running the UPDATE and the Customer file has a LF keyed on SOCI/CUSTNO.

    When I ran this update in interactive SQL, after about 2 minutes it had only done a couple of hundred updates. For 600,000 customers, there are about 300,000 with an entry in SEGMENT. So hours of processing ! I killed it.

    A quick few lines of RPG later and the program ran in about 2 minutes for the whole file.

    Thanks Andrew


  • #2
    The issue with using an update statement as shown, is that WHERE EXISTS has extremely poor performance as noticed by OP.

    This is where the MERGE statement really shines because the query engine can do what it does best and utilize the indexing on both files. This can be proven in Visual Explain.

    Code:
    merge into CUSTFILE  as a
    using
    (select CUSTNO, CODE from SEGMENT as a Join SEGMENT as b On a.CUSTNO = a.CUSTNO Where SOCI = '03') as b
    on a.CUSTNO = b.CUSTNO
    When matched then
    Update Set a.CODE = b.CODE
    **Assumes that the following indexes exist: SEGMENT keyed by CUSTNO and CUSTFILE keyed by CUSTNO/SOCI. (keyed fields must be in this order.)

    First, a standard full-select statement is processed building a recordset which normally is a smaller subset than the file being updated. The query engine can use the indexes over CUSTFILE and SEGMENT during the creation of this recordset because both the join and the where predicate match the existing index. (CUSTFILE - LF keyed on CUSTNO/SOCI and SEGMENT - LF keyed on CUSTNO) Next, the MERGE statement compares that recordset to the Using full-select. The query engine can use the index over CUSTFILE (using only the first keyed field - CUSTNO) to probe the index for only the customer numbers in the smaller recordset.

    The update statement cannot use the full keyed fields in the indexes defined. Only the first keyed field (SOCI) is used in the update statement because only the field SOCI is compared to a literal expression. So, the query engine will start at the bottom of records equal to SOCI = '03' and for each record, it has to execute the EXIST subquery.

    Imagine if there are 10 records matching on CUSTNO in SEGMENT where SOCI = '03' but there are 100,000 CUSTFILE records with SOCI = '03', in the update statement all 100,000 records are read and for each record, it has to execute the EXIST subquery. In the MERGE statement execution only the 10 records in SEGMENT where SOCI = '03' are probed against the CUSTFILE index.

    Jim

    Comment


    • #3
      Thanks a lot Jim for your detailed explication.

      Unfortunately apparently MERGE is only available from V7R1 and this machine is only on V5R4. But I have access to another machine with V7R1 so I'll certainly do a few tests.

      Think maybe the "select" in your code should read

      Code:
      select CUSTNO, CODE from[B] CUSTFILE [/B]as a Join SEGMENT as b On a.CUSTNO = b.CUSTNO Where SOCI = '03'
      Andrew

      Comment


      • #4
        Also use index advisor too - there could be a benefit to creating an index on SEGMENT on column SOCI

        Comment


        • #5
          Cheers Rocky for the reply but column SOCI (which is a Division No.) exists only in CUSTFILE, not in SEGMENT.

          I had to specify it in my UPDATE as (in theory) a Customer could exist in different divisions and, as I said in my initial post, there is a LF over CUSTFILE with the keys SOCI/CUSTNO.

          Andrew

          Comment


          • #6
            Well - it was a thought... LOL

            Comment

            Working...
            X