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