Load a single partition into MEMORY

[patrickbachmann]

Hi folks,

I have a very large MSEG table partitioned by material doc YEAR.  When testing a particular view sometimes we will fully load MSEG into memory to ensure fast results.  However usually we are only testing for a specific year so this is overkill.  So my question is rather than right click on the table MSEG and clicking 'LOAD' is there a way to load just a specific YEAR partition into memory?  ie: what I really want to do is just load MSEG into memory where MJAHR = '2012' for example.  Can this be done?

Thanks!

-Patrick

[former_member182114]

Hi Patrick,

LOAD statment have no option for this.

Your partition is by range/hash with Year on key?

Try if a selection by year load only the partition you want:

unload mseg;

select count(*) from (select * from mseg where mjahr='2012')

And check the resuls:

select schema_name,table_name,part_id,loaded from m_cs_tables where table_name = 'MSEG';

Regards, Fernando Da Rós

[patrickbachmann]

Hi Fernando, yes it's a range partition by year MJAHR.   I was indeed wondering if it was that simple (ie: just running a select against it like select * from MSEG where MJAHR='2012' if that would indeed load ALL columns in MSEG for that particular partition).  I will experiment with this.

Thanks

[former_member184768]

Hi Patrick,

Your requirement is pretty standard and just like you, even I wish a more elegant solution from SAP. It is worth to raise a feature request for this. The requirement is in-line with the temperature architecture SAP mentioned in BW on HANA. The partition which contains history data should be "cool" and we should be able to move it out of memory or load as and when required with standard functionality.

Regards,

Ravi

[former_member182114]

Totally agree.

[patrickbachmann]

Guys I tried an experiment;

1) Completely unloaded mseg from memory

2) Ran select * from MSEG where MJAHR = '2012' (This matches partition by MJAHR)

3) I looked at table definition and partition for 2012 only shows 'PARTIALLY' loaded in memory.  Rest of the partitions show loaded = "NO" as expected. 

I'm not sure how to make it FULLY loaded for just the one partition.

-Patrick

[patrickbachmann]

Ahhh I just realized when I run the select via SQL the max records is 1000.  I wonder if that's why it's not fully loading?

[patrickbachmann]

Fernando I am trying your COUNT example next.... will update you in a minute.

[patrickbachmann]

Fernando, for a moment I thought the way you had structured the COUNT would mean it would process everything FULL in memory yet only display the single count row (thus avoiding the 1000 row limit) however unfortunately it still only loaded the partition PARTIALLY.  Interestingly, it looks like the exact same amount of memory consumed as my SELECT * that returned 1000 rows.

[henrique_pinto]

Try the select as fernando mentioned (select count(*) from (select *...)).

Also, make sure there is no delta (i.e. that delta merges have happened before).

[patrickbachmann]

That's exactly what I did.  Although I didn't merge delta (although it looks tiny).  I will do a quick delta merge and retry though.

[henrique_pinto]

I just thought of that because the LOAD statement has a specific option for loading DELTAs.

Check: http://help.sap.com/hana/html/sql_load.html

[patrickbachmann]

Ok I ran delta merge (then took a brief nap while waiting) and then unloaded memory (because after the merge all the partitions were partially loaded) and then reran Fernando's code.  Same exactly result, it got partially loaded in memory.  What I did find interesting, however is that when I first did this count with unloaded memory I had to wait a minute or so.  This time when I ran the count with unloaded memory it was INSTANTANEOUS.  I'm thinking how could that be if I unloaded memory?  Is there cache somewhere else I'm not aware of?

-Patrick

[former_member182114]

Hi Patrick,

Don't know if it's cached the count result but I achieved same load partial on table and also on a column which I was using for counting... The only way to reach a FULL column load was DISTINCT inside table rsss.

The table, unfortunatelly I couldn't reach a FULL load of it, reason is the internal attributes... Tried put it explicity but trex_udiv not came up 😞

select count(*) from (select distinct * from table);

select count(*) from (select distinct *,"$trex_udiv$","$rowid$"  from table);

Instead of anoying status PARTIAL is the result you need as you could and prepare the proper warm up. In fact in a real scenario for big tables like MSEG we in fact don't want to load all columns but some.

Maybe not so far from now DEV team will enhance the LOAD option with an option to set a specific partition.

As you said that you are conducting tests, my suggestion to you is:

- unload mseg;

- run your query once (wait the needeful for automatic load)

- look for loaded columns (don't matter if it was partial or not), you can use:

select * from m_cs_all_columns where table_name='MSEG' and loaded <> 'FALSE';

- prepare your personalized statement to achieve same loading filtering year to load only the target partition

- try avoid distinct as it will move to rowengine, maybe more than one SQL for "parallel load" will be good.

Regards, Fernando Da Rós

[henrique_pinto]

Yes there is a result set cache option, check for resultcache_enabled in the configuration tab of administration view.

But I'm not sure that's the reason...

[patrickbachmann]

Thanks for the great detail Fernando.  I will continue to experiment with your ideas. 

Henrique, I looked at that setting and ours is set to resultsetenabled=NO so I'm baffled why it was so fast the second time.  I will do some more testing with that too and see if I can consistently get it faster the second iteration each time (for curiosity sake)

-Patrick