Rocket Uniface Support Resources

 View Only
  • 1.  Performance issue on queries to DB

    PARTNER
    Posted 11-02-2021 08:35
    Hi Uniface community!

    First post here and here I come with a problem.

    We are having a strange issue with a query that is failing because of a time out at the beginning of the day but after some tries from part of the user, it starts working. But, of course and the problem is here, if we run the query in SQL Server Management Studio it takes less than a second.
    We are working with SQL Server and the version of Uniface is 9.7.05.
    This version of the application is working in several customers without any performance issue but the last one we have updated has found this issue.

    The query is quite simple and it would return only one record. It is built with a u_where like this

     read u_where (SES_DATE.SES_DET <= DATE.DUM & %\
                     FIN_TIME.SES_DET > TIME.DUM) %\
                     order by "THEATRE,FIN_TIME,SESSION_ID"
    ​


    We have generated the log file with ioprint level on 63 and this is what we get when the query is failing.

    I/O function: S, mode: 0, on file/table: SES_DET index: 5 >=
     Where ((SES_DATE = 21102021 And THEATRE = 'DDS') Or (SES_DATE < 21102021 And FIN_TIME >= 21102021 00:00:00 And THEATRE = 'DDS')) Order By(
        START_TIME)
    select [theatre],[ses_date],[session_id],[consultant],[specialty],[start_time],[fin_time],[ana_type],[nocase],[time_used],[cance
    lled],[cancel_reason],[notice_code],[u_version],[open_closed],[da_flag],[cancel_2],[cancel_3],[ls_reason_2],[ls_reason_3],[lf_re
    ason_2],[lf_reason_3],[es_reason_2],[es_reason_3],[ef_reason_2],[ef_reason_3]
    from [ses_det] with(rowlock)
     where ((([ses_date]=? and [theatre]=?) or (([ses_date]<? and [fin_time]>=?) and [theatre]=?)))
     order by [start_time] asc
    
    
    I/O function: S, mode: 0, on file/table: SES_DET index: 5 >=
     Where ((SES_DATE = 21102021 And THEATRE = 'DDS') Or (SES_DATE < 21102021 And FIN_TIME >= 21102021 00:00:00 And THEATRE = 'DDS'))
    HYT00 [Microsoft][ODBC SQL Server Driver]Query timeout expired

    The strange thing is that the same query, after users are trying to open the screen several times, it returns this and everything works fine.

    I/O function: S, mode: 0, on file/table: SES_DET index: 5 >=
     Where ((SES_DATE = 21102021 And THEATRE = 'DDS') Or (SES_DATE < 21102021 And FIN_TIME >= 21102021 00:00:00 And THEATRE = 'DDS')) Order By(
        START_TIME)
    select [theatre],[ses_date],[session_id],[consultant],[specialty],[start_time],[fin_time],[ana_type],[nocase],[time_used],[cance
    lled],[cancel_reason],[notice_code],[u_version],[open_closed],[da_flag],[cancel_2],[cancel_3],[ls_reason_2],[ls_reason_3],[lf_re
    ason_2],[lf_reason_3],[es_reason_2],[es_reason_3],[ef_reason_2],[ef_reason_3]
    from [ses_det] with(rowlock)
     where ((([ses_date]=? and [theatre]=?) or (([ses_date]<? and [fin_time]>=?) and [theatre]=?)))
     order by [start_time] asc
    
    
    -> Hits selected: 1 .​

    If we run the query in SQL Server Management Studio it takes less than a second. The record is there since the beginning and it is retrieved without any problem.

    Our dba has been looking at the db and he says he doesn't find anything strange. He has been looking at database traces and there is no query that is taking too long, not even this one.

    What I don't understand is why, after trying several times, everything seems to be right and you can close the application and open it up again and there is no performance issue.

    We have a copy of the database and cannot replicate the problem there. The only way we have been able to replicate it has been by deleting the cache of the database. Doing this it seems we repeat the same behaviour that they find at the beginning of the day but we are not certain that the cache has been deleted in the live environment. It is something we are still investigating.

    Finally, there are some errors that I know we have, but other customers also share these errors and they are not facing performance issues. For example, they are using SQL Server 2016 but the connector we have in the asn is U4.0. I know it should be U5.1 but the application was installed with this connector and it seemed it caused no error. We have tried changing the connector in some clients but it does not seem to fix the problem.

    Also, another error we have is that the application was developed and compiled with Uniface 9.7.04 but is being run with Uniface 9.7.05. I don't know if this could affect somehow.

    Any idea or clue to continue investigating??

    Thanks and regards!

    ------------------------------
    Pedro Morales
    Uniface Community Edition Shared Account
    United States
    ------------------------------


  • 2.  RE: Performance issue on queries to DB

    PARTNER
    Posted 11-08-2021 09:07
    Edited by David Andrews 11-08-2021 10:16
    Based on the SQL I would imagine it is one of 2 things:
    1. The data you are trying to fetch is locked by someone else (or perhaps your own session elsewhere) and your "with Rowlock" query is waiting for the lock to be released. In oracle a locked row would return straight away with an error but as I recall SQL server and Sybase database can "wait for lock". Not sure if there is a driver setting for this or a DB setting.
    EDIT: Sybase certainly and possibly SQL server can also apply "page" locking where requesting a lock of a single row can apply a lock to an entire page of records in an index so it may not be directly locked. Some ways to get around this would be to:
     - Ensure all records are specifically unlocked.
     - Go for a more optimistic locking strategy so that it doesn't do the "with rowlock".
     - Set the page size to 1 so that only the requested record is locked (probably a DBA function).
     - Turn off page locking (probably a DBA function).
    2. Some difference in the table definition - perhaps the index definition - if the index that uniface thinks it is using is actually not the same in the model and database it could be trying to plug query value into different fields.