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
The first version took events one by one and saved them in separate transactions. That was very readable, but not very fast. As so,
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.
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
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.
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
