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
- 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();
Expand|Select|Wrap|Line Numbers
- 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;
Expand|Select|Wrap|Line Numbers
- select max(uprn) + 1 into p_nextUPRN from nlpg_uk where uprn < 10000000
Thank you.
M :o)