 |
|
| Computers Forum Index » Computer - Databases - Oracle (Tools) » The return cursor(SYS_REFCURSOR) is too much slow... |
|
Page 1 of 1 |
|
| Author |
Message |
| Santana... |
 Posted: Fri Sep 11, 2009 4:15 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| ddf... |
| Posted: Fri Sep 11, 2009 7:16 pm |
|
|
|
Guest
|
On Sep 11, 11:15 am, Santana <paulito.sant... at (no spam) gmail.com> wrote:
Quote: 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 (no spam) 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 (no spam) 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 (no spam) sample315.com
316 Sample316 316 Avenue
316 Suite
316 A316B Narweegy
Sping
316 narweegy... at (no spam) sample316.com
317 Sample317 317 Avenue
317 Suite
317 A317B Narweegy
Sping
317 narweegy... at (no spam) sample317.com
318 Sample318 318 Avenue
318 Suite
318 A318B Narweegy
Sping
318 narweegy... at (no spam) sample318.com
319 Sample319 319 Avenue
319 Suite
319 A319B Narweegy
Sping
319 narweegy... at (no spam) 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 (no spam) sample315.com
316 Sample316 316 Avenue
316 Suite
316 A316B Narweegy
Sping
316 narweegy... at (no spam) sample316.com
317 Sample317 317 Avenue
317 Suite
317 A317B Narweegy
Sping
317 narweegy... at (no spam) sample317.com
318 Sample318 318 Avenue
318 Suite
318 A318B Narweegy
Sping
318 narweegy... at (no spam) 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 |
|
|
| Back to top |
|
|
|
| Mark D Powell... |
| Posted: Tue Sep 29, 2009 1:39 pm |
|
|
|
Guest
|
On Sep 11, 3:16 pm, ddf <orat... at (no spam) msn.com> wrote:
Quote: On Sep 11, 11:15 am, Santana <paulito.sant... at (no spam) 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 (no spam) 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 (no spam) 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 (no spam) sample315.com
316 Sample316 316 Avenue
316 Suite
316 A316B Narweegy
Sping
316 narweegy... at (no spam) sample316.com
317 Sample317 317 Avenue
317 Suite
317 A317B Narweegy
Sping
317 narweegy... at (no spam) sample317.com
318 Sample318 318 Avenue
318 Suite
318 A318B Narweegy
Sping
318 narweegy... at (no spam) sample318.com
319 Sample319 319 Avenue
319 Suite
319 A319B Narweegy
Sping
319 narweegy... at (no spam) 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 (no spam) sample315.com
316 Sample316 316 Avenue
316 Suite
316 A316B Narweegy
Sping
316 narweegy... at (no spam) sample316.com
317 Sample317 317 Avenue
317 Suite
317 A317B Narweegy
Sping
317 narweegy... at (no spam) sample317.com
318 Sample318 318 Avenue
318 Suite
318 A318B Narweegy
Sping
318 narweegy... at (no spam) 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 -- |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sat Nov 28, 2009 10:35 am
|
|