|
Home > Archive > MS SQL Server > February 2006 > Running updates - the quickest approach - please help.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Running updates - the quickest approach - please help.
|
|
| almurph@altavista.com 2006-02-28, 8:23 pm |
| 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.
| |
| Tom Moreau 2006-02-28, 8:23 pm |
| 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.
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| 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.
>
|
|
|
|
|