471,354 Members | 2,047 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

Slow return in Oracle stored procedure

132 100+
Hi there.

I have written a C# web service that calls an Oracle stored procedure. The SP is a simple select-max query and the table it is getting the value from has about 2.8 million rows in it. The field being MAX'd is an index field in the table. I have further ensured that the table has had an analysis and rebuild run on it to ensure good integrity but still I am finding the stored procedure takes around 3 minutes to return the MAX value.

Here are the code examples below:

In the C# the stored procedure is called as follows:

Expand|Select|Wrap|Line Numbers
  1. OracleCommand uprnComm;
  2. uprnComm = new OracleCommand("NLPGnextUPRN", conn);
  3. uprnComm.CommandType = CommandType.StoredProcedure;
  4. OracleParameter outParam = uprnComm.Parameters.Add("p_nextUPRN", OracleType.Char, 12); //12 refers to length of uprn data type
  5. outParam.Direction = ParameterDirection.Output;
  6. uprnComm.ExecuteNonQuery();
  7. string newUPRN = outParam.Value.ToString();
  8.  
And the stored procedure looks like the following:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE SADAS_MANAGER.NLPGnextUPRN (p_nextUPRN   out varchar2) IS 
  2. BEGIN
  3. select max(uprn) + 1 into p_nextUPRN from nlpg_uk where uprn < 10000000;
  4. END;
  5.  
Also I am finding that if I run the following command directly in the Oracle client, it likewise takes a long time to run:

Expand|Select|Wrap|Line Numbers
  1. select max(uprn) + 1 into p_nextUPRN from nlpg_uk where uprn < 10000000
  2.  
Any ideas please? As this is becoming so confusing so I am assuming there is some form of additional optimising that needs to be carried out on the target table.

Thank you.

M :o)
Feb 11 '09 #1
7 7703
debasisdas
8,127 Expert 4TB
why not use a function instead ?

since data is more ,that execution time is normal.
Feb 11 '09 #2
E11esar
132 100+
What I am finding though is that if I run the same stored procedure elsewhere then it is a lot more efficient, so I suppose I am wondering if there is anything "obvious" which I can check with the table structure or such, please?

Thank you.

M :o)
Feb 11 '09 #3
debasisdas
8,127 Expert 4TB
are you executing that directly on the server ?
Feb 11 '09 #4
E11esar
132 100+
@debasisdas

At the moment it is all on the same machine (laptop) but the final solution will have the web service on the same server as the database.

Thank you.

M :o)
Feb 11 '09 #5
amitpatel66
2,367 Expert 2GB
Firstly, why are you declaring a varialbe type as character in your c# code. Make it integer because the return value is going to be always integer. Change it accordingly in Oracle procedure code as well.

Second, Why are you need to create a stored procedure for simple query. Just execute the query directly in your C# instead of putting it in a seperate procedure. If you use a procedure, then your when the procedure executes a PLSQL ENGINE is called,and in turn it need to call a SQL ENGINE to execute your SELECT statement. So if you execute your SELECT directly in your C# then a call from C# to PL/SQL then from PL/SQL to SQL and back to PL/SQL can be eliminated.
Feb 13 '09 #6
E11esar
132 100+
Thank you. I have done as suggested and the results are excellent.
Feb 13 '09 #7
madankarmukta
308 256MB
Hi,

Follow the modifications suggested by AmitPatel..I completely agree him.

One more thing you can try is that.. you partition the table you are querying for the specific range of "uprn" values..

Thanks!
Feb 13 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by kristoff plasun | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.