Home » SQL & PL/SQL » SQL & PL/SQL » The procedure execute very slow,how to change it!
The procedure execute very slow,how to change it! [message #194122] Wed, 20 September 2006 19:32 Go to next message
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 #194162 is a reply to message #194122] Thu, 21 September 2006 01:31 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
Hope you have index on column hh in table hfmis.yd_yhjbda !
If so then change second insert into;

DELETE yd_gy;
INSERT INTO yd_gy
SELECT a.dfny,a.hh,a.jfdl FROM hfmis.yd_yhjbda s, hfmis.yd_ls_yh_sf a
WHERE s.hh = a.hh AND s.yhsx ='大客户'; 

[Updated on: Thu, 21 September 2006 01:32]

Report message to a moderator

Re: The procedure execute very slow,how to change it! [message #194292 is a reply to message #194122] Thu, 21 September 2006 07:33 Go to previous message
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
Previous Topic: Challenge and A challenging question (merged 2 cross-posts)
Next Topic: .dat,.ctrl,log files path
Goto Forum:
  


Current Time: Sat Dec 10 22:41:21 CST 2016

Total time taken to generate the page: 0.08959 seconds