read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

Hi Karthin,

you haven't mentioned the DB you're using. I'm used to Oracle DB (not sure if that's important). Anyway, I can see 2 very different sql's...

The 1st one is a select from a table, probably entity1 with where, something like this "select * from entity1 where ((id='id_from_ent2') and key='key_from_ent3);

The 2nd sql is a join over 3 tables entity1, entity2 and entity3.... you wrote your own sql...

I find it quite common that those 2 (very different) sql's might take quite different amount of time.

You can always set ioprint to let uniface log generated where/sql into a log file (putmess) and then you can compare. According to my experience if you have the same sql, it takes the same amount of time in whichever program you run it (Uniface, SQLDeveloper, SQLplus, ... anything). If there is a different amount of time, then you have different sql's (or differene where's).

And, of course, you could use sql proc statement (or sql/data).

Zdeněk

Hi Karthik

This is a knwon issue with UnifAce

Uniface opens the database cursor in update-mode* where the SQL-editor opens the cursor with read-only-mode*
And there is no way to tell Uniface, which cursor to use.
This was/is a wish for a long time, but ...

Do you need updates with this hitlist?
If not, one way to boost the SQL is to

a) set the entity to non updateable

b) use a view with some hints like
CREATE VIEW VTableName AS SELECT * FROM TableName WITH(NOLOCK)

Ingo

*) simplified

Hi Ingo,

which DB do you use? Because we do not have such issues with Oracle DB. And AFAIK Uniface opens cursors for read (only( as long as you do not start editing.... or maybe, could you clarify what your "update-mode" and "read-only-mode" mean?

Seems like this issues are DB connected?

Zdeněk

Hi Zdenek

We are using MS-SQL

Uniface ist using SP_CURSORPREPEXEC to prepare a SELECT:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cursorprepexec-transact-sql?view=sql-server-ver15

And the cursor type, UnifAce is using is 0x2001
scrollopt
0x0001     KEYSET
0x2000     AUTO_FETCH

And this is very slow
I did try the same SQL with  0x2004 (or some other combinations)

0x0004     FORWARD_ONLY
0x2000     AUTO_FETCH

Any the selects runs very fast like that one from the SQL-editor

Problem with UnifAce ist, that the driver always prepare the cursor/hitlist for a possible "rewind" and updates.
And there is no way to tell UnifAce:In this very moment, just fetch all rows and then forget the cursor
What I want in Uniface is an option like
READ ... ,OPTIONS="DB_CURSOR_TYPE=8196",...

Ingo

Ingo,

Are you using the driver setting "keysetcursor : on"? This would explain the cursor type KEYSET. The default of the driver is "keysetcursor : off" and this should result in a FORWARD_ONLY cursor. The keysetcursor setting is intended for improving performance, but this apparently does not wok in all situations.

Zdeněk, however, makes some good suggestions in his comment. We first need to know which database is used here and which exact SQL statement the driver is generating. It is possible that the execution plan will force a full table scan that will cause the long execution time. Besides that it might be good to know how the three entities are painted on the component. When we have a complete picture then we hopefully can make an assessment how to improve the performance: e.g. change the component, the read statement, adding missing indices to the tables, or create a view with the three tables.

Daniel

Hi Daniel

Nope, this is our driver-setting:
USYS$MSS_PARAMS procs:off,ids:quoted,mapping:2,iso:rc,gran:row,os:96,hs:on,ri:uniface,stmtcache=off,locktime=120,mlw=3
And we (Daniel Kurz and me) were in Amsterdam long time ago to diskuss the cursor behavior 🙂

Regards
Ingo

Hi Ingo,

I did some tracing and from what I can see Uniface will set the cursor type according to the keysetcursor setting.

This is what I can see in the ODBC tracing:

ide             3c10-5f8c    EXIT  SQLSetStmtAttr  with return code 0 (SQL_SUCCESS)
        SQLHSTMT            0x071B72D8
        SQLINTEGER                   6 
        SQLPOINTER                 0 
        SQLINTEGER                  -5 ]] >

This is what the driver is actually setting.

In the SQL Profiler I can see that the following is generated for SP_CURSORPREPEXEC (event RPC:starting):

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=20484
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 char(7)',N'select [ulabel],[ubase],[uform],[ukversion],[umversion],[udescr],[u_bord],[db_access],[u_upd],[u_minr],[u_maxr],[u_int],[templatename],[uinherit],[ualt_name],[uwaorder],[ucomment] from [uxgroup]  where ([uform]=@P1)  order by [uform] asc, [ubase] asc, [ulabel] asc ',@p5 output,@p6 output,@p7 output,'HKR_ZZZ'
select @p1, @p2, @p5, @p6, @p7

If I understand it correctly then the FORWARD_ONLY cursor type is set (along with KEYSET_ACCEPTABLE and FORWARD_ONLY_ACCEPTABLE), as expected.

I saw that the value for the cursor type can be different once the function is executed. No idea why that's the case.

Daniel

set @p6=8193 ; set scrollopt to 0x2001
sp_cursorprepexec 
(1) prepared handle OUTPUT 
(2), cursor OUTPUT
(3), params 
(4), statement 
(5), options
(6), scrollopt 
(7), ccopt
(8), rowcount
(9), '@parameter_name[,...n ]'

scrollopt
Scroll Option. scrollopt is an optional parameter that requires one of the following int input values.

According to this post it's p5 and not p6:

> https://www.sqlservercentral.com/forums/topic/fast_forward-cursor-slow-for-simple-select-count-because-sp_cursorfetch-re-scans-all-index

Beats me.

Double checked this and the scrollopt parameter is p5. When I change the keysetcursor driver option then this will clearly affect the value of p5 in the SQL Profiler tracing. Not sure what p6 is, but it probably has nothing to do with the cursor type.