locked
architectural guideline RRS feed

  • Question

  • User1489758560 posted

    We have healthcare office in 800 locations and

    Right now we have web application and each facility manager click web application button triggers api which does complex backend operation and getting time out some times. to avoid that, i am planning to do this on the c# console application job. 

    I have a c# console application created and can be scheduled on task scheduler. have 5 servers to deploy this. this batch job calls multiple procs and its does complex operations. so one facility will take 3-4 minutes to finish operation. I cannot do sequential operation like one facility at a time. I need to run this for 800 facilities at a time. This has to happen 5pm everyday for each facility. What is the best way to achieve this scenario from architectural perspective.

    Tuesday, April 20, 2021 2:50 AM

All replies

  • User-821857111 posted

    In my opinion, you need to forget about architecture and establish why the timeouts are occurring first. Reorganising poor performing code from a web app to a console app is not going to make it run better. 

    Tuesday, April 20, 2021 5:08 AM
  • User-474980206 posted

    Assuming the the data is properly partitioned by facility you should run into few locking issues. But this is what you want to tune for. 

    Friday, April 23, 2021 6:30 PM
  • User1489758560 posted

    Mike and Bruce, thanks for the reply.  At this moment backend tuning will be hard as this process calls multiple Procs and more complex logistics involved.  But i can understand that will be the long term solution. but for time being, is there any other workaround? Right now the Timeout error occurs because of backend taking time to respond back. Can i start looking into library like HangFile.IO to achieve long running processing as a separate thread? But if i go this route, how do i know the process completed? your sugestions will be highly appreaciated.

    Sunday, April 25, 2021 2:57 AM
  • User475983607 posted

    born2win

    Can i start looking into library like HangFile.IO to achieve long running processing as a separate thread? But if i go this route, how do i know the process completed?

    We have no idea how your process works or why you do not know when the process ends.  Very unusual.  Maybe you are asking how make the this unknown process stateful?  Perhaps store the process results in a table with a meaningful key.

    Anyway, this approach most likely will not fix the timeout.  You are just moving the timeout to other logic.

    Sunday, April 25, 2021 12:02 PM
  • User-821857111 posted

    It depends on the cause of the timeouts. If they only occur because 800 clients are overloading the system, you could just add resources to cater for the load. 

    Sunday, April 25, 2021 1:43 PM
  • User1489758560 posted

    Thanks everyone who trying to  mentor  and I am sure will tune the procedure performance. but still this procedure have written complex logic's and it has cursor being used to loop through the records. this code is pretty old. since more logic's are written in this procedure, it cannot be touched at easily. but we will at some point. right now we are trying to find the solution without touching the backend. Like i said the issue we are getting is "TimeOut" as backend takes more time to process data if if needs to process number of patients are more for the day. 

    i have below approaches for temp solution and please suggest which one will be the best,

    1. using HandFire.IO to run this logic in API as task
    2. Have the logic in console application and trigger the exe from API
    3. Create this proc calling logic in Windows service and schedule in each facility machines.

    Please suggest me which option will help me at this moment.

    Sunday, April 25, 2021 2:24 PM
  • User475983607 posted

    None of the proposed solutions will fix a SQL client timeout due to a long running script.  What is the source of the timeout?  

    Sunday, April 25, 2021 2:58 PM
  • User1489758560 posted

    The source of Timeout is logic in Storedprocedure. Inside this proc there a cursor being used to loop through the records with complex logic was written. approximately this proc takes 5 - 20 mins based on load of data.

    Sunday, April 25, 2021 9:59 PM
  • User-821857111 posted

    The source of Timeout is logic in Storedprocedure. Inside this proc there a cursor being used to loop through the records with complex logic was written. approximately this proc takes 5 - 20 mins based on load of data.

    So you are getting a SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding? If so, you can increase the CommandTimeout value (https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-5.0) or set it to 0, which means that there is no timeout limit. 

    Moving your C# code to a console app or Hangfire etc will make no difference unless you do that.

    Monday, April 26, 2021 9:21 AM
  • User1120430333 posted

    It seems to me that the sproc and whatever else should be using MS SQL Server Service Broker in an async  manner with notification  back to the client when the process is done.

    https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics

    Service Broker can also host and execute  .NET code such as C# or VB.NET. To me,  that's  what you should be looking into is using the power of SB and MS SQL Server.

    I was the one posting to you about using async in MS Q&A. Now that you said what the timeout is about, a MS SQL Sevier timeout,  you should figure out a way to send a message to a SB queue for an individual entity in your  company and execute the sproc and let it run and let the process log start and completion messages to a log file. Hell  email a completion message back to whoever.

    Monday, April 26, 2021 2:24 PM
  • User-474980206 posted

    Thanks everyone who trying to  mentor  and I am sure will tune the procedure performance. but still this procedure have written complex logic's and it has cursor being used to loop through the records. this code is pretty old. since more logic's are written in this procedure, it cannot be touched at easily. but we will at some point. right now we are trying to find the solution without touching the backend. Like i said the issue we are getting is "TimeOut" as backend takes more time to process data if if needs to process number of patients are more for the day. 

    i have below approaches for temp solution and please suggest which one will be the best,

    1. using HandFire.IO to run this logic in API as task
    2. Have the logic in console application and trigger the exe from API
    3. Create this proc calling logic in Windows service and schedule in each facility machines.

    Please suggest me which option will help me at this moment.

    you don’t give us enough info. You do need a reliable task scheduler, I would not use the websites as my first choice. Also if a deadlock happens, the task needs to recover or be rerun. the code that calls the proc needs a longer timeout. But you need to test how many off the 800 can run concurrently without blocking issues. 

    Wednesday, May 5, 2021 7:21 PM