Adding my own index to schema tables?

[Cringer]

If I add my own indexes on VSTs will it break anything?

 

[RealHeavyDude]

You can't. AFAIK the schema tables are frozen. You would need to lift the "freeze" upfront.

Furthermore I strongly recommend you not messing with the schema of any table that is part of the meta-schema of the database. Although some of the definitions are only in the meta-schema so that one could compile programs referencing VSTs not all of them are virtual. Some of them, like _Lock, are not virtual. I would consider all theses tables part of the meta-schema and would leave them be. Most likely something will break if you don't.

Just out of curiosity - why would you want to add an index onto a VST?
Wouldn't it be better to cache the relevant VST in a Temp-Table which provides indexing that suits your needs?

Heavy Regards, RealHeavyDude.

 

[Cringer]

Thanks RHD. I thought as much. It's the UserIndexStat and UserTableStat tables. They have a field on them called xxx-conn which is a foreign key to the _connect table. If I want to find out what tables and indexes a specific user is hammering the query is pretty slow, and if I want to break it down for all users every five minutes, say, the query doesn't complete before the next iteration is needed. You can fudge it but it's not 100% accurate, particularly if the same user is logged in many times.

 

[TomBascom]

VSTs are not real tables and do not use indexes per se.

The "id" field is the only useful pseudo-index on most VSTs.

ProTop is your friend - just get over your ChUI phobia

George posted some interesting usertablestat code on PEG recently.

 

[Cringer]

Thanks Tom. Not got a massive phobia of CHUI. I've got some monitoring scripts that output stuff to file every 5 minutes. It would be useful to be able to also put out who is doing what at that time to narrow down what is hogging resources. I know that at 6pm all DB activity goes through the roof. This is partly the backups kicking in, but I suspect other batch processes may also be hammering the DB at the same time. It would be lovely to have that info accurately available rather than cludged.
I'll have a look on the PEG. I don't tend to follow it to be honest.

 

[Rob Fitzpatrick]

I'll have a look on the PEG. I don't tend to follow it to be honest.

PEG discussions can go off into the weeds at times, but there are also some gems in there at times. Highly worth the $75/year, in my opinion, even if you never post. Also, several interesting and informative people post there and in no other Progress channels (at least the ones I follow).

 

[Rob Fitzpatrick]

I know that at 6pm all DB activity goes through the roof. This is partly the backups kicking in, but I suspect other batch processes may also be hammering the DB at the same time.

I suggest you look at _UserIO. If someone is really hammering the database you should see their DB Accesses go through the roof. There is one record per user so the query will be quick. Then, once you know the user(s) in question you can target them with _UserTableStat/_UserIndexStat queries to see which objects they're hitting. Or, turn on client statement caching for those users to see what code they're running, which may be what you're really after.

Or you can just break down and install ProTop already.

 

[Cringer]

Thanks Rob. That's a good idea.
Can ProTop dump stuff to file easily for future consumption? Not found that ability. Although in truth I haven't looked to hard.

 

[Rob Fitzpatrick]

It can e-mail you screen scrapes.

 

[Rob Fitzpatrick]

In addition to monitoring, ProTop is also very useful to programmers as a VST-querying code tutorial, as it's open source.

 

[Cringer]

Thanks Rob. Will have to get round to having another look.

 

[Rob Fitzpatrick]

To help narrow down your search, George posted his code on the morning (in EDT) of October 8.

 

[TheMadDBA]

Here is the simple way - basically the same as what George posted... Example for _UserTableStat but the same concept works for _UserIndexStat.

Find the highest _TableStat-ID value, then multiple the conn-id * that number

ASSIGN UserStart = (HighTableID * ConnectionID) + 1
UserEnd = UserStart + (HighTableID - 1).

Then you can query the _UserTableStat-ID GE UserStart and LE UserEnd. It will be really fast and indexed instead of scanning the entire VST.

 

[Cringer]

Brilliant! I will be implementing that shortly. Thank you.

 

[TheMadDBA]

No problem... One thing I forgot to mention... if you are looking at conn-id 1 then you need to just use 1 and HighTableID. Connection 1 is almost always a broker/server but better to be safe.

 

[Cringer]

Just implemented noddy code to test this and it's awesome!!!! Very happy man here. Just got to implement the change in our DB monitor tool now. Which means I need to remember how to build the pl... lol