Improve Performance - Bulk, Batch Updates

[BryanJ]

Hello!

I'm using dotConnect for SQLite version 5.2.283, EntityFramework (Model First) for the data access. I am trying to improve the performance of my bulk inserts (Batch Updates) but I can't seem to see any improvements based on my changes to the configuration of the provider or the data model.

Currently I'm seeing about 1000 inserts every 10 seconds. I would appreciate any help fine tuning things or shedding some light on the limitations of SQLite.

Here is a simplified example of how I'm doing my inserts:

Code: Select all

private void InsertDataSamples(RequestCreateDataSamples request)
{
  DataCollection dataCollection = this.DataContext.DataCollections.Single(
    dc => (dc.Id == request.DataCollectionId));
    
  foreach (DataSample sample in request.DataSamples)
  {
    dataCollection.DataSamples.Add(dataSample);
  }
  
  // Save the data samples.
  this.DataContext.SaveChanges();
  // Also tried with: SaveOptions.None
}

I tried turning on Batch Updates as per the documentation:
http://www.devart.com/dotconnect/sqlite/docs/

Code: Select all

SQLiteEntityProviderConfig config = SQLiteEntityProviderConfig.Instance;  
config.DmlOptions.BatchUpdates.Enabled = true;

However, I noticed this statement about "Batch Updates Limitations" from the doc above:
"Statements with the RETURNING clause, for example, statements that update or insert entities with columns having the StoreGeneratedPattern attribute value set to Identity or Computed."

Currently the entities I am inserting do have a Int64 as the entity (primary) key and are configured to use "Identity" store generation.

I did try changing the Store Generated column to "None" but did not see any improvements.
Am I missing something?

Also I came across this in the forums: http://forums.devart.com/viewtopic.php? ... ulk#p95190

I realize that that forum is for Oracle; however it mentions an investigation to see if Batch Updates can be used with StoreGeneratedPattern of "Identity". Is this possible for SQLite?

Is there something else I should be configuring or configuring differently to improve performance?

Please let me know if you need any more information.
Thank you for your time!

[Shalex]

When using SQLite, please remember that this is a local embedded database (there is no client-server interoperation via network); that is why, the use of Batch Updates for SQLite does not always guarantee an increase in performance. In some cases, it might actually result in a performance decrease.

The corresponding documentation:
http://blog.devart.com/new-features-of- ... html#Batch
http://www.devart.com/dotconnect/sqlite ... dates.html

Please use the dbMonitor tool (documentation, download link) to compare the generated SQL in both cases (with/without batch updates).

[BryanJ]

Thanks for the response!

I did use the dbMonitor tool; it was very helpful! It confirmed that the inserts were being performed one at a time when using the entity framework. Enabling BatchUpdates did not change anything; I suspect that has to do with all of my entity keys being set to "Identity" store generated - which it says in the docs that BatchUpdates are not supported for "Identity" or "Computed" or anything returning a value.

I realize that SQLite is an embedded database, but there must be at least "some" overhead for doing the inserts one at a time. I think I remember reading somewhere that if not specified otherwise, SQLite actually treats each INSERT as a transaction. Not sure if that is the case here... Perhaps I'm still missing something?

Regardless, executing raw SQL against the db connection seems to go much faster. So for the time being (unless I have configured something wrong), I'm sticking with entity framework for the majority of operations but doing large INSERTs by executing raw SQL against the db connection.

Something like this:

Code: Select all

EntityConnection entityConnection = (EntityConnection)this.dataContext.Connection;
DbConnection connection = entityConnection.StoreConnection;
connection.Open();

using (DbCommand command = connection.CreateCommand())
{
  command.CommandText = sql;
  command.ExecuteNonQuery();
}

Please let me know if you think of anything else I can do to gain performance, as raw SQL is fast, but working with the abstraction that the entity framework provides is really nice.
I've enjoyed using dotConnect, thanks!

[Shalex]

Entity Framework is slower than a plain ADO.NET because of overhead expenses: https://msdn.microsoft.com/en-us/library/cc853327.aspx.