[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


.net service and MSSQL, need to trigger process and leave

Posted on 2012-08-31
Medium Priority
Last Modified: 2012-09-05
I have a service which is called by an application that simply needs to add or update a row to a database and then return a success, the service has now ended. Meanwhile, the row that was added/updated needs to trigger on a field, on its own without any connection to the first service.  That trigger will call a service to do some other table updates.

The point is that the first service must respond fast and will go away while this 2nd performs it's operations. This entire process must be super fast.

Is this possible? How, if so? and the solution must be fast.

Question by:petel2k
  • 2
  • 2
LVL 21

Expert Comment

by:Dale Burrell
ID: 38356636
The normal way to do what you are asking is have your second service running as a true service i.e. it runs in the background all the time, and have it poll the database for any records that need processing.

However it depends how much work you need to do per row, you could put a database trigger on the table and have it carry out the additional processing. This does mean that the first service will be waiting while it the trigger runs though, but I've used very complex database triggers with very good performance, you just have to ensure you write it with performance in mind.

If these aren't options maybe you can define what you mean when you say super fast... processors tend to be pretty quick these days anyway, so how fast is super-fast?


Author Comment

ID: 38357327
I have no more than 1 sec wait time in the first service. The next service is the one that consumes the most time. It needs to connect to the data row added or updated from the first. Using that data it calls  another service which I have no control over, That service takes 2 seconds.

1. Here is what's going on; An application we use allows us to call a service, but that service must run in less than 1 second. What I did was create a service to drop a row into a table with instructions, ie get information for a specific account number. This service must now end.

2. My second service I wrote will read the earlier row written and using the account number to call another outside service, one I have no control over and takes 2 seconds to get the data. After I receive the information I update that same row with customer info.

3. The original app that called my first service in step 1 will now call another service I wrote 5 seconds later, to pick up the customer info.

This stuff is time sensitive and my control is limited. Here's how it works. I ask the customer on the phone for an account # and then do step 1. I then play a message to the customer and step 2 is working in the background. After the message I run step 3 to get the data. This is done to avoid the customer from hearing nothing on the phone since the app we use cannot play a message and execute a service at the same time.

I don't like the idea of an app running through records looking for updates since it wastes cycles. There are about 50 calls that come in at any moment and must be fifo.

LVL 21

Accepted Solution

Dale Burrell earned 1500 total points
ID: 38357931
Well my first suggestion is still a possibility, although as you point out it would be making a lot of calls to the database to check when work is required. But it can and has been done that way.

To avoid having to query the actual table you are adding rows to you could insert a row into table B containing the ID of the row in Table A and saying that it needs processing. Then when you finish processing the row you can delete the row in table B. That means that the performance of finding the relevant rows should be very fast.

The only other solution I can think of is the push notification feature of SQL Server.


LVL 28

Expert Comment

by:Ryan McCauley
ID: 38368660
I've done this and I accomplished it via a queue table with a clustered index on an auto-generated identity column - that way, when you fetch "TOP 1", you always get the oldest row. However, if you're leaving work in the table after it's done, you could also add a "NeedsProcessing" BIT column and set up a filtered index on it so that you can fetch TOP 1 instantly with the criteria you want, and then just flip the BIT column to 0 when you're done with that row.

Here are some good links about queue processing in SQL Server, and a few gotchas to watch out for (like what happens if your second service crashes - does it release the row it was working on? How can you ensure your inserts in the first service are instant and don't get blocked by the second service? Etc.):


Author Closing Comment

ID: 38368689

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question