bulk insert into database in repository pattern and unit of work with best performance

Question

i want to insert 800k row in my repository and then commit. i send every 15k record to repository and commit it but its too slow.

        foreach (var imei in command.IMEIItems) {

            var newIMEI = new IMEI(imei.IMEINumber, command.PromotionId);
            _imeiRepository.Add(newIMEI);
        }

        _unitOfWork.Commit();

my problem is about foreach loop. its too slow to add in repository. commit is fast.

my repository:

         public virtual TEntity Add(TEntity entity)
    {
        if (entity != null)
        {
            TEntity addedTEntity = ObjectSet.Add(entity); // add new item in this set
            return addedTEntity;
        }
        else
        {
            throw new ArgumentNullException();
        }

    }

     protected virtual IDbSet<TEntity> ObjectSet
    {
        get
        {
            return _unitOfWork.CreateSet<TEntity>();
        }
    }

     public virtual IDbSet<TEntity> CreateSet<TEntity>() where TEntity : 
      class
    {
        return base.Set<TEntity>();
    }

Hi @amirhossein do you need to wait for this to complete before moving to the next step? if not then look at something like Hangfire to run this in the background. Here is another link that also might help: https://stackoverflow.com/questions/24304855/how-to-design-unit-of-work-to-support-bulk-operations-and-give-more-performance. And another about thread-safety of EF: https://stackoverflow.com/questions/4455634/entity-framework-thread-safety — TsTeaTime, May 04 '19 at 06:06
@TsTeaTime yes. i use WCF at this project. i send every 15k row to this wcf service. and if i do it async, i think its may be bad. — AmirHossein, May 04 '19 at 06:12
Sigh - and what is _unitOfWork.CreateSet doing? We did not see that code either — Sir Rufo, May 04 '19 at 06:13
@SirRufo i added CreateSet method to my question. this project was developed 3 years ago. i did not know who use this pattern. i work on this project now and want solve performance problem. i think problem is ObjectSet and CreateSet not supported parallel. could you help me? — AmirHossein, May 04 '19 at 07:31
**Bulk** operations are **not** a good match for Entity Framework - and adding another level of complexity with a repository pattern makes it even worse. If you **really** want to bulk-import - then use low-level ADO.NET code directly (see the documentation on the `SqlBulkCopy` class). — marc_s, May 04 '19 at 07:47

score 0 · Answer 1 · answered May 04 '19 at 06:04

0

One suggestion, assuming that your Repo method is thread safe -

Can you try using Parallel.forconsidering your large data size. Something like this

However, if your data size is relatively small, then a Parallel.For might be less efficient, taking into account the CPU context switch overhead.

So, might be worth checking the size of the input collection and then deciding between a normal for and a parallel for,

answered May 04 '19 at 06:04

Tejas Parnerkar

201
1
5

thnaks for you answer. i think ObjectSet and CreateSet method does not support parallel. i think i have to change My Add Method.what is your opinion? – AmirHossein May 04 '19 at 07:33

Jonathan Magnan · Answer 2 · 2019-05-06T08:50:18.970

Add vs AddRange

That's a very common error to use the Add method to add multiple entities. In fact, it's the DetectChanges method that's INSANELY slow.

The Add method DetectChanges after every record added.
The AddRange method DetectChanges after all records are added.

See: Entity Framework - Performance Add

To solve your issue,

The idea is probably to add a new method AddRange in your repository. However, you will face another issue because the IDbSet doesn't have the AddRange method but the DbSet does. So maybe you will need to make a cast.

EDIT: answer comment

how cast i'll need to make?

It depend on what is, in reality, your IDbSet<TEntity>. I mean, that's not an instance of an interface, it must inherit from a class.

It probably an instance of DbSet<TEntity> so if that's the case, you only have to cast it

((DbSet<TEntity>)ObjectSet).AddRange(items);

thanks. i have added AddRange Method in my repository and As you said, this happened. how cast i'll need to make? — AmirHossein, May 06 '19 at 08:35

bulk insert into database in repository pattern and unit of work with best performance

2 Answers2