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

Thanks for all of the advice.  After looking at all possibilities, we have 
combine/modified and came up with 3 approaches.  Case statements, series of 
updates, and lookup tables.  I think he is going to start with  a 
combination of the case and the updates.  With all of the index changes he 
made...this should speed it up tremendously.  He only uses this about 6 
times a year...so the first approach that works in a reasonable amount of 
time...will end the testing.

Thanks again,

Mark A. Matte

>From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] If or Case or 'what'?
>Date: Thu, 22 Jun 2006 07:08:44 +1000
>
>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
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>