Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
00kevin
Yak Posting Veteran
78 Posts |
 Posted - 2010-06-22 : 13:54:27
|
| Hi Everyone,I have a few tables that contain the results of a multi-stage calculation. TableStageATableStageBTableStageCetc...An application I have is updating TableStageA about twice per second.After the table is updated it needs to call a stored procedure that will perform each stage of the calculation across a total of 9 tables. Now, I don't want the application to wait for the entire process to complete before I can insert into the first table (TableStageA)What I want is for the a calculation procedure to fire in another thread. It really needs to be done asynchronously. Basically, the table needs to be updated as soon as possible and subsequent calculation may take much longer to complete then the time it takes for the next update to arrive from the app.Even if this is possible, will sql server not lock the stage tables down or will transactions take care of it? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-06-22 : 19:23:47
|
| One approach would be to load your data into a temp table. The temp table then populates TableStageA and is used for the subsequent downstream processing.Just an idea...=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-23 : 03:01:53
|
I had this same issue a few years ago and I actually ended up tuning the process that was supposed to be asynch to a point where it worked fast enough to run in synch instead. I guess it was easier for me at the time and I didn't investigate too much about alternatives...The only 2 ways I know of doing stuff asynch in sql server is to either use the service broker (but I've never used that before so I don't know exactly how it works) and to have a separate process running an eternal loop constantly checking for updates to a table. You could for example have a sql server agent job with a procedure that does something like this:WHILE (1 = 1) BEGIN IF EXISTS (SELECT 1 FROM TableStageA WHERE UpdateFlag = 1) BEGIN --> Fire off processing END ELSE WAITFOR DELAY '00:00:00.500' END You would need to build some logit making sure that this loop is actually runing though...- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
Multi-threaded procedures?