[TRAFODION-2748] Slower upsert performance when source is a Hive scan

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.3-incubating
• Fix Version/s: 1.3-incubating
• Component/s: sql-cmp
• Assignee: Suresh Subbiah
• Reporter: Suresh Subbiah
• Created: 18/Sep/17 16:11
• Updated: 15/Sep/18 07:32
• Resolved: 02/Oct/17 15:35

Description

From Eric Owhadi.

In the following set of steps, the cqd GEN_DP2I_SIZE_DOWN should not have been necessary. The quesize should be determined by rowset size. However we were not flagging Hive scans as returning multiple rows in OltOptInfo class in the generator. This cause the initial queue size to be set too low. The fix could help improve performance for other query types with Hive scan too.

cqd GEN_DP2I_SIZE_DOWN '1024';
cqd UPD_ORDERED 'OFF';
cqd HBASE_ROWSET_VSBB_SIZE '20480';

cqd allow_nullable_unique_key_constraint 'ON';
CREATE TABLE STORE_SALES5(
ss_sold_date_sk int,
ss_sold_time_sk int,
ss_item_sk int,
ss_customer_sk int,
ss_cdemo_sk int,
ss_hdemo_sk int,
ss_addr_sk int,
ss_store_sk int,
ss_promo_sk int,
ss_ticket_number int,
ss_quantity int,
ss_wholesale_cost real,
ss_list_price real,
ss_sales_price real,
ss_ext_discount_amt real,
ss_ext_sales_price real,
ss_ext_wholesale_cost real,
ss_ext_list_price real,
ss_ext_tax real,
ss_coupon_amt real,
ss_net_paid real,
ss_net_paid_inc_tax real,
ss_net_profit real)
store by (ss_sold_date_sk, ss_sold_time_sk)
Salt using 96 partitions on (ss_sold_date_sk, ss_sold_time_sk)
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1400000000',
MAX_FILESIZE = '107374182400',
BLOOMFILTER = 'NONE',
BLOCKSIZE = '524280'

)
;

alter 'TRAF_1500000:TRAFODION.ERIC.STORE_SALES5', CONFIGURATION =>

{'hbase.hstore.compaction.max.size' => '4000000000'}

prepare s from
upsert using load into store_sales5 select * from hive.tpcds_sf10000.store_sales_ext;