>>Can you do a batch update updating 10K rows in a time? If each update is different. you can place the update of 10K rows into begin transaction / commit transaction to run it as a batch.
>
>Yes, I can do that. I was mostly wondering if this would be really a gain as far as optimization goes. Before doing code for that, I wanted to make sure the gain would be significant. If I only save a few fractions of a second on 10000 updates, then it would not be useful. But, if this round trip to the database server is a factor, I should see a significant improvement.
Hi Michel,
I don't know what is your algoritm.
1) Try create big SQL command:
CREATE TABLE #TMPTABLE (P1 int,F2 int)
INSERT INTO #TMPTABLE (10,1)
INSERT INTO #TMPTABLE (11,2)
INSERT INTO #TMPTABLE (12,3)
INSERT INTO #TMPTABLE (13,4)
..
INSERT INTO #TMPTABLE (1,100)
UPDATE AA SET AA.F2=AB.F2
FROM SOMETABLE AA INNER JOIN #TMPTABLE AB ON AA.P1=AB.P1
DROP TABLE #TMPTABLE
2) you can send MULTIPLE UPDATE
UPDATE SOMETABLE SET AA.F2=1
WHERE P1=10
UPDATE SOMETABLE SET AA.F2=2
WHERE P1=11