Slow return in Oracle stored procedure

[E11esar]

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:

OracleCommand uprnComm;
uprnComm = new OracleCommand("NLPGnextUPRN", conn);
uprnComm.CommandType = CommandType.StoredProcedure;
OracleParameter outParam = uprnComm.Parameters.Add("p_nextUPRN", OracleType.Char, 12); //12 refers to length of uprn data type
outParam.Direction = ParameterDirection.Output;
uprnComm.ExecuteNonQuery();
string newUPRN = outParam.Value.ToString();

And the stored procedure looks like the following:

CREATE OR REPLACE PROCEDURE SADAS_MANAGER.NLPGnextUPRN (p_nextUPRN   out varchar2) IS 
BEGIN
select max(uprn) + 1 into p_nextUPRN from nlpg_uk where uprn < 10000000;
END;

Also I am finding that if I run the following command directly in the Oracle client, it likewise takes a long time to run:

select max(uprn) + 1 into p_nextUPRN from nlpg_uk where uprn < 10000000

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)

[debasisdas]

why not use a function instead ?

since data is more ,that execution time is normal.

[E11esar]

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)

[debasisdas]

are you executing that directly on the server ?

[E11esar]

are you executing that directly on the server ?

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)

[amitpatel66]

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.

[E11esar]

Solution

Thank you. I have done as suggested and the results are excellent.

[madankarmukta]

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!