Berocoder

We have an application that pump data from main database to another smaller database. So it just reads from production DB. But it reads a lot! When we turned off that most of the conflicts was gone. The use case for the application is analyze of data. Turn off that is not so popular in management. But we know more now at least

I have actually tested READ_COMMITTED in SQL Studio by open 2 tabs. One with update query with no commit. And the other with read query for same data. And read query is waiting until I commit on first tab. So that is how READ_COMMITTED works. But I also think that update should not take long time. Obviously it is more complicated than that. Had meeting today and we looked at slow queries from Google admin console. Most was select queries but there was also some updates. Single slowest update for one row was 16 seconds. So this feels weird. It should be done in milliseconds. I suppose it waiting on something else so it is a chain reaction. We also saw that some logs was missing 30.4 the day problem started. Might be that the problem starts in Google. We have made a support ticket to them. But much more questions than answers now.

The OS behind SQL Server is handled by Google. None of us have access to it. We see only MSSQL Instance. On applications server was latest updates 10 April so before this problem starts.

Our team have a big problem now but I don't expect people here have the complete solution. Maybe I get a hint. Background I work in a team with for developers. We maintain a big ERP desktop program for transport logistics. Size is about 9 MLOC but that include Devexpress grid and components. The company have about 200 active users. What is a bit unusual is that we use Bold for Delphi as framework between database and application code. That means 99% of SQL for read and 100 % of write queries are generated by Bold. Application is deployed on GCP (Google Computing Platform). The code is far from perfect, there is for example random exceptions in a dialog that handle ferries... We made a patch 29 April morning. People worked normal on that day. Next day 30 April we got reports on slow performance. Slow SQL queries are logged. So I checked that 24 - 29 April there was about 80-90 queries per day that took more than 15 seconds. 30 April and forward there was 200-400 queries per day. There is a slight increases of deadlocks. Now 30 - 40 per day. Previosly it was 15 - 25. I don't get it. Previous patch was made 11 April. Commits in git after patch was not so complicated. A new column was added to a table that replaced a calculated value for vehicles driver. And reads of that was also significantly faster. And also some other minor changes. Anyway if the patch is the reason 29 April should also been a slow day. It wasn't. This is a sample slow query. PlanMission table represent a Trip for vehicle. But each vehicle unit have own insteance. So for example if a truck combination consists of a hauler and a trailer that is 2 PlanMissions. The PlanMission.Trailer link is connected in that case. The purpose of query is to have one row per combination. Trailers are excluded. 2025-05-06 10:13:05:801 (21820) PERF: TBoldUniDACQuery.Open took 58.803 seconds (0.000s cpu) 1 sql for SELECT PlanMission.bold_id FROM PlanMission WHERE (statePlanOpen > 0 OR stateInProcess > 0) AND PlanMission.bold_id NOT IN (SELECT Trailer FROM PlanMission) It took almost a minute to execute. Result is around 1000 rows. One thing I thought about is that we have always used READ_COMMITED isolation in MS SQL Server database. I am no DB guru but that means if any row in the result is updating but not committed queries like above have to wait until done. Advantage is that read query got the latest data. Disadvantage is that user have to wait for the result. But write queries should be fast so don't know either.... If I run query above it takes 0 seconds but that might use the cache in database so maybe not fair comparison. Other things to mention. There is a big project to improve logging but it is not in production yet. Unfortunately that would be handy now. Currently the log is written to a text-file. One per user and session. In the future all logs will be sent to a LogManager application that save it in a logdatabase instead. This will make it easier to analyze problems where different users read and write same data. That is also a problem we had in the past. A group of users both read and modify vehicle-trips and parcels. This is slow. But currently much slower than normal. Can only guess of the reason. It can be the patch. Or it can be some other external issue that came now. As I said I don't expect a solution but maybe a discussion that generate ideas 🙂

The code I wrote was just an example. Actually the project don't have much code to handle raw SQL. We have a framework for that. It is also very big project. About 9 MLOC when do a full build. So the goal is always to do as much as possible in the global exception handler. I cannot evaluate all methods if there is a chance for exception or not. There are many thousands of them. But in some cases there is a need to add more information to the exception message to make it easier to find the reason for exception and fix it later. This is the main reason to do try/except in code

