| The procedure execute very slow,how to change it! [message #194122] |
Wed, 20 September 2006 19:32  |
Tom_webex
Messages: 13
Registered: July 2006
Location: China
|
Junior Member |
|
|
CREATE OR REPLACE PROCEDURE "CREATE_TEMP_TABLE"
IS
BEGIN
delete yd_dy1;
insert into yd_dy1
select x.dfny,x.ydxz_id,x.bh,sum(x.dl)dl,sum(x.hs)hs from
(
select a.dfny,a.ydxz_id,a.bh,sum(a.ygdl)dl,count(*) hs from hfmis.yd_pjxx a
where a.fplx=1 group by a.dfny,a.ydxz_id,a.bh
union
select a.dfny,a.ydxz_id,a.bh,sum(a.ygdl)dl,count(*) hs from hfmis.yd_ls_pjxx a
where a.fplx=1 group by a.dfny,a.ydxz_id,a.bh)x
group by x.dfny,x.ydxz_id,x.bh;
delete yd_gy;
insert into yd_gy
select dfny,hh,jfdl from hfmis.yd_ls_yh_sf a
where exists (select 1 from hfmis.yd_yhjbda where hh=a.hh and yhsx = '大客户');
commit;
END;
/
When I execute the procdure,it is very slow,
there are 360000 rows in table hfmis.yd_pjxx and 3600000 rows in table hfmis.yd_ls_pjxx.
|
|
|
|
|
|
| Re: The procedure execute very slow,how to change it! [message #194292 is a reply to message #194122] |
Thu, 21 September 2006 07:33  |
kimant
Messages: 201
Registered: August 2006
Location: Denmark
|
Senior Member |
|
|
Weird naming of Your procedure.
- It does not create anything, only load.
Is the table truly temporary?
- This would speed things up a lot.
Can You use union ALL instead of just union?
- This way, You would not get all the rows sorted and made distinct.
Which part of the code are slow?
Remove the pl/sql, and run it in sql*plus, with "set timing on".
Br
Kim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
