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

Posted on 2012-08-31
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
    LVL 21

    Expert Comment

    by:Dale Burrell
    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

    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

    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
    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    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…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now