Uniface too many database connections/requests

Author: paul.snow@rmit.edu.au (snowman)

Hi All, Sorry first post, so hope I am posting into the right place. We have a vendor product built upon uniface 9.501 It is exhibiting incredible slowness. A packet trace reveals that the userver is making 2000+ database requests, looks like a row at a time. I've had a poke around the ASN files, and cannot see anything obvious. Any suggestions for magic parameters what enable batching up these requests into one query ? userver/urouter/tomcat running on linux redhat 6.5 (virtual) db = Oracle 11g on solaris 11 (I think it is solaris 11) (virtual) The vendor is looking into it, but I just want to make sure that there is not something on our side that might be the cause, eg oracle drivers/parameters. Or something simple in the uniface config that I can change to test. thanks Paul


  1. Hi Paul, Hard to say anything without more info. Maybe you can investigate first if it isn't the userver that is started 2000+ times ? You can use the Urouter Monitor for that. Regards, Theo

    Author: Theo Neeskens (tneeskens@itblockz.nl)
  2. Theo, Isn't that the way that UNiface works? It builds a cursor and then gets each row of the result set by getting the data from that cursor. I.e. the read trigger is fired for every row of the table, it's not getting all the data in one query on the database instead it does it RBAR (row by agonising row). There is a setting in the oracle database connector. array fetch size which might help, below is an extract from the documentation. Stepped Hitlist The default step size is dependent on the operating system; it is 10 on most operating systems, but the number may be different (not necessarily smaller) on platforms with memory limitations. When Uniface is building the stepped hitlist, it uses Oracle array fetching to reduce the client/server communication overhead. This does, however, mean extra memory usage by the connector. If you want to ensure that the step size is a specific fixed value, do not depend on the default value. Instead, use one of the following connector options: array fetch size fixed array size step size USYS$ORA_PARAMS = array fetch size 5, step size 10, fixed array size If you use the assignment in this example, Uniface receives five records at a time from the Oracle server, and internally processes the records in steps of 10 (two arrays of five records are fetched per step of the hitlist). This is backwards compatible with older versions of the ORA connector, which used a fixed step of 10. The performance is better because array fetching is used. Parent topic: Oracle

    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  3. Hi Theo and Lain, Thanks for your replies. Luckily it is only one userver process running. Coming from a development background it just seemed a little odd that it would get a row at a time, I am used to pulling hundreds of rows in one call. If this is default behaviour then the array fetch seems like the answer. I'll look into the umonitor, and the array sizes.. thanks for your replies.. much appreciated. (will let you know how we go) Paul

    Author: snowman (paul.snow@rmit.edu.au)
  4. Hi, Of course you are right Iain, but is was not sure yet in what part of the entire process Pauls problem was. Paul, be aware that Uniface does not have the concept of a join between tables. So setting the step size will have a noticeable effect for screen with only one entity painted on them, it does not alway help much for the related entities. Uniface might still need to send a lot of SQL statements to the database. And improving that is a matter of application design, so your application venders responsibility. Good luck. Theo

    Author: Theo Neeskens (tneeskens@itblockz.nl)