Speed up XML upload to SQL Server with EF. Improve performance by applying schema?

Tuesday, November 3, 2015

I'm trying to upload XML to SQL Server database, I use Entity Framework. I have an XML of apx. 30MB with 10k records. It's been taking minutes just to upload a small portion of this data. I read in this post - The Best Way to shred XML data into SQL Server database columns - that using schema can drastically increase performance. I have a schema file available in xsd format, how would I go about applying it? Or perhaps there is some other way to improve the performance? The simplified version of my code is below.



public ActionResult XmlLoaded() {
XDocument root = XDocument.Load("..path..");
XElement feed = root.Element("feed");
IEnumerable<XElement> items = feed.Elements("item");

foreach (XElement item in items)
{
XElement sku = item.Element("sku");
XElement brand = item.Element("brand");

Product product = new Product();

product.ProductId = (int)sku;
product.Brand = (string)brand;

_productRepo.Add(product);
_productRepo.SaveChanges();
}

0 comments:

Post a Comment