Re: The return cursor(SYS_REFCURSOR) is too much slow

From: ddf <oratune_at_msn.com>
Date: Fri, 11 Sep 2009 12:16:40 -0700 (PDT)
Message-ID: <a9915296-aef6-441f-95d4-bbcd2a306358_at_g23g2000yqh.googlegroups.com>

[Quoted] 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 Received on Fri Sep 11 2009 - 21:16:40 CEST