Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "big data batch insertion Analysis of .NET multiple databases". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Feature introduction (version 5.0.45 required)

Big data's operation of ORM performance bottleneck lies in entity transformation, and can not be implemented using conventional Sql.

When the more columns, the slower the conversion, SqlSugar will achieve the highest conversion performance and adopt the best database API

Operate the database to reach the limit of performance

Functional usage

The performance of BulkCopy is much better than the ORM framework in the existing market, and 30% faster than EFCore Efcore.Bulkextension.

BulkUpdate hoists all existing frames 2-3 times faster than EFCoreEfcore.Bulkextension

/ / insert 1 million in less than 10 seconds db.Fastest () .BulkCopy (GetList ()); / / performance is 30% faster than any existing Bulkcopy / / update db.Fastest () .BulkUpdate (GetList ()) / update all frames N times, 30 columns update db.Fastest (). BulkUpdate (GetList (), new string [] {"id"}, new string [] {"name", "time"}) / / No primary key usage / / table setting alias db.Fastest (). AS ("tableName") .BulkCopy (GetList ()) database support database API support self-increasing SqlSever full support is

MySql

Add AllowLoadLocalInfile=true to the connection string

Full support is PgSql full support No OracleBulkCopy No performance Test

Insert Scheme SqlSugar VS EFCoreEFCore.BulkExtensions

With 30 columns of 100000 data, we can see that the performance of EFCore.BulkExtensions in the first insertion is very slow. Under normal circumstances, big data's insertion is also better than the first time.

In terms of updates, SqlSugar also uses bulkCopy to update. In this respect, .NET in addition to EFCore.BulkExtensions

No related products are found (except fees), and SqlSugar is basically unique in updating the operation plan.

The disadvantage of EFCore.BulkExtensions is that it only supports SqlServer and its performance is not as good as SqlSugar

Use questions

Like Sqlite... What if you still don't support it?

A: Sqlite general insertion plus transaction is already an excellent level in the market (follow-up to strive for technical breakthroughs to support SQLITE)

Db.Insertable (insertObj) .ExecuteReturnIdentity (); / / ordinary insertion is not slow, and columns can be inserted in 10, 000 per second.

When do you use db.Fastest?

Answer: db.Fastest is mainly used for big data insert and update processing. If there are less than 1000 entries, there is no need to use it. After all, the supported API is not as rich as ordinary inserts, and it does not have many advantages in small data insertion. Db.Fastest mainly has more than 1000 powerful data processing.

Old version

The old version can be used this way, but its performance is not as good as that of db.Fastest.

/ / (3), blukCopy insertion / / is only suitable for super large data insertion or slow data insertion above, and the performance of small data insertion is not good. Not all libraries support db.Insertable (List). UseSqlServer (). ExecuteBulkCopy () db.Insertable (List). UseMySql (). ExecuteBulkCopy () / / high-version Mysql.data driver Add AllowLoadLocalInfile=true db.Insertable (List). UseOracle (). ExecuteBulkCopy () / 5.0.3.8-Preview to the connection string. This is the end of the content of ".net multi-database big data batch insertion analysis". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.