Uniface User Forum

 View Only
  • 1.  selectdb performance issue

    Posted 09-22-2021 13:28

    Hi Experts,

    There is a simple select statement in lose focus trigger of a field.

    The problem is it takes around 30 secs to execute in uniface but it takes fraction of a second when executed from toad not sure why there is so much of difference in timing ?  also u_where clause is used in query which has only a column in it which is primary key on table. 


    Thanks,

    Imran. 



  • 2.  RE: selectdb performance issue

    PARTNER
    Posted 09-22-2021 13:57

    You can get the exact SQL generated by UNiface from running it with $ioprint set to 32. This may point to how it is formatting the SQL differently from what you would expect from the selectdb.

    If you run the expected (or produced) sql in toad, but with the filter column off (so group by the filter column, but don't filter it, and it takes  30 seconds, that may point to what uniface is doing differently (applying the filter to the resultset instead of part of the query). 

    Alternatively, you may find there's an improvement if you build the sql yourself and run it as sql/data. 

    Regards, 

    Iain



  • 3.  RE: selectdb performance issue

    Posted 09-24-2021 06:23

    Thanks for your comments Iain.


    I have set $ioprint=32 in asn file and restarted the application but though the query in discussion is executed it is not captured in message window. Below is how query looks 


    selectdb(count(column_name)) from "table_name" u_where(column_name.table_name = "%%L_VAR") to (L_COUNT)

    There many other queries showing in the message window except above. application uses oracle database.



  • 4.  RE: selectdb performance issue

    ROCKETEER
    Posted 10-01-2021 06:57
    With $ioprint=63 there will be more information:
    What happens using the sql script statement:
    sql "select count(column_name)  from table_name where column_name = '%%(L_VAR)'","ORA"

    for example

    select count(ULABEL) from UFORM where ULABEL='TESTSQL'


    The delay is perhaps a hitlist being completed.

    Regards
    Peter

    ------------------------------
    Peter Beugel
    Rocket Internal - All Brands
    Denver CO United States
    ------------------------------



  • 5.  RE: selectdb performance issue

    PARTNER
    Posted 10-08-2021 07:26
    Hi,
    What database do you use ? For example with ORACLE, Uniface uses variable binds, which makes it possible to optimize an execution plan of the request by using the statistics, whereas if we do not use bind variables the execution plan is calculated each time .
    If these statistics are bad / outdated the query may not be efficient.
    Gilles.

    ------------------------------
    Hortion Gilles
    Agfa-Gevaert NV
    ------------------------------