[jira] [Updated] (TRAFODION-2265) ex_conv_clause::findInstruction() linearly searches convInstrInfo[]

["Selvaganesan Govindarajan (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-2265?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Selvaganesan Govindarajan updated TRAFODION-2265:
-------------------------------------------------
    Assignee: Narendra Goyal

> ex_conv_clause::findInstruction() linearly searches convInstrInfo[]
> -------------------------------------------------------------------
>
>                 Key: TRAFODION-2265
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2265
>             Project: Apache Trafodion
>          Issue Type: Improvement
>          Components: sql-exe
>    Affects Versions: any
>            Reporter: Narendra
>            Assignee: Narendra Goyal
>            Priority: Minor
>              Labels: performance
>   Original Estimate: 48h
>  Remaining Estimate: 48h
>
> 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 -----



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)