We are using asp.net mvc as web application and oracle 12C as database, 'Oracle.DataAccess.Client'(12.1.0, 64 bit version) for connecting to Oracle Database, and our asp.net MVC application runs on 64 bit machine as well.
One of our stored procedures returns a cursor as out put parameter.
Now, the same stored procedure takes little less than a second when we execute it on TOAD or SQL Developer, but when C# executes the same stored procedure it takes about 35 seconds..!!! to return back. TOAD, SQL Developer and asp.net iis server all are on my local machine and the Oracle server is on different machine.
In C# i have logged all kinds of timings, like how long it took to open a connection, on what time stamp C# is calling stored procedure and on what time it completed and came back, how long it took to convert DataSet to C# List. after observing all timing we see that following statement takes 35 seconds
OracleDataAdapter loDataAdapter = new OracleDataAdapter(loCommand);
LogInfo("fill DataTable");//logs time stamp saying start
loDataAdapter.Fill(loDataTable);
LogInfo("fill DataTable",false);//logs time stamp saying Done
loDataAdapter = null;
We thought it could be network issue, if that is the case the toad and sql developer should also take same time, so network is not an issue.
What we Tried: The only suspect i have is ODP.NET Driver, so removed all oracle client(s) and reinstalled them, No use.
Now trying to see if there is some thing i have to configure on sqlnet.ora or tns file connection ? but could not find any...
Here is the complete code
public DataTable GetDataTable(string psStoredProcedure) { LogInfo(psStoredProcedure);
//******************************************************************
//Function.........GetDataTable
//Description......Gets a data set (with the schema) from stored procedure
//Input Param......(1)Stored procedure name
// (2)Boolean to determine if we want to get the schema
//Output...........Return DataTable
//
//******************************************************************
OracleCommand loCommand = new OracleCommand();
OracleConnection loConnection = new OracleConnection(msConnectionString);
DataTable loDataTable = new DataTable();
//---Main Execution Block
try
{
if (!(loConnection.State == ConnectionState.Open))
{
LogInfo("Open Conn");
loConnection.Open();
LogInfo("Open Conn",false);
}
//---Define the SelectCommand
if (moCommand == null)
{
loCommand = new OracleCommand();
}
else
{
loCommand = moCommand;
moCommand = null;
}
loCommand.CommandText = psStoredProcedure;
loCommand.Connection = loConnection;
loCommand.CommandType = CommandType.StoredProcedure;
//---Populate the dataset
OracleDataAdapter loDataAdapter = new OracleDataAdapter(loCommand);
LogInfo("fill DataTable");
loDataAdapter.Fill(loDataTable);
LogInfo("fill DataTable",false);
loDataAdapter = null;
}
catch (Exception oExcep)
{
throw oExcep;
}
finally
{
//---Close the connection
if (loConnection.State == ConnectionState.Open)
{
LogInfo("Close");
loConnection.Close();
LogInfo("Close", false);
}
loCommand = null;
loConnection = null;
}
LogInfo(psStoredProcedure, false);
return loDataTable;}
Following is the PL/SQL In stored procedure:
CREATE OR REPLACE PROCEDURE REF_LIST_INQ (
ivID IN VARCHAR2 DEFAULT NULL,
ivAID IN VARCHAR2 DEFAULT NULL,
ivDID IN VARCHAR2 DEFAULT NULL,
ivSelection IN VARCHAR2 DEFAULT NULL,
ivStartDate IN VARCHAR2 DEFAULT NULL,
ivEndDate IN VARCHAR2 DEFAULT NULL,
ocRefList OUT ES_PACKAGE.cursorType,
ovReturnCode OUT VARCHAR2,
ovErrorMsg OUT VARCHAR2
)
AS
cmdSQL VARCHAR2(4000) := NULL;
whereCause VARCHAR2(2000) := NULL;
selectCause VARCHAR2(2000) := NULL;
BEGIN
/************
--Hundreds of lines of PL/SQL Code for
-- dynamically creating sql statements
-- and assigning it to 'selectCause' and 'whereCause'
*************************/
cmdSQL := selectCause || whereCause || ' ORDER BY SD.MODIFIED_DATE DESC, SD.REFERRAL_NUMBER';
OPEN ocRefList FOR cmdSQL;
ovReturnCode := '0';
EXCEPTION WHEN OTHERS THEN
ovErrorMsg := 'REF_LIST_INQ - ' || SUBSTR(SQLERRM,1,200);
ovReturnCode := '-1';
END REF_LIST_INQ;
/
For me this is an answer, may be if someone is facing same situation can try it out.
After researching few days, following C# statement helped me.
loCommand.FetchSize = loCommand.FetchSize * 18192;
As per my understanding (i am not good at describing, trying my level best) this works only for 64 bit version, and if we do not specify 'FetchSize' it will keep fetching predefined size (i believe 128k) of data from cursor opened at Oracle DB, it will do round trips until the data provided by the cursor is done.
So here is the complete C# code, but that one line code made a lot difference.
//******************************************************************
//Function.........GetDataTable
//Description......Gets a data set (with the schema) from stored procedure
//Input Param......(1)Stored procedure name
// (2)Boolean to determine if we want to get the schema
//Output...........Return DataTable
//
//******************************************************************
OracleCommand loCommand = new OracleCommand();
OracleConnection loConnection = new OracleConnection(msConnectionString);
DataTable loDataTable = new DataTable();
//---Main Execution Block
try
{
if (!(loConnection.State == ConnectionState.Open))
{
LogInfo("Open Conn");
loConnection.Open();
LogInfo("Open Conn",false);
}
//---Define the SelectCommand
if (moCommand == null)
{
loCommand = new OracleCommand();
}
else
{
loCommand = moCommand;
moCommand = null;
}
loCommand.CommandText = psStoredProcedure;
loCommand.Connection = loConnection;
loCommand.CommandType = CommandType.StoredProcedure;
//Here is the Hero
loCommand.FetchSize = loCommand.FetchSize * 18192;
//---Populate the dataset
OracleDataAdapter loDataAdapter = new OracleDataAdapter(loCommand);
LogInfo("fill DataTable");
loDataAdapter.Fill(loDataTable);
LogInfo("fill DataTable",false);
loDataAdapter = null;
}
catch (Exception oExcep)
{
throw oExcep;
}
finally
{
//---Close the connection
if (loConnection.State == ConnectionState.Open)
{
LogInfo("Close");
loConnection.Close();
LogInfo("Close", false);
}
loCommand = null;
loConnection = null;
}
LogInfo(psStoredProcedure, false);
return loDataTable;}