Please help me improve ths speed of my code that automatically insert specifications for a product

Dear kp948.

Forget about inserting using standard services. I found that it's having a good start and but slowing down quickly - after half an hour the speed is slowing down at 5 times at least. Importing 2700 products (with al the related data as product variants, specs, attributes etc. took 18 hours using even Parallel library, but I found that is Entity Framework issue and even Microsoft team now about that issue but promised to improve in in EF6)

New York wrote:

Excellent decision.
I used SqlBulkCopy as I found that inserting entity by entity in Entity Framework is very-very-very slow in inserting data, when you need to insert thousands of records.

kaunuk wrote:

You or New York wouldn't happen to have some SqlBulkCopy code that you've already implemented for nop 3.1?  Just a copy and paste to get me started in the right direction?

ok mate, just wait 2.5 hours I'll get at work where is my code.

kaunuk wrote:

Thank you, I'm looking forward to it :)

ok. here is my code written just in might be 20 minutes, so i didn't have enough time to design it properly.
you can refactor in for your needs. It's accepting list of combinations as input parameter and writing them into the database. Purpose of it - half of hour products have 136 attribute combinations, and this code cut the time of import from 3 month till 20 hours for 2500 products.


As you have Specifications attributes to insert, you definitely have to change the code, this is just a sample for you how to use SqlBulkCopy

        /// <summary>
        /// Inserts a product variant attribute combination
        /// </summary>
        /// <param name="combination">Product variant attribute combination</param>
        public virtual void InsertProductVariantAttributeCombinationBatch(List<ProductVariantAttributeCombination> combinations)
        {
            if (combinations == null)
                throw new ArgumentNullException("combination");

            //_productVariantAttributeCombinationRepository.InsertBatch(combinations);

           var rowsCount = combinations.Count;

           DataTable tbl = new DataTable();

           #region define columns
           tbl.Columns.Add(new DataColumn()
           {
               AllowDBNull = true,
               ColumnName = "Id",
               DataType = System.Type.GetType("System.Int32")
           }
               );

           tbl.Columns.Add(new DataColumn()
           {
               AllowDBNull = false,
               ColumnName = "AllowOutOfStockOrders",
               DataType = System.Type.GetType("System.Boolean")
           }
               );
           tbl.Columns.Add(new DataColumn()
          {
              AllowDBNull = false,
              ColumnName = "ProductVariantId",
              DataType = System.Type.GetType("System.Int32")
          }
              );

           tbl.Columns.Add(new DataColumn()
           {
               AllowDBNull = false,
               ColumnName = "StockQuantity",
               DataType = System.Type.GetType("System.Int32")
           }
               );

           tbl.Columns.Add(new DataColumn()
           {
               AllowDBNull = true,
               ColumnName = "AttributesXml",
               DataType = System.Type.GetType("System.String")
           }
               );
           tbl.Columns.Add(new DataColumn()
           {
               AllowDBNull = true,
               ColumnName = "Gtin",
               DataType = System.Type.GetType("System.String")
           }
               );
           tbl.Columns.Add(new DataColumn()
           {
               AllowDBNull = true,
               ColumnName = "ManufacturerPartNumber",
               DataType = System.Type.GetType("System.String")
           }

               );

           tbl.Columns.Add(new DataColumn()
           {
               AllowDBNull = true,
               ColumnName = "Sku",
               DataType = System.Type.GetType("System.String"),
           }
               );

           #endregion

           foreach (var combination in combinations)
           {
               
               var row = tbl.NewRow();
               #region set the values
               row["Id"] = 0;
               row["AllowOutOfStockOrders"] = combination.AllowOutOfStockOrders;
               row["ProductVariantId"] = combination.ProductVariantId;
               row["StockQuantity"] = combination.StockQuantity;

               if (String.IsNullOrEmpty(combination.AttributesXml))
                    row["AttributesXml"] = DBNull.Value;
               else
                    row["AttributesXml"] = combination.AttributesXml;

               if (String.IsNullOrEmpty(combination.Gtin))
                    row["Gtin"] = DBNull.Value;
               else
                    row["Gtin"] = combination.Gtin;

               if (String.IsNullOrEmpty(combination.ManufacturerPartNumber))
                    row["ManufacturerPartNumber"] = DBNull.Value;
               else
                    row["ManufacturerPartNumber"] = combination.ManufacturerPartNumber;


               if (String.IsNullOrEmpty(combination.Sku))
                    row["Sku"] = DBNull.Value;
               else
                    row["Sku"] = combination.Sku;
               #endregion
               tbl.Rows.Add(row);
               
           }
            var dataSettingsManager = new DataSettingsManager();
            var dataProviderSettings = dataSettingsManager.LoadSettings();
            string connectionString = dataProviderSettings.DataConnectionString;//new DataSettings().DataConnectionString;
            using (SqlBulkCopy copy = new SqlBulkCopy(connectionString))
            {
                copy.BatchSize = combinations.Count;
                #region map columns
                copy.ColumnMappings.Add("Id", "Id");
                copy.ColumnMappings.Add("AllowOutOfStockOrders", "AllowOutOfStockOrders");
                copy.ColumnMappings.Add("ProductVariantId", "ProductVariantId");
                copy.ColumnMappings.Add("StockQuantity", "StockQuantity");
                copy.ColumnMappings.Add("AttributesXml", "AttributesXml");
                copy.ColumnMappings.Add("Gtin", "Gtin");
                copy.ColumnMappings.Add("ManufacturerPartNumber", "ManufacturerPartNumber");
                copy.ColumnMappings.Add("Sku", "Sku");
                #endregion
                copy.DestinationTableName = "dbo.ProductVariantAttributeCombination";
                copy.WriteToServer(tbl);
            }

            //event notification
            //_eventPublisher.EntityInserted(combination);
        }

