By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,836 Members | 2,062 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,836 IT Pros & Developers. It's quick & easy.

Slow return in Oracle stored procedure

100+
P: 132
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
Share this Question
Share on Google+
7 Replies


debasisdas
Expert 5K+
P: 8,127
why not use a function instead ?

since data is more ,that execution time is normal.
Feb 11 '09 #2

100+
P: 132
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
Expert 5K+
P: 8,127
are you executing that directly on the server ?
Feb 11 '09 #4

100+
P: 132
@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
Expert 100+
P: 2,367
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

100+
P: 132
Thank you. I have done as suggested and the results are excellent.
Feb 13 '09 #7

100+
P: 293
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.