| Improve Performance of SQL [message #37521] |
Tue, 12 February 2002 23:21  |
Suparna Saha
Messages: 10
Registered: October 2001
|
Junior Member |
|
|
I have two tables, t_sale_summary_monthly contains 170000 rows, t_sale_prodwise contains 3300000 rows. For a particular criteria I want to update one field of a table. I have written a SQL. But it is taking 4hours, but not complete. Only 57% is completed. I want to improve the performance of this SQL. Please Suggest any solution. Following is im SQL.
declare
i number := 0;
cursor s1 is SELECT distinct cust_code, comp_code FROM t_sale_summary_monthly WHERE year_no = 2001 and month_no = 10
FOR UPDATE;
my_cur s1%ROWTYPE
begin
for c1 in s1 loop
select nvl(sum(qty_sold_ltrs),0) into qty from t_sale_prodwise
where year_no = 2001 and month_no = 10 and
upper(cust_code) = upper(my_cur.cust_code) and upper(comp_code) = upper(my_cur.comp_code);
update t_sale_summary_monthly set qty_sold_ltrs = qty
where year_no = 2001 and month_no = 10 and
upper(cust_code) = upper(my_cur.cust_code) and upper(comp_code) = upper(my_cur.comp_code);
i := i + 1;
if i > 1000 then
commit;
i := 0;
end if;
end loop;
commit;
end;
/
primary key of those tables - comp_code, cust_code, year_no, month_no. Please reply as early as possible. Thanks.
|
|
|
|
| Re: Improve Performance of SQL [message #37542 is a reply to message #37521] |
Wed, 13 February 2002 12:51  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Recommendation: Do this in one update statement. No cursors, no row locking, no periodic commits, and no use of UPPER on your column references - this will just totally ignore your primary key index and force a full-table scan of your detail table for each summary row.
You should have no problems updating whatever portion of the 170K rows are for October 2001. If you run into a rollback segment size error, your rollback segements are just plain too small and need to be larger.
update t_sale_summary_monthly s
set qty_sold_ltrs =
(select nvl(sum(d.qty_sold_ltrs), 0)
from t_sale_prodwise d
where d.comp_code = s.comp_code
and d.cust_code = s.cust_code
and d.year_no = s.year_no
and d.month_no = s.month_no)
where year_no = 2001
and month_no = 10
Let us know how it goes...
|
|
|
|
| Re: Improve Performance of SQL [message #37550 is a reply to message #37521] |
Wed, 13 February 2002 21:18 |
Satish Shrikhande
Messages: 167
Registered: October 2001
|
Senior Member |
|
|
Try it Out
declare
i number := 0;
cursor s1 is
SELECT distinct cust_code, comp_code
FROM t_sale_summary_monthly
WHERE year_no = 2001
and month_no = 10
FOR UPDATE;
--Added By Satish Shrikhande on 14/02/2002
cursor s2(a number,b number) is
select nvl(sum(qty_sold_ltrs),0) qty from t_sale_prodwise
where year_no = 2001
and month_no = 10
and upper(cust_code) = upper(a)
and upper(comp_code) = upper(b);
cu_code number;
co_code number;
qty number;
my_cur s1%ROWTYPE;
begin
for c1 in s1 loop
cu_code:=c1.cust_code;
co_code:=c1.comp_code;
--Added By Satish Shrikhande on 14/02/2002
for z in s2(cu_code,co_code) loop
qty:=z.qtye;
update t_sale_summary_monthly
set qty_sold_ltrs = qty
where year_no = 2001
and month_no = 10
and upper(cust_code) = upper(cu_code)
and upper(comp_code) = upper(co_code);
i := i + 1;
if i > 1000 then
commit;
i := 0;
end if;
end loop;
end loop;
commit;
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
