Uniface User Forum

 View Only
Expand all | Collapse all

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

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

    Posted 09-23-2021 04:58

    Hi Team,

     Reading below read trigger u_where condition takes around 32 seconds in uniface but Same query which is taking less than 1 second in toad not sure why its taking so much time in uniface, Can some one please help on this.

    Uniface  takes around 32 seconds

    read  u_where((id.entity1= id.entity2) & key.entity1= key.entity3) 

    DB query

    select * from entity1,entity2,entity3 where entity1.id=entity2.id and entity1.key=entity3.key;




  • 2.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    Posted 09-23-2021 06:22

    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



  • 3.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    Posted 09-23-2021 06:30

    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



  • 4.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    Posted 09-23-2021 07:04

    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



  • 5.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    Posted 09-23-2021 07:22

    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



  • 6.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    ROCKETEER
    Posted 09-23-2021 07:53

    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



  • 7.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    Posted 09-23-2021 08:53

    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




  • 8.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    ROCKETEER
    Posted 09-23-2021 11:25

    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 <sql_attr_cursor_type>
            SQLPOINTER                 0 <sql_cursor_forward_only>
            SQLINTEGER                  -5 ]] ></sql_cursor_forward_only></sql_attr_cursor_type>

    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



  • 9.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    Posted 09-23-2021 11:38
    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.

    0x0001KEYSET
    0x2000AUTO_FETCH


  • 10.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second



  • 11.  RE: read trigger u_where condition takes around 32 seconds in uniface but DB less than 1 second

    ROCKETEER
    Posted 09-24-2021 14:24

    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.