Update On Sql2000

[sanjeev]

Hi,

Performance of sqlserver2000 while running an Update on any table with huge data is very slow. ( actually the query/SP hangs). The Update runs fine if the number of rows in a table is small (Less than 100K)

When I run the same update query on the table (approx 600k rows) on a different server running sqlserver 7.0
I get the results faster

** Table to be updated has proper index
** sql7.0 ( running on win2k server say server1)
** sql2000 ( running on win2k server say server2

Any help in this matter is really appreciated.

Thanks
Sanjeev

[Anu]

Hi Sanjeev,

Did you run updatestatistics on the database after you created the index?

Thanks,
Anu

[sanjeev]

Thanks Anu.

I did run update statistics on the db.

It is happening to almost all the tables with huge number of rows.

[Anu]

Did you create index after moving to sql 2000 or you already had an index on that table before moving

Actually index makes the query to run faster, but if you have more index on the tables the insert,delete and update will become slow.

[MAK]

Give index hints on Updates.

example:

update A set A.name ='xxx'
from Table A (index = x_idx) where A.id = 3