Rocket U2 | UniVerse & UniData

Hi All,

We are in a high volume environment running Unidata on AIX. I have a question about the new option.

Is anyone useing it? Is there any speed-up in real-world situations?

We have a very old code-base. In what situations would this, perhaps, speed some SELECT quieries up? (Or slow them down)

Has anyone turned it on and noticed any change, good or bad.

We are contemplating turning it on, and are curious about other's experience. Did you test it, etc?

Thank you for any feedback or information.

Best,

Rick Brown

:version

    Module Name         Version   Licensed

UniData RDBMS............ 8.2     Yes

Hi Rick,

A bit of history for you around multiple indexes, there are examples where using mutliple indcies will cause a query to run slower and this is based upon the steps that UniData decides on if / if not to use an index and more importantly what UniData does internally to combine the results together for the result. There is a technote in our knowledge base you can look at around this but it is fairly technical and a lot depends on the state of the indicies themselves in terms of good node distribution.

In order to improve this we added logic to optimize the use of mutliple indexes, however during our internal testing we found a couple of edge  cases where it was slower and these were using 11+ indexes and some of those indexes had very bad node distrubtion and with the optimize logic turned on it was slower. With normal and reasonable indexes our internal testing using 7 indexes and 7 conditions peformed a lot better. The performance improvement on Windows was more dramatic than on Linux and Unix.

When ever we touch something in the UniData engine at this level, we will almost certainly put a UDT.OPTION in place to control to it. Also as it's a UDT.OPTION it can be turned ON and OFF on a per session basis. My recommendation would be test the option for yourself as in most cases I expect it to improve performance, if you find the edge query that is slower you can always code turning the option OFF around the query.

Thanks,

Hi All,

We are in a high volume environment running Unidata on AIX. I have a question about the new option.

Is anyone useing it? Is there any speed-up in real-world situations?

We have a very old code-base. In what situations would this, perhaps, speed some SELECT quieries up? (Or slow them down)

Has anyone turned it on and noticed any change, good or bad.

We are contemplating turning it on, and are curious about other's experience. Did you test it, etc?

Thank you for any feedback or information.

Best,

Rick Brown

:version

    Module Name         Version   Licensed

UniData RDBMS............ 8.2     Yes

Thank you very much, Jonathan.

Rick

------------------------------
Rick Brown
Sysadmin
Rocket Forum Shared Account

Original Message:
Sent: 05-24-2023 08:39
From: Jonathan Smith
Subject: UDT.OPTION 128 (Index usage change)

Hi Rick,

A bit of history for you around multiple indexes, there are examples where using mutliple indcies will cause a query to run slower and this is based upon the steps that UniData decides on if / if not to use an index and more importantly what UniData does internally to combine the results together for the result. There is a technote in our knowledge base you can look at around this but it is fairly technical and a lot depends on the state of the indicies themselves in terms of good node distribution.

In order to improve this we added logic to optimize the use of mutliple indexes, however during our internal testing we found a couple of edge  cases where it was slower and these were using 11+ indexes and some of those indexes had very bad node distrubtion and with the optimize logic turned on it was slower. With normal and reasonable indexes our internal testing using 7 indexes and 7 conditions peformed a lot better. The performance improvement on Windows was more dramatic than on Linux and Unix.

When ever we touch something in the UniData engine at this level, we will almost certainly put a UDT.OPTION in place to control to it. Also as it's a UDT.OPTION it can be turned ON and OFF on a per session basis. My recommendation would be test the option for yourself as in most cases I expect it to improve performance, if you find the edge query that is slower you can always code turning the option OFF around the query.

Thanks,

------------------------------
Jonathan Smith
UniData ATS
Rocket Support

Original Message:
Sent: 05-24-2023 08:12
From: Rick Brown
Subject: UDT.OPTION 128 (Index usage change)

Hi All,

We are in a high volume environment running Unidata on AIX. I have a question about the new option.

Is anyone useing it? Is there any speed-up in real-world situations?

We have a very old code-base. In what situations would this, perhaps, speed some SELECT quieries up? (Or slow them down)

Has anyone turned it on and noticed any change, good or bad.

We are contemplating turning it on, and are curious about other's experience. Did you test it, etc?

Thank you for any feedback or information.

Best,

Rick Brown

:version

    Module Name         Version   Licensed

UniData RDBMS............ 8.2     Yes

------------------------------
Rick Brown
Sysadmin
Rocket Forum Shared Account
------------------------------