December 30, 2014 at 12:26 pm
let me try to explain...........
there is an application that calls a procedure say sp_BatchOrSingle(@ids nvarchar(4000))
so if @id is only one id , we need the application to wait until process ends in the back end.
but lets say if @ids is for multiple ids , we don't want the calling application to wait back end process , since its a batch process. @ids could be '1,2,3,4,5.........'. we want the application to intiate the backend process and user can continue other tasks at the front end.
HOW TO DO THIS?
thanks experts
December 30, 2014 at 2:57 pm
If you want to do it from the back end, I can think of one possibility.
Have your stored procedure add the IDs to a queuing table and process the IDs from the queue. If you only get one ID, you can return after the ID disappears from the queue. If you get more than one ID, then you can just immediately return and let the queuing process take care of everything.
Otherwise, you're going to want to do this from the front end through multi-threading.
December 31, 2014 at 6:37 am
If you get more than one ID, then you can just immediately return and let the queuing process take care of everything.
would you elaborate this more,
how can we immediatly return and at the same time procedure executes looping through the ids in Queue table.
December 31, 2014 at 7:42 am
At a general conceptual level, here's how a system like that would work. Or at least, this is how I might tackle something like this.
Create a queue table for processing.
Create a process history table, so you have an audit history of what you have processed.
What you want to do is add IDs to your queue table and have those processed. There are different ways of achieving this. You can either have a job run every few seconds to see if there is anything in the queue table to process, or you can put a trigger on the queue table that executes your processing code. (Beware of handling multiple records in your trigger. This gets a lot of people in trouble.)
At the conclusion of the processing routine, you delete the ID from the Queue table and insert it into the process history table.
So, processing isn't handled from the procedure that your front end is calling. That's being handled by what ever process is handling the queue.
As far as the front end is concerned, what it does is hand off one or many IDs to the back end procedure. That procedure inserts the ID or IDs into the queue table.
If you only received one ID, then you can keep checking your process history table for that ID to show up. (Make sure you put a maximum time to wait here, or else you could get stuck in the procedure if the processing fails in some way and your ID never makes it to the history table. I would have a wait for delay of say 1 second and increment a counter every time you check for your ID. If the counter hits 300, you've been waiting for 5 minutes and you should throw an error. Adjust for your expected processing time and maximum delay you can tolerate.) Once your ID shows up in the history table, (or you hit your maximum wait time and need to fail out) you can call your return code and the front end continues.
If the back end procedure gets more than one ID, then you just shove them all into the queue table and call your return value.
If I were doing this, I would do something like this for the back end procedure:
IF @id has only 1 ID BEGIN
INSERT INTO QueueTable (ID) VALUE @id
declare @MaxTry int = 0
WHILE not exists (select id from process history where id = @id) OR @MaxTry >= 300 BEGIN
@MaxTry = @MaxTry + 1
WAITFOR DELAY 00:00:01
END
IF @MaxTry >= 300 BEGIN
RETURN 1
END
ELSE BEGIN
RETURN 2
END
END
ELSE BEGIN
split your IDs out so you can insert them into the table
INSERT all the ids into the queue table
RETURN 3
END
So now your calling program knows whether it got a response back because the process timed out, the single id was processed, or it just inserted all your ids and hoped for the best.
January 2, 2015 at 10:51 am
This works fine , thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply