>>>Hi
>>>
>>>Looking for a good practice to share a task across 3 machines.
>>>
>>>The same process is running on 3 separate machine and each process should get the tasks in batches of 20 items from a SQL server table, process each, and mark the record as done when completed.
>>>
>>>No duplication allowed.
>>>
>>>What's normally done to achieve this ?
>>>
>>>TIA
>>>John Harriss
>>
>>There are multiple ways I think. One way would be to use ntile() function to divide the data into 3 partitions. ie:
>>
>>machineNum = 1
>>text to m.lcSQL noshow
>>select * from
>>(select *,ntile(3) over(order by customerId) as batchNum from customers) allCustomers
>>where batchNum = ?m.machineNum
>>endtext
>>
>>(or have a similar partition directly on table itself on SQL server).
>>
>>Cetin
>
>Thanks Cetin
>That looks really good and so easy.
>I did a quick test and sure enough the result sets came through so will do some more testing.
>It seems that when I run the query and get say batch No1, I cannot get that batch again which is just what's needed. Is that data set permanently partitioned on the the table so it can never be got again ?
>
>If so could it be lifted ?

Hi John,
No it is not soemthing expected. There should be another reason behind it. You can get it again just like any other. It is not a permananet partitioning BTW. In a permenanet partition you use a column(s) and specify value ranges to create the partitions. It just helps to physically create smaller files. You still do your queries if it is a single file. ie:

create partition function myPartFunc (varchar(6)) as range left ('K','T')
go
create partition scheme myPartScheme 
as partition myPartFunc to (myFileGroup1, myFileGroup2, myFileGroup3)
go
create table myCustomers (CustomerId varchar(6), CompanyName varchar(100),...)
on myPartScheme (CustomerId)
go

Here what we are doing is to create myCustomers table as a partitioned table. From usage point we would use it as 'myCustomers'. However, SQL server would store any row with a CustomerID of K and smaller (since we wouldn't have a single letter customerID any customerID that starts with J and smaller letters) into 1st physical file, T and smaller into 2nd and rest in 3rd. Having smaller files mean faster retrieveal. However note that it has nothing to do directly with what you need. What I meant there was a little different.

I meant adding a calculated column to your table, such as:

create table tiles 
(rowId int identity,
myCol varchar(10),
tile as (rowId % 3) persisted)

CREATE INDEX myBatch ON tiles ( tile )

insert into tiles (mycol) select customerID from Northwind..Customers

Then you can etiher select like this (0,1,2):

select * from tiles where tile = 1 -- Batch 1
select * from tiles where tile = 2
select * from tiles where tile = 0

Or create a function:

CREATE FUNCTION ufn_Batch (@batcNum int)
RETURNS TABLE
AS
RETURN 
(
    SELECT *
    FROM tiles 
    WHERE tile = @batcNum%3
);

and select by passing batch number (1,2,3):

select * from ufn_Batch(1) -- Batch 1
select * from ufn_Batch(2)
select * from ufn_Batch(3)

Combining this with partitioning you would gain perfromance (and distributing sounds like you need it).

Also it would be a little complex but you can cache the portions of it on those machines using SQL CE. And if you are using SQL 2008 change tracking service might be helpfull (these are relatively newer and complex solutions and fit better into .Net).

Cetin