Uniface User Forum

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. 

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

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.

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
------------------------------

Original Message:
Sent: 09-24-2021 06:22
From: Imran Pasha
Subject: selectdb performance issue

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.

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
------------------------------

Original Message:
Sent: 09-22-2021 13:27
From: Imran Pasha
Subject: selectdb performance issue

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.