HANA systems allows
us to mark column store tables for preloading. Tables marked for preloading
will be loaded into tables after index server restart. Tables can be selected
for fully or partially loading.
By default column
store tables utilizes a lazy loading process, that means HANA will only bring
the table into memory if some queries are executed for those tables. Pre-loading do not wait for the query to be
executed against the table or columns , it will directly load the table and
hence the users will get immediate and faster response.
Definitely
preloading helps in query response time but it has side effects too, if we
select more tables for preloading then the restart time of HANA system will
increase. Therefor we have to be very selective about the tables preloading
marking.
In this document we
will learn, how we can find out the tables/columns marked for pre-loading in an SAP HANA system.
We will refer the system views provided by SAP : TABLES and TABLES_COLUMS.
Requirement: You
want to list all tables that are marked for preloading.
Prerequisite: You need a database user with DATA ADMIN or
CATALOG READ system privilege.
Steps:
- Connect to HANA system and open SQL console in HANA studio
- Execute following SQL
SELECT SCHEMA_NAME ,
TABLE_NAME ,IS_PRELOAD,IS_PARTIAL_PRELOAD
FROM TABLES
WHERE IS_PRELOAD = 'TRUE' OR IS_PARTIAL_PRELOAD= 'TRUE'
As per above screenshot , tables yearlySales in NAFTA schema is marked for preloading, let's check which column of this tables are marked for preloading.
Select SCHEMA_NAME , TABLE_NAME ,COLUMN_NAME,PRELOAD from TABLE_COLUMNS WHERE SCHEMA_NAME = 'NAFTA'
Reference: