Re: The return cursor(SYS_REFCURSOR) is too much slow
Date: Tue, 29 Sep 2009 06:39:55 -0700 (PDT)
Message-ID: <8d6129cb-60dc-4701-843f-2b1d27cdd2d3_at_y36g2000yqh.googlegroups.com>
On Sep 11, 3:16 pm, ddf <orat..._at_msn.com> wrote:
> On Sep 11, 11:15 am, Santana <paulito.sant..._at_gmail.com> wrote:
>
>
>
>
>
> > Hello,
> > i need your help for this situation :
>
> > I have a table :"CUSTOMER" with two thousand of records, the required
> > time to retrieve this information with a query is less than 2
> > seconds :
> > SELECT * FROM CUSTOMER
>
> > But i need get this information through a function that require more
> > than 2 MINUTES!!!
>
> > FUNCTION getCUSTOMER( ) RETURN SYS_REFCURSOR AS
> > CUR SYS_REFCURSOR;
> > BEGIN
>
> > OPEN cur FOR ' SELECT * FROM CUSTOMER ';
>
> > RETURN cur;
> > END;
>
> > There is any database parameter that i need change ?? Any suggestion,
> > please ??
>
> > Regards,
> > Santana
>
> You have problems you're not telling us about with reference to how
> you're using this function; I do not see the same performance
> 'problem' you see for 2000 records:
>
> SQL> _at_customer_ref_cur_ex
> SQL> create table customer(
> 2 cust_id number,
> 3 cust_name varchar2(40),
> 4 cust_addr1 varchar2(50),
> 5 cust_addr2 varchar2(50),
> 6 cust_pst_cd varchar2(10),
> 7 cust_cntct varchar2(40),
> 8 cust_email varchar2(80)
> 9 );
>
> Table created.
>
> Elapsed: 00:00:00.04
> SQL>
> SQL> alter table customer
> 2 add constraint customer_pk
> 3 primary key(cust_id);
>
> Table altered.
>
> Elapsed: 00:00:00.04
> SQL>
> SQL> begin
> 2 for i in 1..2000 loop
> 3 insert into customer
> 4 values (i, 'Sample'||i, i||' Avenue '||i, 'Suite
> '||i,
> 'A'||i||'B', 'Narweegy Sping '||i, 'narweegy'||i||'_at_sample'||
> i||'.com');
> 5 end loop;
> 6
> 7 commit;
> 8
> 9 end;
> 10 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.15
> SQL>
> SQL> set autotrace on
> SQL> set timing on
> SQL> set linesize 300
> SQL>
> SQL> select * from customer;
>
> CUST_ID CUST_NAME
> CUST_ADDR1
> CUST_ADDR2 CUST_PST_C
> CUST_CNTCT CUST_EMAIL
> ---------- ----------------------------------------
> --------------------------------------------------
> -------------------------------------------------- ----------
> ----------------------------------------
> --------------------------------------------------------------------------------
> 315 Sample315 315 Avenue
> 315 Suite
> 315 A315B Narweegy
> Sping
> 315 narweegy..._at_sample315.com
> 316 Sample316 316 Avenue
> 316 Suite
> 316 A316B Narweegy
> Sping
> 316 narweegy..._at_sample316.com
> 317 Sample317 317 Avenue
> 317 Suite
> 317 A317B Narweegy
> Sping
> 317 narweegy..._at_sample317.com
> 318 Sample318 318 Avenue
> 318 Suite
> 318 A318B Narweegy
> Sping
> 318 narweegy..._at_sample318.com
> 319 Sample319 319 Avenue
> 319 Suite
> 319 A319B Narweegy
> Sping
> 319 narweegy..._at_sample319.com
> ...
>
> 2000 rows selected.
>
> Elapsed: 00:00:06.39
>
> SQL>
> SQL> create or replace FUNCTION getCUSTOMER
> 2 RETURN SYS_REFCURSOR AS
> 3 cur SYS_REFCURSOR;
> 4 BEGIN
> 5
> 6
> 7 OPEN cur FOR 'SELECT * FROM CUSTOMER';
> 8
> 9
> 10 RETURN cur;
> 11 END;
> 12 /
>
> Function created.
>
> Elapsed: 00:00:00.03
> SQL>
> SQL> show errors
> No errors.
> SQL>
> SQL> variable mycur refcursor
> SQL>
> SQL> begin
> 2 select getcustomer
> 3 into :mycur
> 4 from dual;
> 5
> 6 end;
> 7 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> print mycur
>
> CUST_ID CUST_NAME
> CUST_ADDR1
> CUST_ADDR2 CUST_PST_C
> CUST_CNTCT CUST_EMAIL
> ---------- ----------------------------------------
> --------------------------------------------------
> -------------------------------------------------- ----------
> ----------------------------------------
> --------------------------------------------------------------------------------
> 315 Sample315 315 Avenue
> 315 Suite
> 315 A315B Narweegy
> Sping
> 315 narweegy..._at_sample315.com
> 316 Sample316 316 Avenue
> 316 Suite
> 316 A316B Narweegy
> Sping
> 316 narweegy..._at_sample316.com
> 317 Sample317 317 Avenue
> 317 Suite
> 317 A317B Narweegy
> Sping
> 317 narweegy..._at_sample317.com
> 318 Sample318 318 Avenue
> 318 Suite
> 318 A318B Narweegy
> Sping
> 318 narweegy..._at_sample318.com
> 319 Sample319 319 Avenue
> 319 Suite
> 319
> ...
>
> 2000 rows selected.
>
> Elapsed: 00:00:05.60
> SQL>
>
> I see no reason this should take 60 times longer to use the function.
> You need to explain how, exactly, you're using this function if you
> really want assistance.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Santana, why are you using a function instead of a stored procedure to return the cursor variable? I notice that your example has no input parameter and normally you use a function when you want to execute logic once for every column value returned in a cursor and you would use a stored procedure to return a set. I figure there could be more going on here than the example explains and the general details might be useful enough to allow someone to provide useful help.
David has already shown that your basic task should not be the issue; however, the devil is in the details.
HTH -- Mark D Powell -- Received on Tue Sep 29 2009 - 15:39:55 CEST