Uniface User Forum

 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

    Posted 11-02-2021 10:29
    Edited by Gianni Sandigliano 11-02-2021 11:40

    Hi Pedro,

    my first reactions after reading your description are:

    1) aren't you mixing date and datetime variables/fields? Could it be sometimes datetime is including a time and this could be the reason of the failing query?
    2) if your query is returning just 1 record, the "order by" could be avoided, right?
    3) are db statistics on that table updated?
    4) with rowlock means lock is active. Are you sure it is a performance issue and NOT a locking issue?

    Regards,
    Gianni Sandigliano

    @Pedro: sorry I've edited my message about 15-20 minutes after initial answer to add a 4th possible reason...you were probably already answering ... :-)

    ------------------------------
    Gianni Sandigliano
    Italy
    ------------------------------



  • 3.  RE: Performance issue on queries to DB

    PARTNER
    Posted 11-02-2021 10:47
    Hi Gianni, thanks for answering.

    Regarding your observations.
    1) All fields in the database are DateTime (it's sql server) but some fields have only the date piece of information (ses_date, date of the session) and others have date and time information (fin_time, finish time of the session), that's why we mix dates and date/time values. With the query, we are trying to retrieve sessions of this day and sessions that started yesterday but finish today (or later). Anyway, the query works, after some tries, so I guess it is not something with the query.
    2)  It can return more than one result, that's why we have the order by. For example, you could have one session in the morning and another in the afternoon (start_time is the starting time of the session). 
    3) No, db statistics are not updated, and indexes are terribly fragmented. We know that but, why then the query works well in SQL Server Management Studio but not in the application and needs some tries in the application to start working? If it was some problem with the database it would not work in the Management Studio either, wouldn't it?

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



  • 4.  RE: Performance issue on queries to DB

    ROCKETEER
    Posted 11-02-2021 11:12
    I think that Gianni is on to something here. This indeed could be a locking problem.

    What driver settings (usys$mss_params) are you using? The driver has several locking options. It hereby is important that the option locktime is greater than maxlockwait. If that's not the case then the maxlockwait option has no effect and the driver could return a query timeout error  instead of a lock request timeout error.

    I hope this helps.

    ------------------------------
    Daniel Iseli
    Rocket Internal - All Brands
    Geneva Switzerland
    ------------------------------



  • 5.  RE: Performance issue on queries to DB

    PARTNER
    Posted 11-02-2021 12:00
    Hi both,

    This looks promising...

    We thought of locks in the database but, as the dba did not find any locks, we discarded the idea.
    The mss_params are the following
    USYS$MSS_PARAMS = step:9999,mapping:2, procs:off, ids:quoted, gran:row

    We don't seem to specify locktime or maxlockwait. Should we specify some values for these parameters?
    I've read that, by default, locktime takes 10 seconds. What could be an acceptable value to test?

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



  • 6.  RE: Performance issue on queries to DB

    Posted 11-02-2021 12:46
    Edited by Gianni Sandigliano 11-02-2021 12:49

    Hi Pedro,

    I suppose you want back a $status = -11 when the occurrence you are trying to update is locked, same way like in Oracle or PostgreSQL.
    In this case maxlockwait MUST be greater than 0 and less than lockwait.

    Let's have:
    - lockwait stick to 10 seconds default NOT explicitely defining it
    - maxlockwait defined to 3 (seconds) into your USYS$MSS_PARAMS

    In those situations where you were getting a timeout result after 10 seconds now you should get:
    - $status = 0, immediately...when you are accessing and locking your resource
    - $status = -11 after 3 seconds...when your resource is already locked from another process. You can later tune up the value like you prefer taking care of the basic rule already mentioned.

    Opening a complete discussion on which is the best locking choice for MS SQL Server is "out of scope" here.  Locking behaviours are still today the biggest differentiators between various (R)DBMS available on the market.

    Regards,
    Gianni



    ------------------------------
    Gianni Sandigliano
    Italy
    ------------------------------



  • 7.  RE: Performance issue on queries to DB

    ROCKETEER
    Posted 11-02-2021 12:47
    Thanks for the info.

    You could try to do a test with maxlockwait:10 and locktime:30, and see what it does.

    But did the dba also use the SQL Server Profiler to analyse this problem? The profiler should be able to tell you what happens with this particular query and why it fails sometimes. If it's not a locking problem then maybe the database is doing a full table scan for some reason? Just guessing.

    ------------------------------
    Daniel Iseli
    Rocket Internal - All Brands
    Geneva Switzerland
    ------------------------------



  • 8.  RE: Performance issue on queries to DB

    PARTNER
    Posted 11-02-2021 13:20
    Ok, we're gonna try setting those parameters just to see how the application responds.

    @Daniel I was more inclined to think that the application was doing a full table scan, but the DBA didn't find that.

    The DBA is still working analyzing the data he has got, but I've started to move this here to get some ideas.

    I'll tell ​you what we find.

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