script to discover what index create to speed up check of foreign keys.

[Former Member]

Hi,

Exists any script to run in maxdb to discover discover what index create to speed up check of foreign keys?

i have an idea how to create that based in how many rows a table have, then create an index to speed check of foreign keys violations, but i wish to know if MaxDB experts have a better idea about that.

ps: all my FK points to PK, but i noticed if i delete a row that many other tables have FK to this table, that sometimes causes lock table when checking FK, for example:

Order points to Invoice, both tables are big, if i dont have and index for that FK when i delete an invoice, i must wait maxdb check if any Order points back to that invoice, and in this time i get an table lock on Order, because maxdb must check every row of Order.

thanks for any tip.

best regards.

Clóvis

[lbreddemann · Accepted solution]

Hi Clovis!

> Exists any script to run in maxdb to discover discover what index create to speed up check of foreign keys?

Nope - there is no such script.

> i have an idea how to create that based in how many rows a table have, then create an index to speed check of foreign keys violations, but i wish to know if MaxDB experts have a better idea about that.

Hmm.. I'm wondering why you just put a single index on all FK-fields of the referring table, so that the inverse lookup can be done quickly.

> ps: all my FK points to PK, but i noticed if i delete a row that many other tables have FK to this table, that sometimes causes lock table when checking FK, for example:

Hmm... it really shouldn't lock the whole table, but just the rows it checks for that moment.

Have you checked whether there was a lock escalation happening?

> Order points to Invoice, both tables are big, if i dont have and index for that FK when i delete an invoice, i must wait maxdb check if any Order points back to that invoice, and in this time i get an table lock on Order, because maxdb must check every row of Order.

As I wrote: just put a single index on the ORDER table on the INVOICE column, so that a inverse lookup is easy.

regards,

Lars

[Former Member · Accepted solution]

Hi, Lars,

All that is just a sample, the idea i wish to discover here, if have a way by script to discover that type of problem, and avoid to just create index where we really dont need that.

But ok, yow saw that dont exists, then i will create my own, and after post here the steps, thanks again for your attention.

in time: how about that tips for speed MaxDB for B.I.?

best regards.

Clóvis

[lbreddemann · Accepted solution]

> All that is just a sample, the idea i wish to discover here, if have a way by script to discover that type of problem, and avoid to just create index where we really dont need that.

Hmm... this is in fact a rather old wish.

And as far as I know there had been some development into this direction but without ever returning a end-user usable feature.

Unfortunately the INDEX_USED statistic does not seem to record index uses when they happen for such "internal" SQL as I've shown in [MaxDB: Some notes on the INDEX_USED statistic|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/12147] [original link is broken] [original link is broken] [original link is broken];.

Personally I'd say that having a general solution for this is rather difficult to implement, if possible at all.

So depending on the size of the tables and the number of FKs in them, I'd probably just manually figure out, for which DELETEs this kind cascaded DELETEs can happen and add indexes for them.

> But ok, yow saw that dont exists, then i will create my own, and after post here the steps, thanks again for your attention.

> in time: how about that tips for speed MaxDB for B.I.?

I know, I know ... it crosses my mind the end of every working week now for months...

Still not given up that point and will try to bring out something.

regards,

Lars

[lbreddemann · Accepted solution]

in time: how about that tips for speed MaxDB for B.I.?

best regards.

Clóvis

Hi Clóvis,

time passes and things tend to develop different than expected...

This time it means that not I but my colleague Christiane Hienger from the MaxDB Development Support took the effort and produced a SAP MaxDB Expert Session on MaxDB and BI.

As she is the other person knowing about MaxDB & BI (hehehe - just kidding), you'll be in good hands with that!

I know, it's not what I've promised and I really wish I had have the time to provide some information earlier on.

Anyhow, I hope the expert session provides you the information you need.

If you still have questions, well, I know that you know how to post them here

Have fun watching the session and best regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

ok, its very helpfull answer, but when you have time, please, try to create your sample for pentaho with MaxDB, i think that with this we can increase the use of MaxDB at community side. Ok, i know, that this cant generate some money to SAP, but sometimes marketing and a good fight with other databases is more fun than money,

marketing word: "Pentaho runs faster if used with MaxDB, MAXimize your Pentaho!"

best regards.

Clóvis