Bulk insert support?

[thomaswolff]

Hi,

What is the fastest (and recommended) way of doing bulk insert using MightyOrm?

I've tried the public override IEnumerable<T> Insert(IEnumerable<object> items) method. However, it doesn't seem very fast. As of writing this, I'm inserting 100K rows in total, and it has taken several minutes reaching 20K.

Update: It took over an hour inserting 100 000K rows (15 columns).

Any inputs on this?

Thanks,
Thomas

[mikebeaton]

Hi. It's not recommended for bulk insert - it's aimed at detecting and updating, or inserting in this case, the changes in a reasonable number (say 100's) of business objects. It works one object at a time, and there's definitely no code in Mighty (or Massive) that tries to optimise for bulk insert. I'd need to do some research myself as to what the most efficient way is to achieve this using system.data.common.

[mikebeaton]

I'm noting/reminding myself that this is something done in quite DB specific ways:

• https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy
• https://www.npgsql.org/doc/copy.html

Mighty (following Massive) is a wrapper over system.data.common, mainly; although Mighty does already add a few new features, such as cursors and Npgsql dereferencing, which aren't in system.data.common at all.

So basically, adding a bulk insert wrapper - whilst certainly possible, and not unreasonable! - is 100% a new feature which Mighty doesn't have! :-/

[thomaswolff]

Thanks for the update @mikebeaton

My investigation also concluded in using the SqlBulkCopy class for this situation, so I have opted for doing that.

Maybe this issue can be marked as an enhancement?

[mikebeaton]

Maybe this issue can be marked as an enhancement?

Happy to do that - in fact, done!

A couple of points:

• I'm currently thinking of this as a fairly niche requirement - any good business/use case/website examples of where bulk insert makes a lot of sense and is something you'd do a lot would be helpful! (To me!)
• I'd definitely want to do this (or, for it to be done) in a way which covers all the existing ADO.NET drivers which have bulk insert support
• If anyone else reading this would like/could use bulk insert support, please thumbs up here or add a comment