| Improve Performance (Merged RL) [message #444299] |
Sat, 20 February 2010 05:13  |
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
|
Senior Member |
|
|
|
Hi,
In my project microsoft Sharepoint is frontend and oracle 10g is backend.
I wrote a package consisting of ref cursor type and stored procedure
containing that ref cursor variable as out parameter which fetches
data from database when my stored procedure is called from ado.net
(using microsoft sharepoint)
End user which uses the application enters the customer name
which is in the where clause of my query (PART OF QUERY) as shown below
Where CUSTOMER_NAME Like 'user_name%';
CUSTOMER_NAME field contains Context index on it, which is working.
I'm returning only ten records from my stored procedure to sharepoint
using pseudocolumn rownum.
Now consider this scenario which is almost same as real one
Below is table creation script
Create Table Test(id Number,Name Varchar2(20));
Table Created
Below is data insertion script
Insert Into test Values(1,'SAMURAI JOSE');
1 row inserted
Insert Into test Values(2,'VIRU SHAH');
1 row inserted
Insert Into test Values(4,'VIRENDRA JOSE');
1 row inserted
Insert Into test Values(5,'VIRENDRU JAGTAP');
1 row inserted
Insert Into test Values(5,'VIRENDRPRATAP JADE');
1 row inserted
Insert Into test Values(5,'VIRESHU JADE');
1 row inserted
Insert Into test Values(6,'VIRULAN THOMAS');
1 row inserted
Insert Into test Values(6,'VIRULA KULKARNI');
1 row inserted
Insert Into test Values(5,'VIREN JADE');
1 row inserted
Insert Into test Values(7,'VIRAM JAAM');
1 row inserted
Insert Into test Values(8,'VIREE KHANNA');
1 row inserted
Insert Into test Values(5,'VIRESH JADE');
1 row inserted
Insert Into test Values(4,'JUSTIN JOSE');
1 row inserted
Insert Into test Values(4,'NESS JOSE');
1 row inserted
Insert Into test Values(4,'ROSE JOSE');
1 row inserted
Insert Into test Values(4,'JESS JOSE');
1 row inserted
Insert Into test Values(4,'KOO JOSE');
1 row inserted
Insert Into test Values(4,'KARAOKE JOSE');
1 row inserted
Insert Into test Values(4,'MIKE JOSE');
1 row inserted
Following query is on same lines with the one i used
in application.
SELECT ID,NAME
FROM (SELECT ID,NAME,ROWNUM R
FROM (SELECT ID,NAME FROM TEST
WHERE NAME LIKE 'VIR%'
ORDER BY NAME))
WHERE R BETWEEN 1 AND 5;
I'm displaying 5 records on sharepoint user interface page, when user
clicks on NEXT page link on sharepoint page i display another 5 records.
Problem is when user enters customer name it takes approximately 1.5 - 2 mins
for output to come in sharepoint UI i.e. it takes 1.5 - 2 mins for output to
come first time.
My question is that can we force oracle to execute this query to
bring only first 5 records and when the user clicks on NEXT link
query execute again to bring next 5 records without using ROWNUM
pseudocolumn.
Regards,
Ritesh
[Updated on: Sat, 20 February 2010 05:46] Report message to a moderator |
|
|
|
|
|
| Re: Improve Performance (Merged RL) [message #444318 is a reply to message #444315] |
Sat, 20 February 2010 09:20  |
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
|
Senior Member |
|
|
|
Hi Michel,
thanks for replying.
But if i use a cursor and bulk collect data then i
use a pl/sql table or nested table for that i would have to
return pl/sql table/nested table as out parameter from stored procedure.
But dont u think using ref cursor as out parameter is good
for performance.
Regards,
Ritesh
|
|
|
|
| Re: Improve Performance (Merged RL) [message #444321 is a reply to message #444318] |
Sat, 20 February 2010 09:25 |
 |
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:But if i use a cursor and bulk collect data then i
use a pl/sql table or nested table for that i would have to
return pl/sql table/nested table as out parameter from stored procedure.
No, I mean use a cursor inside your program not inside the server.
I don't know how ths product works, how you code with it, how you retrieve and so on.
So I just said how you could do it in any programming language maybe it is not possible with your tool.
Quote:But dont u think using ref cursor as out parameter is good
for performance.
Yes it is. It is the most efficient way to retrieve rows when you combine it with bulk collect.
Regards
Michel
[Updated on: Sat, 20 February 2010 09:25] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Improve Performance (Merged RL) [message #444395 is a reply to message #444392] |
Sun, 21 February 2010 09:56 |
|
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I don't know .net but if you can fetch from a query then there is most surely a way to fetch arrays of rows (which is the equivalent of bulk collect in PL/SQL) not not rows one by one.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
