|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
 Posted - 04/16/2004 : 20:35:54
|
Easiest solution is to lock the table while you're grabbing recs but that will be slow.
Put an ID on the table and have a status
So
MainTbl
id int, Status int default 0, ....
Now the processs to get recs to process will be
set rowcount 1000
update MainTbl
set Status = @@spid
where Status = 0
set rowcount 0
Now the process only accesses the data with it's spid number for processing.
When complete it set the Status to -1.
Improvements to this.
You can detect batches that have failed and these should be dealt with before getting a new batch (maybe process row by row and so find the row in error).
Create a new table for the ID's to process - this means you only need the ID to be indexed on the main table so will probably be more efficient even though the getting of the batch will take longer.
ProcTbl
ID int, Status int
set rowcount 1000
begin tran
insert ProcTbl
select ID, @@spid
from MainTbl (tablockx)
where Status = 0
update MainTbl
set Status = @@spid
from MainTbl
join ProcTbl
on MainTbl.ID = ProcTbl.ID
where ProcTbl.Status = @@spid
commit tran
set rowcount 0
I use set rowcount rather than top so that you can use a variable and tailor it to the memory available.
This is the sort of thing I did for a telecoms rating system which rated a million calls a day and could run happily on my desktop with the batch size set smaller.
It was built so that different processes could be handled by different threads perhaps on different machines but was so efficient this was never needed so it single threaded.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
Edited by - nr on 04/16/2004 20:37:50 |
 |
|
Concurrent SELECT question