Thanks for that code, as always I will try to return the favor and share any code I write as well!

If I may bump this thread just so it gets seen again.

Here is my current gameplan:

1. instead of using _productService.InsertProduct i'll use SqlBulkCopy.  I'll loop through all products in the excel file, do the sku check to ensure no duplicates are added to the data table needed for sqlbuklcopy, then use sqlbulkcopy to perform all the product inserts.

2. create a productDictionary that holds all the product names and ids inserted from step 1 (This is done by using a foreach loop for each product in productservice).

3. use data from productDictionary to SqlBulkCopy slugs (from excel file) into urlrecordservice .. I'll have to loop through the data table we used in step 1 and go through all the slugs and match the product name from productDictionary to product name in the data table and create a new Slug Data table with the slug and product id. Then we'll do a sqlbulkcopy with the slug data table.

STOP - Do you see where I'm going with this? SqlBulkCopy then retrieve the ids needed for the next SqlBulkCopy....

4. create specificationDictionary that holds all specification names and ids (I only have 11 specifications)

5. create specificationOptionsDictionary that holds the options name and specification id it belongs to (this data is from the excel file for each product).  I can loop through every specifications field in the excel file and build a list while excluding duplicates. Then we'll use data from specificationOptionsDictionary to SqlBulkCopy options into specifications table.

6. now we need to associate the products to it's correction specifications hence ProductSpecificationAttribute.  At this point we have a specificationDictionary and a specificationOptionsDictionary and a productDictionary.  

Do you see where I'm going with this?  

SqlBulkCopy one table
use existing nop services to loop through that table and get all the ids
using those ids we can continue to the next table to perform SqlBulkcopy

rinse and repeat for category, attributes, specifications, etc.

Anyone see any flaws with this?

correct me if i'm wrong.
in first message of topic I can see

Here is my existing code which automatically inserts product specifications from a field in the excel import file. Read my comments to follow how it works.  

scenario when SqlBulqCopy will work ok i think.

Now we see there is products import involved.

You should understand that different scenario means different solution.



I'll tell you how did we do it.
we imported products with all the specs and other.
because of entity framework speed issues that took about 35-40 hours to import about 6000 products, BUT that was the best way for us at that point, to do it using NopCommerce native methods.
and the rest (hundereds thousands of customers, hundereds thousands of orders) we imported using T-SQL.
so. Analyse situation and make a decision.

please. Don't need to blame community members if you asking
(see topic "improve ths speed of my code that automatically insert specifications for a product")
and members didn't help you to solve the whole scenario with products and related data import.

scenario when SqlBulqCopy will work ok i think. 

I hope so, in theory it sounds like the correct approach.

Now we see there is products import involved. 

You should understand that different scenario means different solution. 

At this point I think anything will work better than nopcommerces native import.

I'll tell you how did we do it. 
we imported products with all the specs and other. 
because of entity framework speed issues that took about 35-40 hours to import about 6000 products, BUT that was the best way for us at that point, to do it using NopCommerce native methods. 

I wish I was in the same boat. I have to import 150,000 products and 500,000 by the end of the year lol.

and the rest (hundereds thousands of customers, hundereds thousands of orders) we imported using T-SQL. 
so. Analyse situation and make a decision.

I'm excited about the bulksqlcopy procedure and the method I described above, i have a good feeling about it.

please. Don't need to blame community members if you asking 
(see topic "improve ths speed of my code that automatically insert specifications for a product")
and members didn't help you to solve the whole scenario with products and related data import.

I would never blame the community, it's always a learning process :)

This code creates a data table and prepares the columns.  For anyone's convenience since it's tedious to type it all out.

//create a data table that will hold all of the products from the excel file
                        DataTable productsDataTable = new DataTable();

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "Id",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "ProductTypeId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "ParentGroupedProductId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "VisibleIndividually",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "Name",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "ShortDescription",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "FullDescription",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "AdminComment",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "ProductTemplateId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "VendorId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "ShowOnHomePage",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "MetaKeywords",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "MetaDescription",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "MetaTitle",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "AllowCustomerReviews",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "ApprovedRatingSum",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "NotApprovedRatingSum",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "ApprovedTotalReviews",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "NotApprovedTotalReviews",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "SubjectToAcl",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "LimitedToStores",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "Sku",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "ManufacturerPartNumber",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "Gtin",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "IsGiftCard",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "GiftCardTypeId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "RequireOtherProducts",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "RequiredProductIds",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "AutomaticallyAddRequiredProducts",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "IsDownload",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "DownloadId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "UnlimitedDownloads",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "MaxNumberOfDownloads",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "DownloadExpirationDays",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "DownloadActivationTypeId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "HasSampleDownload",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "SampleDownloadId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "HasUserAgreement",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "UserAgreementText",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "IsRecurring",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "RecurringCycleLength",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "RecurringCyclePeriodId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "RecurringTotalCycles",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "IsShipEnabled",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "IsFreeShipping",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "AdditionalShippingCharge",
                            DataType = System.Type.GetType("System.Decimal")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "IsTaxExempt",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "TaxCategoryId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "ManageInventoryMethodId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "StockQuantity",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "DisplayStockAvailability",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "DisplayStockQuantity",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "MinStockQuantity",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "LowStockActivityId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "NotifyAdminForQuantityBelow",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "BackorderModeId",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "AllowBackInStockSubscriptions",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "OrderMinimumQuantity",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "OrderMaximumQuantity",
                            DataType = System.Type.GetType("System.Int32")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "AllowedQuantities",
                            DataType = System.Type.GetType("System.String")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "DisableBuyButton",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "DisableWishlistButton",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "AvailableForPreOrder",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "CallForPrice",
                            DataType = System.Type.GetType("System.Boolean")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "Price",
                            DataType = System.Type.GetType("System.Decimal")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "OldPrice",
                            DataType = System.Type.GetType("System.Decimal")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = false,
                            ColumnName = "ProductCost",
                            DataType = System.Type.GetType("System.Decimal")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "SpecialPrice",
                            DataType = System.Type.GetType("System.Decimal")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "SpecialPriceStartDateTimeUtc",
                            DataType = System.Type.GetType("System.DateTime")
                        }
                            );

                        productsDataTable.Columns.Add(new DataColumn()
                        {
                            AllowDBNull = true,
                            ColumnName = "SpecialPriceEndDateTimeUtc",
                            DataType = System.Type.GetType("System.DateTime")
                        }
                            );