Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-2265

ex_conv_clause::findInstruction() linearly searches convInstrInfo[]

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: any
    • Fix Version/s: None
    • Component/s: sql-exe
    • Labels:

      Description

      There are cases when the ex_conv_clause::findInstruction() is called to get the index into the convInstrInfo[] array. The method then linearly searches the array (currently 402 elements). E.g., the following upsert statement (to insert 1 million rows), findInstruction() was called ~13 million times.

      Propose going through the convInstrInfo array at process startup and create an index, then use that to avoid the linear search.

      ------ Begin example statement -----

      drop schema hbase_table;
      create schema hbase_table;
      set schema hbase_table;

      create table source1 (a int not null primary key) salt using 8 partitions ;
      insert into source1 select x1 from (values(1)) as t1 transpose 0,1,2,3,4,5,6,7,8,9 as x1;

      create table source2 (a char(128) not null primary key) salt using 8 partitions ;
      insert into source2 values('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvw\
      xyzabcdefghijklmnopqrstuvwx');

      drop table t106;

      create table t106
      (uniq int not null,
      c100k int,
      c10K int ,
      c1K int,
      c100 int,
      c10 int,
      c1 int,
      char_col1 char(128),
      char_col2 char(128),
      char_col3 char(128),
      char_col4 char(128),
      char_col5 char(128),
      char_col6 char(128),
      char_col7 char(128),
      char_col8 char(128),
      primary key (uniq)
      )
      salt using 8 partitions;
      ;

      cqd parallel_num_esps '1';
      cqd upd_ordered 'off' ;

      prepare s1 from
      upsert using load into t106
      select
      0 + (1000000 * x1000000) + (100000 * x100000) + (10000 * x10000) + (1000 * x1000)
      + (100 * x100) +( 10 * x10) + (1 * x1),
      0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100)
      + (10 * x10) +( 1 * x1),
      0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
      0 + (100 * x100) + (10 * x10) + (1 * x1),
      0 + (10 * x10) + (1 * x1),
      0 + (1 * x1),
      0 ,
      x2 ,
      x2 ,
      x2 ,
      x2 ,
      x2 ,
      x2 ,
      x2 ,
      x2
      from (select a from source1) as s1(x1), (select a from source2) as s2(x2)
      transpose 0 as x1000000
      transpose 0,1,2,3,4,5,6,7,8,9 as x100000
      transpose 0,1,2,3,4,5,6,7,8,9 as x10000
      transpose 0,1,2,3,4,5,6,7,8,9 as x1000
      transpose 0,1,2,3,4,5,6,7,8,9 as x100
      transpose 0,1,2,3,4,5,6,7,8,9 as x10
      ;

      execute s1;

      ------ End example -----

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                narendra Narendra Goyal
                Reporter:
                NarendraGoyal Narendra
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - 48h
                  48h
                  Remaining:
                  Remaining Estimate - 48h
                  48h
                  Logged:
                  Time Spent - Not Specified
                  Not Specified