Hello,we have an application that deals with millions of numbers and we just revamped the whole system and moved to 2008.Our application is optimized to process millions of numbers broken down to batches of 1,000 each. These batches are processed one by one. Optimized for speed to the remote serverMY Question is thus:Whats the best way to run batches in multiple threads ie. If a SP starts a process such asdeclare @batch varchar(max), @results varchar(max), @counter int, @total intset @total = select max(number) from gateway where username = @userset @counter = 0while @counter =< @totalbeginset @batch = -- selects the top 1,000, next 2,000 etc up to 100,000 or millions or recordsset @results = (select [dbo].[function_to_process_execution](@batch))set @counter = @counter + 1end
[dbo].[function_to_process_execution] is a function to process the csv sent to it and return a status code to the calling SP.The calling SP has to wait for the above code to finish execution.When we have large batches in the millions to process, whats the easiest way to execute the batches without having to loop 1,000 + times.Can this be assigned to multiple threads of execution, further breaking down the batches ?Or am I doing this wrong ? Please advicethanksEhi
Multithreading in MS SQL
Posted - 2008-10-05 : 03:57:07
