Secondary Indexes

[rashmi_purohit2]

Hi experts,

I need to have an opinion on the following for performance tuning.

Actually a report was taking long time fetching entries from the tables, so secondary index are created for BSAD, BSID, BSAK and BSIK tables, and reduced the time from 8 hours to 2 hours by this secondary index.

Now becuase of this index, the other processes are getting hampered like a job in BW, uses the same tables. earlier it used to run in 5-10 mins it is now taking around 45-50 mins to run sometimes 2 hours to run.

So how can we improve without hampering any processes?

Thanks,

Rashmi

[former_member192616]

Hi Rashmi,

Hi experts,
> 
> I need to have an opinion on the following for performance tuning.
> 
> Actually a report was taking long time fetching entries from the tables, so secondary index are created for BSAD, BSID, BSAK and BSIK tables, and reduced the time from 8 hours to 2 hours by this secondary index.
> 
> Now becuase of this index, the other processes are getting hampered like a job in BW, uses the same tables. earlier it used to run in 5-10 mins it is now taking around 45-50 mins to run sometimes 2 hours to run. 
> 
> So how can we improve without hampering any processes?
> 
> Thanks,
> Rashmi

Good question! Creating a new index bears always the risk to interfere with other statements applications. There is no way around

analyzing the important (frequently executed) sql statements and come up with an index design that fits for all of them.

You have to analyze the sql statements that are slow now in more detail.

In order to answer your question all details (sqls indexes statisitcs execution plans ...) would be needed.

There is no easy answer to your question.

Kind regards,

Hermann

[volker_borowski2]

Hi,

for the time-based delta extraction, there is a set of preconfigured indexes on these BSIS/BSIK/BSAS/BSAK/BSAD/BSID tables.

Usually it is the index 1 on those.

These are delivered as a "no database index" because if you have no BW, you won't need them.

You can simply activate them and see if this helps for your BW.

If not, revert.

Volker