Which one give best performance SQL Job ,background service

Question

Hello All,

As per my bussiness requirement we need to write code to get all Record and process each record one by one and delete/update data in multiple table in SQL DB ,So what will be best for me to write SQL procedure and call in one SQL Job , or create one background service in asp core and write all code to fetch all record and process them one by one . Both Sql Job and Background service will run every 10 minutes .

Amit Mohanty · Answer

If your process is mostly about database operations, using a SQL Job with stored procedures is likely the best option due to simplicity and efficiency. If your process involves complex logic or integration with external systems, an ASP.NET Core background service gives you more flexibility and control.

Manvendra Patel · Answer

Thanks lot Amit Mohanty ,Željko Peric for valuable comment ,it help me lot to decide which one best for me thanks once again

Željko Perić · Answer

I agree with Amit Mohanty’s assessment and would like to add some additional considerations for resolving this problem: Quote from Amit: “If your process is mostly about database operations, using a SQL Job with stored procedures is likely the best option due to simplicity and efficiency.” While this is true, it’s also important to consider potential server load issues, even in cases where database operations dominate the process. Server Load Considerations SQL Job with Stored Procedures Advantages: Since the entire operation is executed within the database, this option reduces the round trips between the application and the database, thus reducing network latency. However, if the query is complex or involves many operations, it may put a higher load on the database server, especially if other database processes are running concurrently. Performance: Large-scale updates/deletes can cause locks, slow down other queries, and increase I/O operations, leading to performance degradation under heavy load. ASP.NET Core Background Service Advantages: This approach allows more control over how the data is fetched and processed. You could batch the operations, introduce retries or delays, and better manage concurrency to reduce load on the server. Performance: The database server will handle only the necessary queries (data fetching and updating) rather than managing the entire process, spreading out the load across the database and the application server. However, you introduce network overhead due to frequent querying.

Which one give best performance SQL Job ,background service

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)