July 15, 2003 at 8:03 am
wondering if have any way to do a batch processing using sql server 2000
my file is very large and the time consuming to calculate is too long, wondering if it is possible do that in batchs, but don't know how...any ideas??
July 15, 2003 at 10:06 am
What kind of "file" are you referring to? Is this a file of information to be imported? If so - bcp can batch the input. If it is a large table that needs to be processed, there are other ways to select a subset, perform the calculations, commit that set, then start again. I use SET ROWCOUNT or TOP N depending on the implementation. Sometimes I break the group by ID or date range. Depends a lot on the data involved.
Guarddata-
July 15, 2003 at 10:26 am
it's a file with huge data to be processed daily, I have around 100.000 records and I want to process like 5.000 at time how I do that?
July 15, 2003 at 11:35 am
EX: If I run Select TOP 5000 and process it
How I select the next range of 5000???
July 15, 2003 at 5:27 pm
You need to have some kind of data that shows where you are. If there is an ID, select and order by ID. The next select has a WHERE clause > the max ID of the last set. Same would be true of a Date field. If you don't have either of these, you need to tell by other data - values you are changing or even a new column which is a flag of "unprocessed". If rows get changed day after day, the flag should probably be the date they were last changed.
Remember that SELECT TOP 5000 ... ORDER BY will be slower as time goes on because it completes the select of all items in the table behind the scenes. That is why a field range is better. Something like:
SELECT @startField = MIN( MyData ), @endField = MAX( MyData) FROM
MyFile WHERE Processed IS NULL
SET @stepSize = ( @endField - @startField / 1000 )
WHILE @startField < @endField
BEGIN
BEGIN TRAN
UPDATE WHERE MyData >= @StartField AND MyData < ( @startField + @stepSize)
COMMIT TRAN
--Do not use between unless it is not a problem to update the same row twice
SET @startField = @startField + @stepSize
END
Guarddata-
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply