Passing UDTs from C# using Oracle.ManagedDataAccess

0
102

For few last days I’ve been working more than usual with Oracle database. When we were starting our project about a year ago, we tried to write the code as simple and readable as possible. After some time though business hit us hard with performance requirements and now we have to change some of our processes to write to the database in bulks. I was an optimist and thought that I’ll be able to send arrays of objects to the Oracle, but in reality, I had to do some exercises to achieve that, as passing UDTs from C# using Oracle.ManagedDataAccess is not supported. This article will show how to pass such types in a slightly different manner.

What do I need?

I have a project that saves events related to electric meters to the database and process them to show useful charts to the users. Saving events is connected with other database operations, but for simplicity sake, I’m going to show only necessary elements.

The first version took events one by one and saved them in separate transactions. That was very readable, but not very fast. As so, version that I’m working on now will save them in bulks.

Initially I thought that all I need is to declare custom types (UDT – user-defined type) in the database, create a stored procedure that will accept an array of such types and pass the array from C# to the DB. That way worked for the previous version of Oracle driver (Oracle.DataAccess.dll), where handy attributes like OracleObjectMappingAttribute exist. It’s also quite well described here.

Unfortunately, Oracle.ManagedDataAccess.dll doesn’t support UDTs, so I had to invent working solution from the blocks that I know and have available. Considering fact that I’m not Oracle expert, my solution may be not optimal but it definitely works. If you have some tips on how to do this better, please don’t hesitate to comment this post.

Saving events one by one

Below I’m listing C# code that was used to save events one by one.

 public long SaveNewEvent(long eventTypeDbId, long locationDbId, long actorDbId, string meterId,
            DateTime timestamp, TransactionArgs transactionArgs, string eventParameters = "")
        {
            return Execute(arguments =>
            {
                const string insertSql =
                    "insert into event\n" +
                    "(Event_Type_DbId, Location_DbId, Actor_DbId, Task_DbId, Create_Time, Event_Time, Status, Meter_External_Id, Event_Parameters)\n" +
                    "values (:Event_Type_DbId, :Location_DbId, :Actor_DbId, 0, :Create_Time, :Event_Time, :Status, :MeterId, :Event_Parameters) RETURNING ID INTO :inserted_Id";

                using (var command = new OracleCommand(insertSql, arguments.Connection as OracleConnection) { CommandType = CommandType.Text, BindByName = true, Transaction = arguments.Transaction as OracleTransaction })
                {
                    command.Parameters.Add(new OracleParameter("inserted_id", OracleDbType.Decimal, sizeof(long)) { Direction = ParameterDirection.ReturnValue });
                    command.Parameters.Add(new OracleParameter(":Event_Type_DbId", OracleDbType.Int64) { Value = eventTypeDbId });
                    command.Parameters.Add(new OracleParameter(":Location_DbId", OracleDbType.Int64) { Value = locationDbId });
                    command.Parameters.Add(new OracleParameter(":Actor_DbId", OracleDbType.Int64) { Value = actorDbId });
                    command.Parameters.Add(new OracleParameter(":Create_Time", OracleDbType.Date) { Value = _dateTimeProvider.GetCurrentTimeInUtc() });
                    command.Parameters.Add(new OracleParameter(":Event_Time", OracleDbType.Date) { Value = timestamp });
                    command.Parameters.Add(new OracleParameter(":Status", OracleDbType.Int32) { Value = 0 });
                    command.Parameters.Add(new OracleParameter(":MeterId", OracleDbType.Varchar2) { Value = meterId });
                    command.Parameters.Add(new OracleParameter(":Event_Parameters", OracleDbType.Long) { Value = eventParameters });

                    command.ExecuteNonQuery();
                    var returnedValue = command.Parameters["inserted_id"].Value;
                    if (returnedValue == null)
                        return 0;

                    var decimalValue = (OracleDecimal)returnedValue;
                    return decimalValue.IsNull ? 0 : decimalValue.ToInt64();
                }
            }, transactionArgs);
        }

Important elements of this procedure are held inside the Execute method’s parameter. insertSql parameter consists of SQL insert command that is used by OracleCommand instance a while later. The command is decorated with parameters that correspond with values of Event’s properties. When the command is executed, returnedValue holds Id of the newly inserted entry and I have to cast it to long type using a little bit strange construct at the end of the procedure.

The problem is that each time event is coming to the system, the method is invoked. It connects with the database, inserts the event and closes the connection. It’s very readable and simple, but unfortunately also very slow. After the tests we determined that it’s the connection maintenance that consumes most of the time needed to save the event. So if I will manage to save more than one event at once, the whole process should speed up.

Saving events in bulks

As I mentioned, earlier version of the Oracle driver for C# allowed sending complex objects to the database and back. Unfortunately (?) the newest one – Oracle.ManagedDataAccess prohibits that. Rationale is that it’s not very efficient method of sending data. Considering this, and a fact that my set of events have always the same number of properties, I figured out that I can send arrays of the properties instead of one complex object containing them. So instead two Events with Name and Description, I can send two arrays containing two items each – first with names, second with descriptions. It’s relatively easy to do this, the new procedure is shown below.

 public void SaveEventsBulk(BulkEvent[] eventBulk, TransactionArgs transactionArgs)
        {
            if (eventBulk.Empty())
                return;

            Execute(args =>
            {
                const string procedureText = "declare\n"
                                             + "type T_DBID_TABLE is table of event.id%type index by pls_integer;\n"
                                             + "type T_DATE_TABLE is table of event.event_time%type index by pls_integer;\n"
                                             + "type T_METEREXTID_TABLE is table of event.meter_external_id%type index by pls_integer;\n"
                                             + "type T_LOCATIONTYPE_TABLE is table of location.location_type%type index by pls_integer;\n"
                                             + "type T_LOCATIONEXTID_TABLE is table of location.external_id%type index by pls_integer;\n"
                                             + "type T_PARAMETERS_TABLE is table of event.event_parameters%type index by pls_integer;\n"
                                             + "EventTypes T_DBID_TABLE;\n"
                                             + "LocationTypes T_LOCATIONTYPE_TABLE;\n"

                                             + "ActorIds T_DBID_TABLE;\n"
                                             + "LocationExtIds T_LOCATIONEXTID_TABLE;\n"
                                             + "EventTimes T_DATE_TABLE;\n"
                                             + "MeterIds T_METEREXTID_TABLE;\n"
                                             + "EventParameters T_PARAMETERS_TABLE;\n"

                                             + "begin\n"
                                             + "EventTypes := :eventTypeCollection;\n"
                                             + "LocationExtIds := :locationExtIdsCollection;\n"
                                             + "LocationTypes := :locationTypesCollection;\n"
                                             + "ActorIds := :actorIdsCollection;\n"
                                             + "EventTimes := :eventTimesCollection;\n"
                                             + "MeterIds := :meterIdsCollection;\n"
                                             + "EventParameters := :eventParametersCollection;\n"

                                             + "forall i in ActorIds.First..ActorIds.Last\n"
                                             + "insert into event\n"
                                             + "(Event_Type_DbId, Location_DbId, Actor_DbId, Task_DbId, Create_Time, Event_Time, Status, Meter_External_Id, Event_Parameters)\n"
                                             + "values (EventTypes(i), (select l.id from location l where l.Actor_dbid = ActorIds(i) AND l.external_id = LocationExtIds(i) AND l.location_type = LocationTypes(i)), ActorIds(i), 0, :Create_Time, EventTimes(i), 0, MeterIds(i), EventParameters(i));\n"
                                             + "end;\n";

                using (var command = new OracleCommand(procedureText, args.Connection as OracleConnection)
                {
                    CommandType = CommandType.Text,
                    BindByName = true,
                    Transaction = transactionArgs.Transaction as OracleTransaction
                })
                {
                    command.Parameters.Add(new OracleParameter()
                    {
                        Size = eventBulk.Length,
                        ParameterName = ":eventTypeCollection",
                        Direction = ParameterDirection.Input,
                        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                        Value = eventBulk.Select(x => x.EventTypeDbId).ToArray(),
                        OracleDbType = OracleDbType.Int64
                    });
                    command.Parameters.Add(new OracleParameter()
                    {
                        Size = eventBulk.Length,
                        ParameterName = ":locationExtIdsCollection",
                        Direction = ParameterDirection.Input,
                        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                        Value = eventBulk.Select(x => x.LocationIdentifier.LocationExternalId).ToArray(),
                        OracleDbType = OracleDbType.Varchar2
                    });
                    command.Parameters.Add(new OracleParameter()
                    {
                        Size = eventBulk.Length,
                        ParameterName = ":locationTypesCollection",
                        Direction = ParameterDirection.Input,
                        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                        Value = eventBulk.Select(x => (int)x.LocationIdentifier.LocationType).ToArray(),
                        OracleDbType = OracleDbType.Int32
                    });
                    command.Parameters.Add(new OracleParameter()
                    {
                        Size = eventBulk.Length,
                        ParameterName = ":actorIdsCollection",
                        Direction = ParameterDirection.Input,
                        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                        Value = eventBulk.Select(x => x.ActorDbId).ToArray(),
                        OracleDbType = OracleDbType.Int64
                    });
                    command.Parameters.Add(
                        new OracleParameter(":Create_Time", OracleDbType.Date)
                        {
                            Value = _dateTimeProvider.GetCurrentTimeInUtc()
                        });
                    command.Parameters.Add(new OracleParameter()
                    {
                        Size = eventBulk.Length,
                        ParameterName = ":eventTimesCollection",
                        Direction = ParameterDirection.Input,
                        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                        Value = eventBulk.Select(x => x.EventTime).ToArray(),
                        OracleDbType = OracleDbType.Date
                    });
                    command.Parameters.Add(new OracleParameter()
                    {
                        Size = eventBulk.Length,
                        ParameterName = ":meterIdsCollection",
                        Direction = ParameterDirection.Input,
                        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                        Value = eventBulk.Select(x => x.MeterExternalId).ToArray(),
                        OracleDbType = OracleDbType.Varchar2
                    });
                    command.Parameters.Add(new OracleParameter()
                    {
                        Size = eventBulk.Length,
                        ParameterName = ":eventParametersCollection",
                        Direction = ParameterDirection.Input,
                        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                        Value = eventBulk.Select(x => x.EventParameters).ToArray(),
                        OracleDbType = OracleDbType.Varchar2
                    });

                    command.ExecuteNonQuery();
                }
                return true;
            }, transactionArgs);
        }

Procedure analysis

Now it’s totally different. procedureText contains declarations and statements that will be run on the database server side. Instead of this I can save it as stored procedure in the database itself and then the procedureText would only contain name of the procedure.

In the beginning of the text I need to declare all necessary collection types. I use column types for that and I name them with T_ prefixes. Then I declare array variables using these types. 

After begin statement, I assign collections sent from C# to the declared collections and, in the end, I use forall statement to iterate through the all elements of all collections. Instead of forall I could use loop statment, but the first is much more efficient. 

As you can see, I iterate from ActorIds.first to ActorIds.Last. All collections are of the same length (it’s very important, as main assumption here!) and I can use ‚i’ indexer to get particular elements from all arrays. 

The text is clear and now OracleCommand. This time I had to change parameters a bit. I added Size, CollectionType and Direction properties to them. Size is fixed – it’s a length of the all arrays (number of events in my case). Direction by default is set to Input

Value is tricky. It cannot be empty and must be an array (not list nor enumerable). 

:Create_Time parameter is not an array. As you can see, I can mix input parameters types (collections and single values).

Afterword

Change from saving one event per session to many(usually 100, but the number is configurable) events at once resulted in huge performance gain. It also causes some architectural issues – for example – what if one event in batch is corrupted? All of them though can be overcome, and if you only suffer from performance lack, I encourage you to check whether database isn’t the main reason and in such case try to change some processes to their batch-like equivalents.