Time-Consuming Update Command in Sotred Procedure

Question

Post reply

Time-Consuming Update Command in Sotred Procedure

pockeyfan

Old Hand

Points: 355
More actions
July 22, 2008 at 3:07 am

#77048

My stored procedure spends more than 3 minutes to run, I use getdate() and find it costs 43 sec for updating one column in a temporary table... I don't know why, please give me some advice... thanks!
I want to calculate the sum of "fund_value_USD" in table #tmp_416 and assign the value to its another column named "total_portfolio"
Here is the source code:
select customer_account_id, sum(fund_value_USD) amt
into #tmp_port
from #tmp_416 t
group by customer_account_id
update #tmp_416
set total_portfolio = t2.amt
from #tmp_416 t1, #tmp_port t2
where t1.customer_account_id = t2.customer_account_id
The above source code needs 43 sec to run...
And if I just use the "select" command as below, 3 sec is OK:
select t1.total_portfolio, t2.amt
from #tmp_416 t1, #tmp_port t2
where t1.customer_account_id = t2.customer_account_id
I could not find out the reason...Could any please help me fix this problem? Thanks a lot!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

Your update statement is just wrong.

It creates cross join of #tmp_416 to itself and updates everything with whatever.

This must work:

update t1

set total_portfolio = t2.amt

from #tmp_416 t1

INNER JOIN #tmp_port t2 ON t1.customer_account_id = t2.customer_account_id

_____________
Code for TallyGenerator

pockeyfan Old Hand Points: 355 More actions · Answer 2

Hi, Sergiy, thanks for your kind help!

Well, I did not understand your meaning of "cross join"...

And, it still did not work...

First, there is "error" message with update t1

as "t1" could not be found

Then, I change "t1" to "#tmp_416" as:

update #tmp_416

set total_portfolio = t2.amt

from #tmp_416 t1

INNER JOIN #tmp_port t2 ON t1.customer_account_id = t2.customer_account_id

No error this time, however, it still costs 43 sec to run the "update" command...

Does that mean the "cross join" problem still exits? Thanks!

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 3

I suspect something else might be wrong as Sergiy's code should have run without issue... However you might try turning on the execution plan when you run the query and seeing what operations are utilizing most of your resources. This could help in debugging.

Temp tables can cause performance issues pretty quickly. How many records are you working with? You might try experimenting with building a clustered index on customer_account_id in each of the tables first.

Sergiy SSC Guru Points: 110206 More actions · Answer 4

First, there is "error" message with update t1
as "t1" could not be found

Can you post the error message?

_____________
Code for TallyGenerator

pockeyfan Old Hand Points: 355 More actions · Answer 5

Sergiy (7/22/2008)
First, there is "error" message with update t1
as "t1" could not be found
Can you post the error message?

Here is the error message:

Number (208) Severity (16) State (1) Server (SHKUAT) Procedure (dbo.up_ASSET_SHARE_rr416) t1 not
found. Specify owner.objectname or use sp_help to check whether the object exits

pockeyfan Old Hand Points: 355 More actions · Answer 6

How many records are you working with? You might try experimenting with building a clustered index on customer_account_id in each of the tables first.

I'm working with 9453 records...

Is it because too many rows?

For tables "#tmp_416" and "#tmp_port", "customer_account_id" are both the first column.

I tried to build clustered index as below, however, still did not help:

create clustered index idx_cus_acc_id

on #tmp_416(customer_account_id)

select customer_account_id, sum(fund_value_USD) amt

into #tmp_port

from #tmp_416 t

group by customer_account_id

create clustered index idx_port_cus_acc_id

on #tmp_port(customer_account_id)

update #tmp_416

set total_portfolio = t2.amt

from #tmp_416 t1

INNER JOIN #tmp_port t2 ON t1.customer_account_id = t2.customer_account_id

It now needs 53 sec to run, even longer than before...

pockeyfan Old Hand Points: 355 More actions · Answer 7

Just now, I tried to use another SP to do the "update" command, and call it in the original SP, however, still no use...

I was confused and lost~~~

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

pockeyfan (7/22/2008)
Sergiy (7/22/2008)
First, there is "error" message with update t1
as "t1" could not be found
Can you post the error message?
Here is the error message:
Number (208) Severity (16) State (1) Server (SHKUAT) Procedure (dbo.up_ASSET_SHARE_rr416) t1 not
found. Specify owner.objectname or use sp_help to check whether the object exits

You are not posting the same code as what you are using... can't fix what we can't see... post the real code. Also, the error above should be obvious... there's something wrong with the table name you used... it's either misspelled or doesn't exist in the current database.

--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)