Running updates - the quickest approach - please help.

Hi everyone,

I have to update a column with about 1.34M rows. The column is a a
primary key and hence has a clustered index associated with it. I have
to run updates o nthis column.
Would it be quicker to do the following:

1. Delete the index
2. Run the updates
3. Recreate the index

OR

1. Just run the updates and never mind the index?



Which approach would be faster? Anyone with any
views/comments/suggestions/experience thay would like to share I would
be very much appreciative.

Al.

A primary key doesn't necessarily have a clustered index.  It can be
nonclustered.  As for the update at hand, it depends.  The index could help
you locate the specific rows you are updating, thus making the query faster.
However, you'll also be updating the index, which will decrease performance.
The only way to tell is to take a copy of the table and run it both ways,
comparing the results.

Can you post your complete DDL + query?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinpub.com
.
<almurph@altavista.com> wrote in message
news:1141040040.741042.9260@i39g2000cwa.googlegroups.com...
Hi everyone,

I have to update a column with about 1.34M rows. The column is a a
primary key and hence has a clustered index associated with it. I have
to run updates o nthis column.
Would it be quicker to do the following:

1. Delete the index
2. Run the updates
3. Recreate the index

OR

1. Just run the updates and never mind the index?



Which approach would be faster? Anyone with any
views/comments/suggestions/experience thay would like to share I would
be very much appreciative.

Al.

How about
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
---Here is your update statement
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN

CHECKPOINT
END
END

SET ROWCOUNT 0
<almurph@altavista.com> wrote in message
news:1141040040.741042.9260@i39g2000cwa.googlegroups.com...
> Hi everyone,
>
> I have to update a column with about 1.34M rows. The column is a a
> primary key and hence has a clustered index associated with it. I have
> to run updates o nthis column.
> Would it be quicker to do the following:
>
> 1. Delete the index
> 2. Run the updates
> 3. Recreate the index
>
> OR
>
> 1. Just run the updates and never mind the index?
>
>
>
> Which approach would be faster? Anyone with any
> views/comments/suggestions/experience thay would like to share I would
> be very much appreciative.
>
> Al.
>