CODEWITHSUNDEEP
c#.netsql-server-2008-r2
Cartesius00
Cartesius00

Reputation: 24394

Recompute big table in SQL database

We are fighting this problem:

How to do that if performance is crucial? Some kind of batching?

Upvotes: 3

Views: 149

Answers (1)

usr
usr

Reputation: 171216

If performance is crucial, use SQL CLR to compute the new values. You can just use an update statement that way. SQL CLR places restrictions on what code you can use so this might not be an easy option.

There is another way if SQL CLR is not an option:

I assume there is an integer primary key. I'd do it like this:

  • request a batch of rows (10k or so)
  • compute the updates
  • send the updates
  • repeat

The trick is to keep track of which rows you already processed. Keep the ID value of the last row processed. Request a batch like this:

select top 10000 *
from T
where ID > @lastIDProcessed
order by ID

That will guarantee fast an reliable execution.

Upvotes: 2

Related Questions