Rocket U2 | UniVerse & UniData

 View Only
  • 1.  UDT.OPTION 128 (Index usage change)

    Posted 05-24-2023 08:13

    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.


    Rick Brown


        Module Name         Version   Licensed

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

    Rick Brown
    Rocket Forum Shared Account

  • 2.  RE: UDT.OPTION 128 (Index usage change)
    Best Answer

    Posted 05-24-2023 08:40

    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.


    Jonathan Smith
    UniData ATS
    Rocket Support

  • 3.  RE: UDT.OPTION 128 (Index usage change)

    Posted 05-24-2023 09:31

    Thank you very much, Jonathan.


    Rick Brown
    Rocket Forum Shared Account

  • 4.  RE: UDT.OPTION 128 (Index usage change)

    Posted 05-24-2023 18:17
    Edited by Jonathan Smith 05-25-2023 10:02

    We had a 50 million-plus file that had about 18 indexes running on Windows Unidata. We found no problem with performance as regards writing or SELECTing. However, we generally had all of the SELECTing use a maximum of 5 indexes which help every query to come back in less than 2 seconds. Then we had the UniBasic program throw out the value we did not want.

    Doug Averch
    U2 Logic