eAlert indexes are almost useless

Jerome_Pearce

I reported problems with the eAlert indexes a few years ago (May 2011):

http://metastorm.processmapping.com.au/post/eevent-and-ealert-primary-keys-should-not-be-clustered-5258075

 

Basically the eAlert primary key should not be clustered, as it fragments the table very quickly, and the eAlertType should be referenced.

 

It appears that the situation is still the same in 9.3. On top of the table becoming very fragmented when used a lot (and it is the most commonly inserted to and deleted from table in the system), but if you do not store deletion alerts, the index will hardly be used (and may not be at all) if you have lots of Watch alerts, which is very common.

 

It seems simple to fix, and performance improvements would be significant in even modestly sized systems.

Richard Burt

I have often wondered whether or not we are 'allowed' to add or amend indexes on the Metastorm database or whether such action would invalidate our support contract.

 

Industry-Standard SQL health monitoring tools regularly suggest a raft of missing indexes which would improve performance. The tables which worry me most are eFolder (obviously, as it is the core table), eAlert (which is the most frequently updated) and eEvent (which is the largest and fastest growing).

Jerome_Pearce

In my experience, adding indexes will not affect the database operation except for a very slight drop in performance. Assuming the index is used, that is massively offset by the increase in performance an index gives.

 

As long as indexes are not clustered, they will not (cannot) affect the table structure (note that some obscure indexes in Oracle will).

 

As long as no constraints (eg Primary & foreign keys) are added, there should never be any effect on the operation of code using that database. Adding constraints is a definite no-no.

 

This is for the main Metastorm tables. For process tables (and possibly solution tables?) indexes can be added, but will be removed on deployment in later versions of 9.1 and up to 9.3. In 9.0 and earlier versions of 9.1, the Designer will fail to deploy with an ugly and inexplicable error message.

 

You can probably guess that we've been dealing with these issues for a while....

Robert Vignerot

So you are saying that the deployment falure with custom indices doesn't happen on the later releases?  We are using 9.1.3.6 and have to drop and re-create our custom indices for each deployment...

Jerome_Pearce

Yes, that is correct, Rob. In 9.3 we do not have to drop the indexes, but still have to recreate them after deployment.

Robert Vignerot

I suppose that is better than having a random deployment failure that doesn't give you any detail about why, but having to always run a script after deployment is a real problem for us when trying to move from development to UAT or PROD becuase we have to get an extra team involved.