Fast Update(Please Help)

Question

Fast Update(Please Help)

maxyogesh2002

Ten Centuries

Points: 1062
More actions
July 16, 2009 at 9:02 am

#223603

Dear Friends
Below is my query
update Auditdata set tatcalltype='13',CDRcomment=a_sc.Providermaster_id
from auditdata_scrap1 a_sc inner join
auditdata auditdata on a_sc.id=auditdata.id
where auditdata.tatcalltype is null and a_sc.status='y'
the Above SQL Query takes 30 minutes to update
the above query contain 2 table
1)auditdata contain 15000000 rows
2)Auditdata_scrap1 contain 10480000 rows
index are defined to the columns used for the inner join and where clause
plz help me to make the SQL Query Faster
Thanx
Dear Friend,
I m attached the Execution plan of my update statement which takes lot of time
plz explain me that execution plan
while update its also update the indexes plz tell me???????????

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Also asked and answered here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129492

OP got an answer with 50% better performance; 20 minutes instead of 30.

N 56°04'39.16"
E 12°55'05.25"

LutzM SSC Guru Points: 107049 More actions · Answer 3

Did you ever consider getting a consultant to help you resolve your performance issues?

(afaik this already has been recommended in one of your numerous other threads regarding the auditdata issues...)

I strongly recommend to start searching for some performance experts in your local area that can physically have a look at your system and the procedure(s). Obviously, we just see one statement at a time...

Just out of curiosity: How much time do you have to get the overall procedure tuned and what performance gain has been achieved by now?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

maxyogesh2002 Ten Centuries Points: 1062 More actions · Answer 4

Dear Friends,

I m attached the JPEG of my Execution plan plz tell me Whats the problem in that query

why its taken lot of time to update.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

maxyogesh2002 (7/17/2009)
Dear Friends,
I m attached the JPEG of my Execution plan plz tell me Whats the problem in that query
why its taken lot of time to update.

First, I'd recommend reading the second article in my signature block about asking for help with performance issues.

Second, can't tell a thing from the jpeg you posted. As you are using SQL Server 2000 you will need to capture the text plan and post that.

Third, I agree with the others as well, you may want to see about hiring a consultant to come in and help you with the performance tuning as they can also teach you as they are working through some of your current problems.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

maxyogesh2002 (7/17/2009)
Dear Friends,
I m attached the JPEG of my Execution plan plz tell me

Please read the article I referenced. There's not enough info in a screenshot of an exec plan, plus the table definitions and index definitions are still missing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass