petel2k
asked on
.net service and MSSQL, need to trigger process and leave
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.
Thanks
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.
Thanks
ASKER
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.
Thanks
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.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.):
http://www.adathedev.co.uk/2010/03/queue-table-processing-in-sql-server.html
http://rusanu.com/2010/03/26/using-tables-as-queues/
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.):
http://www.adathedev.co.uk/2010/03/queue-table-processing-in-sql-server.html
http://rusanu.com/2010/03/26/using-tables-as-queues/
ASKER
Thanks
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?
HTH