Update Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Table

Author

Topic

kurtgr
Starting Member

25 Posts

Posted - 2009-06-30 : 13:55:39

Can someone tell me if there is another way of in updating a table much faster in sql server 2005? the syntax i am using is

update customer
set branchcode = 1
where customercode <= 1110000

I ave 25072 records in the table and the lapse time is now 1:20:30

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 14:01:34

do you have any index present on customercode column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 14:04:19

try putting a covering index on branchcode ,customercode and then update

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-30 : 14:05:53

WOW! In this time I could make it without sql server in an editor

25072 records should go in seconds!
We don't know the environment.
Is the server heavily used?
Are there locks?
...
What is the table structure?
Indexes?

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 14:10:33

1. How many customers are there in the table?
2. Is there a clustered index?
3. Which indexes do exist in the table?
4. How many customers do you expect to be updated?
5. Do the updates in smaller batches so the you don't fill the log file.
6. Is CustomerCode INT or VARCHAR?

N 56°04'39.26"
E 12°55'05.63"

kurtgr
Starting Member

25 Posts

Posted - 2009-06-30 : 14:16:59

quote:
Originally posted by visakh16

try putting a covering index on branchcode ,customercode and then update

When u say cover index or you talkin about primary key

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 14:21:05

http://www.sql-server-performance.com/tips/covering_indexes_p1.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources