Update is slow

[NoExcuze]

I am just beginning with MS SQL Server.

I just worked on a 50.000 records, 78 variables table and wondered why selects are pretty fast (seconds) and updates take so long (4:50) while the same update-statement in SAS takes only 5 seconds. No index in both cases.
(I worked with the query analyzer). Example:
update table
set var=N'KA'
where (var is null)

Any suggestions?

Thanks
NoExcuze

 

[tlbroadbent]

There are many causes of poor performance with update queries.

1) Lack of indexes on the criteria column(s).
2) Locking/blocking that can occur when multiple users attempt to update the same row, page ot table.
3) Slow I/O when writing changes to disk.
4) Updates create transactions which must be recorded in the transaction log. Selects don't write to the trans log.
5) Poorly designed indexes, too many indexes or changes to certain index columns can cause a lot of index writing activity, movement of data and fragmetation of indexes.

See my reply about improving query speed in thread183-210207. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com