I hv a table with

1. >30 lac records
2. >30 indexes

need to update a few columns.. it works super slow.

Pls suggest if there exists a way to make it work faster.

If I drop the indexes, run the update statement, followed by creating the
indexes again.. is it going to help?
-R

Dropping indexes is usually problematic in a concurrent environment, but may
be able to be done when few are using the system--maybe in the middle of the
night.  If there are triggers, you should probably disable them if you can,
but that requires that nobody be on the system when the update occurs.  You
should avoid dropping the clustered index, but if you must, make sure you
recreate it first.

If you can't do the update at night, then you should make sure that you use
a set-based update.  Set-based operations almost always outperform row-based
operations because triggers only fire once, index maintenance can be
optimized (fewer page splits), and transaction logging is minimized.

Show quoteHide quote

On Sat, 18 Feb 2006 04:18:27 -0800, "Rock"
<R***@discussions.microsoft.com> wrote:
??

I don't grok "lac".

How slow is super-slow, and how fast would you like it?

Is it slow because of the updates, or is the same where clause in a
select slow, or are you updating every one of a zillion records?

Can you just try it?

If you are updating a lot of records including many fields that are
indexed, ESPECIALLY if that includes any clustered index, then yeah,
it might help to drop the NONCLUSTERED indexes. 

If you are changing a zillion keys in a clustered index, there is
likely no help for you, until and unless you change your physical
design - move the clustered index to something less volatile.

J.

Show quoteHide quote

http://en.wikipedia.org/wiki/Lakh

It means 10^5 to programmers on the Indian subcontinent, the way
Americans (and many other humans) say "million" to mean 10^6.

On 19 Feb 2006 12:03:54 -0800, rpres***@gmail.com wrote:
Lac thanks!

J.

This is a common question here. The most likely cuase for the long execution
time is transaction logging and perhaps page splitting. It sometimes seems
that the time required to log transactions increases exponentially in
relation to the number of rows being inserted, updates, deleted. Look into
implementing a looping technique for updating the table in batches of 1000
rows at a time. Also, if possible, insure that the placement of the .ldf log
file is on a seperate physical drive than the .mdf data file. This allows
SQL Server to read / write from both files simultaneously without excessive
skipping.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/7cf55639953d4c24/2ab5d2a93642940b
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c17e4fc02d7d4b5e?hl=en&



Show quoteHide quote

Toughy Normalization Problem
Timeout expired - on a simple SqlDataReader:Read() call
Is it possible to join two tables in a View/Query where the Second Table is based on a Name in the F
Primary Key Question
OBJECTPROPERTY problem