0

I am fairly new to asynchronous programming so I need help.

What I need to do is, create a windows service that constantly checks the database for menu updates (insert/updates), tables updates (insert/updates), menu category updates (insert/updates) and so on and if any change is detected the service will then need to POST those said changes to separate APIs one by one. Keeping in mind that the service will be used for just this purpose and the database that I need to check for updates is SQL Server.

So, how do I approach this scenario efficiently ? Do I create new Tasks (System.Threading.Tasks) or create new Threads (System.Threading.Thread) for each pieces like UpdateMenu that checks the menu updates and upload to api, UpdateTable, UpdateDishes and so on and how do I go about the Posting to the API part I mean do I create a new Task for each and every API call? I want the application to be as efficient as possible and pick the changes and post them to API as soon as possible.

Thanks in advance.

Ishan
  • 285
  • 2
  • 8
  • Why not call the API from the same code which changes the database? `how do I go about the Posting to the API part` do you know how to call Web APIs from C# code? – Chetan Jun 27 '22 at 04:11
  • Can't do that because we don't have the source code of the application, we only have access to the database so.And sorry for misinformation, the api call and service development part is not the problem rather I want them to be as efficient as possible and pick changes and post to the API as fast as possible. – Ishan Jun 27 '22 at 04:13
  • *"checks the database for menu updates (insert/updates), tables updates (insert/updates), menu category updates (insert/updates) and so on"* -- What are the menus, tables and menu categories? Are these separate tables in the database? – Theodor Zoulias Jun 27 '22 at 04:17
  • Yes they are separate tables of a Restaurant database. – Ishan Jun 27 '22 at 04:18
  • Is your intention to pull the database for changes with the same frequency for all three tables (Menus, Tables and ManuCategories), or with a different frequency for each table? – Theodor Zoulias Jun 27 '22 at 04:21
  • Yes I need to pull the changes from each table almost at the same time and frequency, because if a menu category will be added most likely the menu will also be added for that category. – Ishan Jun 27 '22 at 04:22
  • 1
    Have you identified an approach of how to identify the updated/deleted/inserted rows from the table? – Chetan Jun 27 '22 at 04:23
  • Do you have the ability/authorization to change the schema of the database, for example by adding insert/update triggers on the tables that you are interested to monitor for changes? – Theodor Zoulias Jun 27 '22 at 04:25
  • Are you expecting something to query your database continuously for changes? That sounds inefficient as you will have many queries taking CPU time that return nothing (assuming you have no delay in between because it needs to be as fast as possible). Perhaps figuring out how to *trigger* an event on database change, rather than querying constantly, would be the solution. – Ibrennan208 Jun 27 '22 at 04:25
  • Yes, I have a stored procedure that fetches me the changes. – Ishan Jun 27 '22 at 04:26
  • So, that's where my confusion comes in to play because as of now I call the procedures that bring me the changes.. – Ishan Jun 27 '22 at 04:28
  • What's the blocker here? You need to create Windows Service in .NET Framework. You have one or more workers to perform tasks in parallel continuously without impacting each other.. You need to write code for those workers to pull the data from the db and call the API. – Chetan Jun 27 '22 at 04:29
  • Constantly querying your database with the hopes that there may be change is not efficient. Perhaps this will help: https://stackoverflow.com/questions/5288434/how-to-monitor-sql-server-table-changes-by-using-c – Ibrennan208 Jun 27 '22 at 04:36
  • How do I create one or more workers to perform tasks in parallel continuously without impacting each other ? That's what I want to know. – Ishan Jun 27 '22 at 04:46
  • @Ibrennan208 What are the things that I need to consider before using ENABLE_BROKER and SqlDependency Class ? I mean do they have any impacts on the databases ? – Ishan Jun 27 '22 at 04:52
  • You should do some research on internet about creating windows service using .NET https://learn.microsoft.com/en-us/dotnet/core/extensions/workers – Chetan Jun 27 '22 at 04:57
  • @Ishan See: https://learn.microsoft.com/en-us/sql/database-engine/service-broker/overview?view=sql-server-ver16 And: https://learn.microsoft.com/en-us/sql/relational-databases/service-broker/event-notifications?view=sql-server-ver16 And: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-ver16 – Ibrennan208 Jun 27 '22 at 05:08

1 Answers1

1

It seems that you are worried about the overhead of the mechanism that you are going to use, in order to fetch data from the database and post these data to APIs. You are thinking that maybe Threads are fast and Tasks are slower, or vice versa. In fact choosing between these two mechanisms is likely to have no measurable impact to your service's demand for CPU, memory or other system resources.

What is likely to be impactful, is the pattern of communication of your service with the database and the APIs. For example if your threads/tasks are not coordinated with each other, and query the database all at the same time, the database might be slow to respond, and might consume larger amounts of memory while preparing the response. That's not because your threads/tasks are slow. It's because your service is querying the database with a pattern that makes it harder for the database to respond. The same might be true for the pattern of communication with the APIs. If your workers are not coordinated, the network connectivity might become a bottleneck, or the remote machines that host the APIs might suffer.

So my advice is to focus on the usability factor of the mechanisms, and not on their supposed difference in performance. If you are comfortable and familiar with threads, and know nothing about tasks, use threads. If you are familiar with both threads and tasks, use tasks because they are generally easier to use. You'd better invest your time to optimize the communication pattern between your service and its dependencies, than for doing benchmarks trying to find the best between mechanisms that for all intents and purposes are equally efficient.

Theodor Zoulias
  • 34,835
  • 7
  • 69
  • 104
  • Thank you for your answer, but how do I coordinate my workers and how do I optimize the communication pattern and what are these workers are they threads/tasks ? – Ishan Jun 27 '22 at 05:29
  • @Ishan the workers can be whatever you are more familiar with. Threads, tasks, it doesn't matter. As for how to coordinate them, that's a more difficult question that might deserve a separate post. You should first come to a conclusion about the desirable communication pattern, based on the capabilities of your database and the weight of the queries, before asking. If you know what you want to do, we'll show you how to do it. If you don't know, and ask a generic question about the efficiency of different patterns, most likely you'll get generic and not helpful answers like this one. :-) – Theodor Zoulias Jun 27 '22 at 06:16
  • Can you suggest some Communication Pattern, because I don't think I know any ? – Ishan Jun 27 '22 at 06:31
  • @Ishan the simplest pattern is to serialize the requests. Don't issue a new request before receiving the response of the previous request. – Theodor Zoulias Jun 27 '22 at 06:40