| Simple Stored procedure takes 3 minutes to fetch results [message #110323] |
Sat, 05 March 2005 14:37  |
Rans
Messages: 11
Registered: February 2005
Location: chennai
|
Junior Member |
|
|
Hi all teaches and Experts..
I got a serious performance issue with Oracle XML DB.
I got a CITYdetails.xml that contains around 3120 records and eac record consisting of 4 simple element nodes
PREFCODE
PREFNAME
CITYCODE
CITYNAME
The stored procedure I am using for it is the simplest one and I am accessing it from a .NET webservice..
the stroed procedure is like this..
Create or replace procedure sp_getcitydetails(prefcode IN varchar2,res OUT SYS_REFCURSOR)
is
BEGIN
Open res for Select extractvalue(value(q),'//city_code') as citycode, extractvalue(value(q),'//city_name') as cityname from Citymaster cm, table (xmlsequence(extract(value(cm),'/CityMaster/City[pref_code="'||prefcode||'"]'))) q;
End;
Very simple right....
Yes..
And the result I am righting it to a DATASET in .NET
Believe me it takes 3 to 4 minutes to return the values just to fetch 221 records..and if I use SELECT DISTINCT..IT's gone..
Takes maore than 5 minutes..
This is very serious issue now and if not sorted out we may have to change to some other Database other than Oracle XML DB..
So please help me out guys as it is very crucial for me..
The questions.. let me put it like this..
1) What is the usual time XML DB takes to fetch 221 records from a file of 3000 records.
2) Can performance be improved ? If so how?
3) Is there anything wrong with the stored procedure I am using?
|
|
|
|
| Re: Simple Stored procedure takes 3 minutes to fetch results [message #110853 is a reply to message #110323] |
Thu, 10 March 2005 13:05  |
jowizzle
Messages: 3
Registered: February 2005
|
Junior Member |
|
|
|
I have a very similar problem, but in Java. A simple stored procedure returns a REF CURSOR (and one additional out parameter), ordinarily 2500 one-column rows of VARCHAR. Callings ocStmt.execute() finishes immediately, but ocStmt.getCursor(2) takes upwards of 30 minutes. I've fully optimized with oracle types, fetch sizes, column definitions, etc... I can't seem to get it going. SQL*Plus runs the stored procedure in about 20 seconds. Hope someone has a resolution...
|
|
|
|
|
|
| Re: Simple Stored procedure takes 3 minutes to fetch results [message #110901 is a reply to message #110873] |
Thu, 10 March 2005 20:45 |
Rans
Messages: 11
Registered: February 2005
Location: chennai
|
Junior Member |
|
|
No.
Nowhere I am calling a stored procedure inside another stored proc..
The best part is it doesn't even need to loop through the database to fetch those records...
Hope some Oracle expert resolves this.......
Is there any other way of fetching multiple records in oracle without using cursors?....
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
