Updatea table without degrading performance

Question

Post reply

Updatea table without degrading performance

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Tara-1044200 SSCoach Points: 15951 More actions · Answer 1

How about pulling all srckey values where memkey are null into a temp table an

select srckey into #t from Emp_CA where memkey is null group by srckey

then do an update statement joining values from #t.

I want to add one more thing here... I just gave 1 memberlist table but i do have to update Emp_CA from 6 different memberlist tables from 6 other servers. do u suggest me to run a seperate update stmt for 6 tables ?

Paul White SSC Guru Points: 150467 More actions · Answer 2

Did you see the updated code?

UPDATE ECA

SET memkey = ML.memkey,

state = ML.state,

empno = ML.empno

FROM Production.Emp_CA ECA

JOIN OBBEAVER.MemberList ML

ON ML.srckey = ECA.srckey

WHERE ECA.memkey IS NULL;

You can use the temporary table approach if you wish, yes.

For the six remote servers, you could run six updates, or collect the data locally and then do one update.

I don't know enough about your environment or the detail of the problem to recommend any particular approach.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

paul

can i stil use group by SrcKey on ECA table ? does it update the same .

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

As i have to run the same update from 6 different server/databases having same table, is there a way i can loop through all in single procedure or something.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Tara-1044200 (2/18/2010)
As i have to run the same update from 6 different server/databases having same table, is there a way i can loop through all in single procedure or something.

1) just make 6 copies of the template code and modify to suit each server/database configuration. Wrap that in a sproc if you need to reuse the code and you are done.

2) Tara, you have been fighting with this for 2 days now. You could have given a consultant 1 hour with you and had this completely finished. 🙂 I bet you would have learned a lot in that hour too! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 6

@TSG*

We are getting paid for this...right?

LOL

*The SQL Guru

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi