Time-Consuming Update Command in Sotred Procedure

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

Time-Consuming Update Command in Sotred Procedure

Author

Topic

pockey
Starting Member

4 Posts

Posted - 2008-07-22 : 23:14:54

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:

quote:
select customer_account_id, sum(fund_value_USD) amt
into #tmp_port
from #tmp_416 t
group by customer_account_id

quote:
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:

quote:
select t1.total_portfolio, t2.amt
from #tmp_416 t1, #tmp_port t2
where t1.customer_account_id = t2.customer_account_id

I even tried to use "INNER Join", it still did not work:

quote:
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

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 could not find out the reason...Could any please help me fix this problem? Thanks a lot!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-07-22 : 23:30:01

Try creating an index on the customer_Account_id column in both tables. Also use the INNER JOIN as it is ANSI standard..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pockey
Starting Member

4 Posts

Posted - 2008-07-23 : 02:39:22

quote:
Originally posted by dinakar

Try creating an index on the customer_Account_id column in both tables. Also use the INNER JOIN as it is ANSI standard..

Thanks for your suggestion!

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

quote:
create clustered index idx_cus_acc_id
on #tmp_416(customer_account_id)

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

Also I tried the "INNER Join":

quote:
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...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 04:18:31

have you had a look at execution plan to see costly steps?

pockey
Starting Member

4 Posts

Posted - 2008-07-23 : 05:17:17

quote:
Originally posted by visakh16

have you had a look at execution plan to see costly steps?

I use the "Query Plan" in "Embarcadero DBArtisan 8.1.2", however, I could not understand the result showed as it's the first time for me to use "Query Plan"...

I show the result here, could anyone help to check? Thanks a lot!

quote:

1
2
3 QUERY PLAN FOR STATEMENT 1 (at line 1).
4
5
6 STEP 1
7 The type of query is COND.
8
9
10 QUERY PLAN FOR STATEMENT 2 (at line 3).
11
12
13 STEP 1
14 The type of query is DROP PROCEDURE.
15
16
17 QUERY PLAN FOR STATEMENT 3 (at line 4).
18
19
20 STEP 1
21 The type of query is COND.
22
23
24 QUERY PLAN FOR STATEMENT 4 (at line 5).
25
26
27 STEP 1
28 The type of query is PRINT.
29
30
31 QUERY PLAN FOR STATEMENT 5 (at line 7).
32
33
34 STEP 1
35 The type of query is PRINT.
36
37
38 QUERY PLAN FOR STATEMENT 6 (at line 0).
39
40
41 STEP 1
42 The type of query is GOTO.
43
44
45 QUERY PLAN FOR STATEMENT 7 (at line 0).
46
47
48 STEP 1
49 The type of query is GOTO.
50
51
52
53 The command executed successfully with no results returned.
54
55
56
57 QUERY PLAN FOR STATEMENT 1 (at line 1).
58
59
60 STEP 1
61 The type of query is EXECUTE.
62
63
64
65
66 QUERY PLAN FOR STATEMENT 1 (at line 1).
67
68
69 STEP 1
70 The type of query is COND.
71
72
73 QUERY PLAN FOR STATEMENT 2 (at line 2).
74
75
76 STEP 1
77 The type of query is PRINT.
78
79
80 QUERY PLAN FOR STATEMENT 3 (at line 4).
81
82
83 STEP 1
84 The type of query is PRINT.
85
86
87 QUERY PLAN FOR STATEMENT 4 (at line 0).
88
89
90 STEP 1
91 The type of query is GOTO.
92
93
94
95
96 QUERY PLAN FOR STATEMENT 1 (at line 1).
97
98
99 STEP 1
100 The type of query is GRANT.
101 TO TABLE
102 dbo.up_ASSET_SHARE_rr416
103 Using I/O Size 2 Kbytes for data pages.
104
105

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 06:35:14

quote:
Originally posted by pockey

quote:
Originally posted by visakh16

have you had a look at execution plan to see costly steps?

I use the "Query Plan" in "Embarcadero DBArtisan 8.1.2", however, I could not understand the result showed as it's the first time for me to use "Query Plan"...

I show the result here, could anyone help to check? Thanks a lot!

quote:

1
2
3 QUERY PLAN FOR STATEMENT 1 (at line 1).
4
5
6 STEP 1
7 The type of query is COND.
8
9
10 QUERY PLAN FOR STATEMENT 2 (at line 3).
11
12
13 STEP 1
14 The type of query is DROP PROCEDURE.
15
16
17 QUERY PLAN FOR STATEMENT 3 (at line 4).
18
19
20 STEP 1
21 The type of query is COND.
22
23
24 QUERY PLAN FOR STATEMENT 4 (at line 5).
25
26
27 STEP 1
28 The type of query is PRINT.
29
30
31 QUERY PLAN FOR STATEMENT 5 (at line 7).
32
33
34 STEP 1
35 The type of query is PRINT.
36
37
38 QUERY PLAN FOR STATEMENT 6 (at line 0).
39
40
41 STEP 1
42 The type of query is GOTO.
43
44
45 QUERY PLAN FOR STATEMENT 7 (at line 0).
46
47
48 STEP 1
49 The type of query is GOTO.
50
51
52
53 The command executed successfully with no results returned.
54
55
56
57 QUERY PLAN FOR STATEMENT 1 (at line 1).
58
59
60 STEP 1
61 The type of query is EXECUTE.
62
63
64
65
66 QUERY PLAN FOR STATEMENT 1 (at line 1).
67
68
69 STEP 1
70 The type of query is COND.
71
72
73 QUERY PLAN FOR STATEMENT 2 (at line 2).
74
75
76 STEP 1
77 The type of query is PRINT.
78
79
80 QUERY PLAN FOR STATEMENT 3 (at line 4).
81
82
83 STEP 1
84 The type of query is PRINT.
85
86
87 QUERY PLAN FOR STATEMENT 4 (at line 0).
88
89
90 STEP 1
91 The type of query is GOTO.
92
93
94
95
96 QUERY PLAN FOR STATEMENT 1 (at line 1).
97
98
99 STEP 1
100 The type of query is GRANT.
101 TO TABLE
102 dbo.up_ASSET_SHARE_rr416
103 Using I/O Size 2 Kbytes for data pages.
104
105

what is that? a third party tool?

pockey
Starting Member

4 Posts

Posted - 2008-07-23 : 06:49:48

It's a funciton of "Embarcadero DBArtisan 8.1.2", just a button on the GUI...
What kind of tool do you use?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources