[dba-SQLServer] If or Case or 'what'?

Stuart McLachlan stuart at lexacorp.com.pg
Wed Jun 21 16:08:44 CDT 2006


On 21 Jun 2006 at 13:17, Robert L. Stewart wrote:

> Actually, the best solution would be to generate 2 lookup tables and link
> them to the first one based on the key in the fields you are trying to do
> the case for.  It will be much faster, and easier to maintain later.
> 

As I understand the question, that's what he will be doing. The question is 
how best to generate the keys in the two fields for 80 million existing 
records since these keys depend on the vallues in varisou other fields.

I've never worked with that big a table, my largest has been about 8 
million. Maybe JC has some advise since he was doing a  lot of work on a 
similar sized one a yar or two ago.

One reason that I suggested breaking it down into a series of set based 
updates is that transactional locking for 80 million records on a single 
pass update query will create a massive overhead.  It is likely to be a lot 
quicker  overall to run 30 queries where the row count for each update is 
reduced by a factor of 20 to 30.



-- 
Stuart





More information about the dba-SQLServer mailing list