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.
- 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 TABLESWHERE 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'