Thanks for the code. I work on rather big application with centralized handling of exceptions. So in most cases try/except is not used. But there is also many places where we want a more customized behaviour. Like add more information to the exceptionmessage. Callstack is generated with JCL. But we have also tried EurekaLog. It is VERY important that the linenumber of original exception is not lost when log callstack. So that is why I am suspicious at ChatGpts suggestion. It also look more complicated that just modify the Errormessage. But it would be nice to find a more complete demo of how RaiseOuterException is used and how global exceptionHandler can traverse inner exceptions. https://docwiki.embarcadero.com/Libraries/Athens/en/System.SysUtils.Exception.RaiseOuterException don't have much information. Another thing. Raise Exceptions is slow. So raise one Exception and then raise again should be twice as slow ?

Today I had a debate with ChatGPT how to handle Exceptions in Delphi 🙂 First it try to talk about Java but I remind that now I am using Delphi... I don't post the whole conversation as it is boring to read. My convention is to do something like this: try sqhpartyroles.sql := fsqlhandle.buildsql; sqhpartyroles.executesql; except on e: exception do begin e.message := format('error %s from sql %s', [e.message, sqhpartyroles.sql]); raise; end; end; If an exception happen I catch it in a global exceptionhandler to log callstack and exceptions message. In this case I also want to add some more information in the message. Reply of ChatGPT: In your scenario, you are indeed preserving the original error message. However, modifying the message of an already thrown exception is generally considered a bad practice, for a few reasons: 1. Reusability and encapsulation: If some other part of the code also catches and handles the same exception, it might receive an error message that has been modified somewhere else in the code, which can be quite confusing. 2. Concurrency issues: In a multithreaded environment, exception objects might be shared among multiple threads. If one thread modifies an exception message, it affects all the other threads that might be handling the same exception. 3. Traceability: By altering the exception message, you lose line numbers and other stack trace information that can help locate where the exception initially occurred. Instead of modifying the original exception, you can create and throw a new exception, adding your custom message while retaining the precious stack trace of the original exception. In Delphi, the way to do this would be to raise a new exception and pass the original exception as an inner exception: try sqhpartyroles.sql := fsqlhandle.buildsql; sqhpartyroles.executesql; except on e: exception do begin raise Exception.CreateFmt('error %s from sql %s', [e.message, sqhpartyroles.sql]).SetInnerException(e); end; end; In this way you have a new exception with the additional context (the SQL that caused the problem), but you also keep the original exception with all its crucial debugging details. However, please note that `SetInnerException` is not a standard Delphi RTL method. To use it, you need to extend the `Exception` class and add it by yourself: type TMyException = class(Exception) private FInnerException: Exception; public function SetInnerException(e: Exception): TMyException; property InnerException: Exception read FInnerException; end; function TMyException.SetInnerException(e: Exception): TMyException; begin FInnerException := e; Result := Self; end; I have seen some references to InnerException but never used it. What is your best practice for exceptions ?

I have been implemented send email with FNC CloudPack to OutLook in Azure. Technically everything works fine. But now there is a change request to automate login. First time a user send an email the browser is started and user need to login. I want to remove that step. I found XOAuth2 protocol that seems to be an extension to OAuth2 that can do this automation by periodically refresh the token ? Is this possible with FNC CloudPack. I found TMS Sphinx. is it possible with this one ? Found this XOAUTH2 Demo from another Component supplier Any other way forward ? I am using Delphi 11.3

But this was about Delphi

What is changed when I do this ?

Yes it is a good idea! But I cannot make any promises. Time is limited

Problem solved. It was my mistake. ApplicationEvents.OnException := nil; I believed that this disabled old JCL that was used before. But it seems to disable whole exception mechanism for software exceptions. Hardware exceptions still works.

The actual Assert is triggered and catched by Delphi IDE. It is just that it is not catched by EurekaLog. So there is no EL-logfile and my event OnExceptionNotify is not called

From SO https://stackoverflow.com/questions/79332724/assert-raise-no-exception It feels like I tried everything. Asserts logs fine with a new application but not in the real big application. Any hints what to do ?

Let EurekaLog send all exceptions to a database is not an bad idea. The downside is that unlike a textfile it needs a tool to red it. Or a SQL prompt... But we have already a system with log-files that contains various loggings, exceptions and callstacks. This use one log-file per running process. We are also working on a solution to log everything to one central place in Google Cloud Platform. So that might be the final solution 😊