Inconsistent Table Load Times

[raymond_lackey]

SAP IQ Version 16.0, sp11.06 Running On LINUX in simplex mode.

IQ_Main 60% of 2.8 Tb.

Table in question about 360 Gb.

Using Load Table, loading 5.8-5.9 million rows per day to a table with 9 billion plus rows.

No Primary key, but 32 columns, NBit, 21 HG tiered indexes.

Majority of columns are integer, remainder char, nothing greater than 20 chars.

Fresh daily load requires 35-45 minutes, client wanted to know if I could speed this up, so now it gets interesting...

If we delete all the rows from the daily load, commit, then re-run the daily load, requires less than 1 minute??

This is a behaviour I've never noticed, probably because in 25 years of IQ never had the opportunity to test it. A load was a load and you lived with it pretty much unless you needed to juggle configs.

Then I re-tested, same data, this time cycled the server before test, same result, load runs < one minute.

Ran a second test, new data, load time back up to 35-45 minutes.

Question is: Is IQ caching pages for the table based on the deleted rows??

All dbspaces show as pre-allocated as they should...

Has anyone else noticed this behaviour?

Raymond

[markmumy]

Hey Raymond!

Are you loading the same data over and over? Or is it new data? Trying to determine if the data is going into the HGs something like 1 row per page.

Climbing up on the soap box...

Have you tried this on IQ 16.1? IQ 16.0 is no longer supported, exited support Dec 2020.

Why so many HG indexes? See some of my blogs on indexes in IQ 16.1. More often than not, I am finding that we stick to the traditional over-index IQ because that's what we've always done. We've made a lot of changes to IQ default indexes and default storage in 16.1 so that we can get away from needing indexes on everything.

IQ 16 and HDL Index Strategy

OK, off it now...

You may also want to check to make sure that IQ is getting enough threads for the load.

And see if you can determine if the slowdown is in pass 1 or pass 2. Try your load tests without any HG indexes. Does it still exhibit the slowdown? Then it's a pass 1 issue. If not, then it is the merging of data for the HG indexes in pass 2.

Mark

[raymond_lackey]

Hey Mark,

Thanks for the quick response!

Load test pattern example:

Step 1: Load Apr 20 data, new -> 35-45 minutes

Step 2: delete Apr 20 data,

Step 3: reload Apr 20, < 1 minute

Step 4: Load Apr 21 data, new -> 35-45 minutes

definitely pass 2 is the issue, pass 1 completes 5.8 million rows in 10 seconds or less.

No threading issues.

So it seems the HG pages are retained after step 2 and only pass 1 data pages are loaded in step 3

very smart if that is how it works!

I hear ya re: version and HG indexes, it was painful enough getting the client to drop LF and HNG's. Also engaged in updating ASE 15.7 CE to something on the support chain.

Back to IQ, there does not seem to be any magic bullet, it is what it is?

I'll emphasize updating their dev platform to IQ 16 at a minimum.

Raymond

[markmumy]

Are you sure that all HG indexes are tiered?

Here is some piece of code that works in HANA Cloud Data Lake IQ and in IQ 16.1. Run this and check the "tiered" column. Should all be Y.

create or replace procedure sp_iqshowtiered()
begin
  declare tiered char(1);
  select rtrim(table_name) as 'table_name',rtrim(table_owner) as 'table_owner',rtrim(index_name) as 'index_name',rtrim(column_name) as 'column_name',index_type,unique_index,' ' as tiered_index
    into #tier_temp
    from sp_iqindex()
    where index_type = 'HG' and lower( index_name ) not like '%hotsql%'
    and lower( table_name ) not like '%hotsql%'
    and lower( table_name ) not like 't_pwd%';
commit;
--create indexes to avoid index advisor
--create unique HG index tier_HG on #tier_temp(table_name, table_owner, index_name);
create HG index index_name_HG on #tier_temp(index_name);
create HG index table_name_HG on #tier_temp(table_name);
create LF index table_owner_LF on #tier_temp(table_owner);
  for FORLOOP as FORCRSR dynamic scroll cursor for
    select table_name,table_owner,index_name from #tier_temp order by table_name asc,table_owner asc,index_name asc
  do
    execute immediate 'select (case when substring(value2,1,1)=''N'' then ''Y'' else ''N'' end) into tiered from sp_iqindexmetadata (''' || index_name || ''',''' || table_name || ''',''' || table_owner || ''') where value1 = ''Maintains Exact Distinct'';';
    execute immediate 'update #tier_temp set tiered_index = tiered where table_name=''' || table_name || ''' and table_owner=''' || table_owner || ''' and index_name=''' || index_name || ''';'
  end for;
  --select * from #tier_temp where tiered in ('Y','y');
  select table_owner, table_name, index_name, index_type, tiered_index  from #tier_temp ;
end;