Hi all

Using LLBLGEN 3.5, Adapter, MSSQL (2005-2012). What is the best way to achieve a high insert rate using LLBLGEN? I sometimes have to add like 10k rows. SQL Server does not parallelize inserts AFAIK, and I want to speed that up using multithreading / connection pooling / other technique.. ?

Currently, i'm doing a Parallel.ForEach:

Parallel.ForEach(items, currentItem => ItemWrapper.Add(LinkDatabaseConnection.Get(currentItem.LinkId, true), Convert(currentItem)));

Where items is a collection of items, which gets Converted to an LLBLGEN Entity. Let's simplify this for this forum thread:

SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
cs.DataSource = "test";
EntityCollection<ItemEntity> items = new EntityCollection<ItemEntity>();
for (i = 0; i<10000;i++)
{
    items.Add(new ItemEntity(i));
}

Parallel.ForEach(items, currentItem => ItemWrapper.Add(cs, currentItem));

ItemWrapper.Add is:

public static void Add(SqlConnectionStringBuilder connectionString, ItemEntity item)
        {
            try
            {
                Database.AddEntity(connectionString, item, false);
            }
            catch (ORMQueryExecutionException ex)
            {
                if (ex.InnerException is SqlException)
                {
                    Logger.Trace("Exception Number: ");
                    switch ((ex.InnerException as SqlException).Errors[0].Number)
                    {
                        case 2627:
                            Database.UpdateEntitiesDirectly(connectionString, item, new RelationPredicateBucket(ItemFields.ItemId == item.ItemId));
                            break;
                        default:
                            Logger.Trace("SQL Error Number: {0}", (ex.InnerException as SqlException).Errors[0].Number);
                            throw;
                    }
                }
                else
                {
                    throw;
                }
            }
        }

which ultimately does (Database.AddEntity<T>):

public static T AddEntity<T>(SqlConnectionStringBuilder connectionString, T entity, bool refetch)
            where T : EntityBase2, IEntity2, new()
        {
            try
            {
                using (var adapter = Connection.GetAdapter(connectionString))
                {
                    adapter.SaveEntity(entity, refetch);
                    return entity;
                }
            }
            catch (Exception e)
            {
                Logger.ErrorException("Error while saving entity", e);
                throw;
            }
        }

in this code, would KeepConnectionOpen on the DataAccessAdapter help in any way? I suppose not, as the adapter object is disposed after every SaveEntity anyway? (Which I have to do because DAA is not thread-safe)

Is there a better way to do it?

Let me know if you need more information.

Thanks for your thoughts Michel

MichelZ wrote:

What is the best way to achieve a high insert rate using LLBLGEN? I sometimes have to add like 10k rows. SQL Server does not parallelize inserts AFAIK, and I want to speed that up using multithreading / connection pooling / other technique.. ?

From my own experience, keeping the connection alive (KeepConnectionOpen) is the way to speed up batch operations, but then you need to use just one DataAccessAdapter, thus just one thread.

MichelZ wrote:

in this code, would KeepConnectionOpen on the DataAccessAdapter help in any way?

That would help definitely, if you would include more entities to save per thread/DAA.

MichelZ wrote:

I suppose not, as the adapter object is disposed after every SaveEntity anyway? (Which I have to do because DAA is not thread-safe)

That's correct. Even if you keep the connection open, it will be closed when the DAA is disposed.

MichelZ wrote:

Is there a better way to do it?

I would start evaluating whether the multi-threading approach really helps on your performance versus keep the connection open and save the collection in one transaction in one single thread (adapter.SaveEntityCollection(allEntities)). You can use tools like ORMProfiler to measure those things.

Then you can go to a middle point, where you send a bucket of entities to each thread, then save the bucket (IEntityCollection) in one DAA transaction, or just save the entities in the bucket individually keeping the connection alive. In one extreme side you would end up saving an entity per thread, like you are doing now, in the opposite case you may end up saving one collection of entities in one thread. If you adjust those variables (entitiesToSavePerThread, numberOfThreads, etc) and you monitor the behavior in ORMProfiler you may discover what would be the best configuration.

Hope that makes sense

daelmo wrote:

Then you can go to a middle point, where you send a bucket of entities to each thread, then save the bucket (IEntityCollection) in one DAA transaction, or just save the entities in the bucket individually keeping the connection alive. In one extreme side you would end up saving an entity per thread, like you are doing now, in the opposite case you may end up saving one collection of entities in one thread. If you adjust those variables (entitiesToSavePerThread, numberOfThreads, etc) and you monitor the behavior in ORMProfiler you may discover what would be the best configuration. Hope that makes sense

That makes perfect sense, haven't thought about it this way yet. Multi-threads, multi-entities it is, then

Thanks for the hint!