Last post May 03, 2017 03:59 AM by kourosh23
May 01, 2017 11:39 AM|kourosh23|LINK
I am reading a large number of objects from .XML file. It takes too long right now, ie. for 12 objects takes 1 minute to save them into db with Entity Framework (EF).
As it is right now Saving 1200 objects takes 100 minutes which is not acceptable.
How can I make this saving process faster (optimized)?
May 01, 2017 11:58 AM|PatriceSc|LINK
Do you need to use EF ? If it is rather some kind of import you could use
Also 1 minute for 12 objects seems unexpectedly slow. Do you have blob columns ?
May 01, 2017 12:17 PM|kourosh23|LINK
Thank you for your answer.
But unfortunately I do not think that is the answer I am looking for!
I am using EF and I do not have any blobs.
My problem is that I have too many instances that I want to save into the db.
As I mentioned I am reading from XML file and saving into db (SQL server).
The sqlbulkcopy you suggested copies data from table to table, isn't it? that is not what I want.
I just want to know how save XML file data to SQL server db faster.
May 02, 2017 09:12 AM|Cathy Zou|LINK
The major issue with Entity Framework is without a doubt the bad performance. Making saving operations like inserting hundreds or thousands of entities is long, very long and sometimes take forever!
The reason is simple, Entity Framework doesn’t insert using batch or bulk operations but does instead a database round-trip for every record.
Entity Framework Core has partially fixed this issue by batching data. The performance is better but still far away to be optimal to insert thousands of rows in SQL Server.
There are two solutions to fix this issue, using a
third party library supporting Bulk Insert or creating your own Bulk Insert.
we will show how to bulk insert entity in SQL Server similar to the library
Entity Framework Extensionsbut way lighter and by using hard coded string/property to make it easier to understand.
May 02, 2017 11:12 AM|kourosh23|LINK
Thanks a lot Cathy.
May 02, 2017 11:16 AM|PatriceSc|LINK
No you can use whatever you want as a source using
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.writetoserverasync(v=vs.110).aspx including your own custom class that implements IDataReader. Another option would be to process this XML document server side using built-in XML
support for SQL Server.
EF is good but for imports it is IMO a bit overkill to turn XML data into objects that are then turned immediately into SQL insert statements though I suspect you do have a problem here as it seems unexpectedly slow.
May 02, 2017 12:14 PM|kourosh23|LINK
May 03, 2017 03:59 AM|kourosh23|LINK
Working with MVC, not all solutions are good enough.
This is the answer I was looking for. In case someone else needed it:
Many thanks to Jarod Ferguson