Regardsing Indexes creation and deletion in Cube

[Former Member]

Hi Expert,

As every body knows this is a Basic Question but I am not clear on Indexes.

Normally when we load data before we use to delete indexes and then will re-create indexes for better data load performance.

My Question ...why we have to delete indexes before and after, what will happens internally.

can any give me some clear picture.

Regards

Reddy.

[ravi_chandra3]

Hi

If you create the index during the loading the data , the loading performace will be degraded because whenever a new record is inserted, then it has to generated the index value for each and every record present which will impact the loading performance. If there exists less number of records then loading will not be affected , but if there exits some huge amount of records then the loading will be impacted. As a result it is the best practise to create the index only after the completion of loading of the data.

Regards,

RC

[Former Member]

Hi Redyy,

Indexes are helpfull for performance of report execution. But when you are loading data it has a reverse impact as when you insert data in table it inserts in tables based on available indexes. Hence if your Cube has a indexes then system waste a lot of resources for loading. Hence we delete indexes.

This improves load performance. Now once you loaded data, you need to bring back your report execution performance. To do that you build Index again so that report will execute faster.

I guess this clears your queury.

Regards,

Ganesh

[Former Member]

Data loading is a write operation and report execution is a read operation.

So if indexes exists for the cube while loading data, it needs to search for the indexed record in the database(cube tables) and insert the new record there. This needs additional time to search the already existing indexed record. So, if indexes exists while loading; data loading time will increase.

To avoid this delay, we delete the indexes for cube before loading data.

While we execute the report, it reads the underlying cube data. So the query read operation will be quick when indexes exists for the database tables (cube). This is similar to a book with Index page.

So, we delete indexes to improve the run time of the data load and we create indexes after data load to improve the query run time.

Regards,

Sunil