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

From: Mark D Powell <Mark.Powell2_at_hp.com>
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 - 08:39:55 CDT

Original text of this message