Home » RDBMS Server » Performance Tuning » Improve Performance (Merged RL) (Oracle, 10g, Windows server 2003)
Improve Performance (Merged RL) [message #444299] Sat, 20 February 2010 05:13 Go to next message
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 #444315 is a reply to message #444299] Sat, 20 February 2010 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know microsoft Sharepoint but if you can use a cursor then use it (with FIRST_ROWS(5) hint) and bulk collect the data with a limit 5.
If your user are used to see more than the first screen then bulk collect the number of screens they used to see and keep the data in local array to display them by screen.

Regards
Michel
Re: Improve Performance (Merged RL) [message #444318 is a reply to message #444315] Sat, 20 February 2010 09:20 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
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 #444322 is a reply to message #444321] Sat, 20 February 2010 09:30 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,


Quote:
Yes it is. It is the most efficient way to retrieve rows when you combine it with bulk collect.


Combining bulk collect with?


Regards,
Ritesh
Re: Improve Performance (Merged RL) [message #444323 is a reply to message #444322] Sat, 20 February 2010 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With ref cursor.

Regards
Michel
Re: Improve Performance (Merged RL) [message #444324 is a reply to message #444323] Sat, 20 February 2010 09:36 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

How can we combine ref cursor with bulk collect?

Can u please give small example using my query

Regards,
Ritesh
Re: Improve Performance (Merged RL) [message #444329 is a reply to message #444324] Sat, 20 February 2010 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The way depends on your programming language.

Regards
Michel
Re: Improve Performance (Merged RL) [message #444392 is a reply to message #444329] Sun, 21 February 2010 09:19 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

That means i have to code pl/sql inside .net

[Updated on: Sun, 21 February 2010 09:21]

Report message to a moderator

Re: Improve Performance (Merged RL) [message #444395 is a reply to message #444392] Sun, 21 February 2010 09:56 Go to previous message
Michel Cadot
Messages: 63817
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
Previous Topic: Copy history from db1 to db 2
Next Topic: Query Tuning
Goto Forum:
  


Current Time: Fri Sep 30 15:48:57 CDT 2016

Total time taken to generate the page: 0.19195 seconds