Devart Forums

Is there any reason why Entity Framework takes much longer time to execute a query vs the same query against oracle directly in TOAD?

For example, in our code, when we execute ToList() on an entity, it would take 1 minute to finish the execution. If we trace the query entity framework is executing by calling the ToTraceString() and use that query to execute in TOAD, it takes just 2 sec to finish.

Am I missing anything? How can I improve the Entity framework? Need some suggestion urgently. thanks..

Actually, the problem was in the stored proc itself. I was using the global temp table. I found another way to query my data and it's working with the existing code now. Thx for the cursor resource link anyway..

Thx for the reply.

Your code to create the Command object works. But now I have another problem getting the cursor value by calling the ExcuteReader(). See my codes below and let me know what I am doing wrong

using (OracleCommand command = ((OracleConnection) ((EntityConnection) Connection).StoreConnection).CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Stored_Proc_name";

//Parameters
OracleParameter countParam = command.CreateParameter();
countParam.ParameterName = "i_count";
countParam.Direction = ParameterDirection.Input;
countParam.OracleDbType = OracleDbType.Number;
countParam.Value = count;
command.Parameters.Add(countParam);

OracleParameter cursorParam = command.CreateParameter();
cursorParam.ParameterName = "o_resultCursor";
cursorParam.Direction = ParameterDirection.Output;
cursorParam.OracleDbType = OracleDbType.Cursor;
command.Parameters.Add(cursorParam);

if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}

OracleDataReader dr = command.ExecuteReader();

int sapId;
while (dr.Read())
{
sapId = System.Convert.ToInt32(dr[0]);
}
return dr;

}

The above code hangs on dr.Read() and eventually gets this error "ORA-08103: object no longer exists inner"

Any idea?

Is there a way I can call stored proc directly from the EntityContainer's Function Imports directly using the DbCommand(using the context's connection object) that outputs a cursor parameter? We were able to do it with other types(int, double, string, etc), but no option for cursor type when comes to create a DbParameter object. I've tried to cast the Connection.CreateCommand to OracleCommand but returned NULL.

Anybody knows another way to call an oracle stored proc to putput a cursor?