high cpu

Question

high cpu

Viewing 4 posts - 16 through 18 (of 18 total)

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

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 1

Jeff,

I am sorry if I have confused you.

I still stand on the same point that there are set of update statements in a stored procedure.Due to these updates, CPU is touching almost 100%.

But when I have executed one single update query and compared the old and new query ,

new query is taking high cpu than the old one

If I could tune one update statement then I can apply the same to rest of the update statements and reduce cpu issue

Please find the below query which I have modified

Update dnbatr set fax=x.fax
from de_norm_buscard_attr dnbatr
inner join view_a x on x.contractid=dnbatr.contract_id
inner join table_a pcm WITH (NOLOCK) on pcm.contract_id=dnbatr.contract_id
inner join table_b pra on pcm.pcm_party_role_id=pra.party_role_id
INNER JOIN table_c y WITH (NOLOCK) ON pra.party_phn_id=y.party_phn_id
where (y.lst_updt_dtm >='02/12/2012' OR pra.lst_updt_dtm >='02/12/2012' )

FYI,

The no of rows per table

de norm table -- 297352

view_a -- 296781

table_a -- 297347

table_b -- 450238

table_b -- 276249

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

pmadhavapeddi22 (2/12/2014)
Jeff,
I am sorry if I have confused you.
I still stand on the same point that there are set of update statements in a stored procedure.Due to these updates, CPU is touching almost 100%.
But when I have executed one single update query and compared the old and new query ,
new query is taking high cpu than the old one
If I could tune one update statement then I can apply the same to rest of the update statements and reduce cpu issue
Please find the below query which I have modified
Update dnbatr set fax=x.fax
from de_norm_buscard_attr dnbatr
inner join view_a x on x.contractid=dnbatr.contract_id
inner join table_a pcm WITH (NOLOCK) on pcm.contract_id=dnbatr.contract_id
inner join table_b pra on pcm.pcm_party_role_id=pra.party_role_id
INNER JOIN table_c y WITH (NOLOCK) ON pra.party_phn_id=y.party_phn_id
where (y.lst_updt_dtm >='02/12/2012' OR pra.lst_updt_dtm >='02/12/2012' )
FYI,
The no of rows per table
de norm table -- 297352
view_a -- 296781
table_a -- 297347
table_b -- 450238
table_b -- 276249
Thanks

How are you measuring and comparing CPU use between the new and the old?

Have you evaluated the execution plans?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 3

pmadhavapeddi22

SSCertifiable

Points: 5306

February 12, 2014 at 8:37 am

#1688396

using sql profiler

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

pmadhavapeddi22 (2/12/2014)
Jeff,
I am sorry if I have confused you.
I still stand on the same point that there are set of update statements in a stored procedure.Due to these updates, CPU is touching almost 100%.
But when I have executed one single update query and compared the old and new query ,
new query is taking high cpu than the old one
If I could tune one update statement then I can apply the same to rest of the update statements and reduce cpu issue
Please find the below query which I have modified
Update dnbatr set fax=x.fax
from de_norm_buscard_attr dnbatr
inner join view_a x on x.contractid=dnbatr.contract_id
inner join table_a pcm WITH (NOLOCK) on pcm.contract_id=dnbatr.contract_id
inner join table_b pra on pcm.pcm_party_role_id=pra.party_role_id
INNER JOIN table_c y WITH (NOLOCK) ON pra.party_phn_id=y.party_phn_id
where (y.lst_updt_dtm >='02/12/2012' OR pra.lst_updt_dtm >='02/12/2012' )
FYI,
The no of rows per table
de norm table -- 297352
view_a -- 296781
table_a -- 297347
table_b -- 450238
table_b -- 276249
Thanks

That IS the correct form for the UPDATE that you're trying to do. The next step, as Grant and others have suggested, would be to examine the execution plan especially since there's a view involved. Folks on this site can help a lot there if given enough information. Please see the second link under "Helpful Links" in my signature line below for how to do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)