Performance issues with stored procedures

[mmatveev]

Hello,
I have two databases (MS SQL and Oracle) with the same conceptual model. They have the same functionality and both have several stored procedures that do some time critical selections and map results into entities. The stored procedures have been optimized and are executed almost same time even the Oracle is faster. But when the application calls these stored procedures it turns out that Oracle becomes slow up to 4 times. For example a simple call from sql script takes 0.17 second to execute one stored procedure and it takes almost 1 second when the entity framework is used. The expected result contains less than 100 records, each of them have 12 columns of simple types (int and datetime). It seems the most time the provider is busy with mapping results into entities. What you can suggest to enhance performance at this step?

Configuration Windows Vista, VS 2008, .NET 3.5, MS SQL Express, Oracle 10g Express, Devart Oracle DotConnect v5.60.124. All tests were performed on local PC to exclude net traffic.

[AndreyR]

There is no way to speed up the materialization process.
Are you sure the performance bottleneck is here?
Could you please send us a test project and the DDL script of the data objects you were using to reproduce the performance problem?

[mmatveev]

I'll try to send you test project this week. Last week I did some performance tests. Instead of using imported function I've created an extension method that calls the Oracle function using ADO.NET then manually create entities and datafill them. In my program it looks like this:

Code: Select all

    public partial class Entities: ObjectContext
    {
       public IEnumerable GetVariablesForQ(Nullable rootDepartment, Nullable modelVarId, Nullable period, Nullable kind, Nullable time)
        {
            OracleConnection con = new OracleConnection(_EntityConnectionString);
            Object revisionTime;
            if (time.HasValue)
            {
                revisionTime = time.Value;
            }
            else
            {
                revisionTime = DBNull.Value;
            }
            con.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = @"""GetVariablesFor""";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new OracleParameter("in_rootDepartment", OracleDbType.Number)).Value = rootDepartment.Value;
            cmd.Parameters.Add(new OracleParameter("in_modelVarId", OracleDbType.Number)).Value = modelVarId.Value;
            cmd.Parameters.Add(new OracleParameter("in_period", OracleDbType.TimeStamp)).Value = period.Value;
            cmd.Parameters.Add(new OracleParameter("in_kind", OracleDbType.Number)).Value = kind.Value;
            cmd.Parameters.Add(new OracleParameter("in_time", OracleDbType.TimeStamp)).Value = revisionTime; 
            cmd.Parameters.Add(new OracleParameter("result", OracleDbType.Cursor)).Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();
            var cursor = cmd.Parameters["result"].Value as OracleCursor;
            OracleDataReader reader = cursor.GetDataReader() as OracleDataReader;
            var lst = new List();
            while (reader.Read())
            {
                lst.Add(CreateVariable(reader));
            }
            reader.Close();
            cursor.Close();
            con.Close();
            return lst;
        }

Here Variable is a base entity class, having several descendants like DecimalVariable, StringVariable... CreateVariable method maps OracleDateReader records into entities:

Code: Select all

      private Variable CreateVariable(OracleDataReader reader)
        {
            Variable result;
            switch (reader.GetInt32(7))
            {
                case 0: result = new DecimalVariable()
                        {
                            ScalarValue = reader.GetDecimal(11)
                        }; 
                        break;
                case 1: result = new StringVariable()
                        {
                            StringValue = reader.GetString(10)
                        };                    
                        break;
                case 2: result = new VectorVariable(); break;
                default: result = new IntegerVariable()
                        {
                            IntValue = reader.GetInt32(12)
                        };
                        break;
            }
            result.Id = reader.GetInt32(0);
            result.TheDate = reader.GetDateTime(4);
            result.Kind = reader.GetInt16(5);
            result.ModifyTime = reader.GetDateTime(6);
            result.IsRecomended = reader.GetBoolean(8);
            result.Comment = reader.GetString(13);
            var entityKeyValues = new KeyValuePair[] 
            {
                new KeyValuePair("Id", result.Id)
            };
            result.EntityKey = new EntityKey("Entities.Variables", entityKeyValues);

            try
            {
                this.Attach(result);
            }
            catch (InvalidOperationException)
            {
                result = this.GetObjectByKey(result.EntityKey) as Variable;
            }
            return result;
        }

It is turn out that this method works 3 time faster than a generated one. Notice that the resulting entities are also have been attached to context and their navigation properties are accessible.

[AndreyR]

Thank you for the explanation. We will investigate the situation.
The test project will speed up our investigation, however.

[AndreyR]

We have made some performance improvements in the latest 6.10.96 build of dotConnect for Oracle.
It can be downloaded from here (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer here.