Code Optimization

Interesting things about software development and code optimization

MS SQL Batch or Bulk UPDATE and all around EF

Dear friends,

Thank you for comming and here I will show you how to make a huge bulk UPDATE in database from your C# code using Entity Framework.

My issue was about calculation prices for +50K products and then updating these product prices all at once.

Before this optimization SQL Update took more than 60 seconds ( using different approaches and of course you even should not think to do it with EF update/save method at all). After implementation of this approach we get less than 10 seconds for updating +50K products (of course it depends on hosting and environment you use). So what is that?

Table-Valued Parameters

yes, may be you have heard about it but have not understand it enough to use or just have not believed that it would really help you.

So everything is really easy - you create table, put all data you need for your update - it is +50K rows and pass it to your stored procedure that knows how to use it, here is an example:

- create table and columns

                            DataTable tblUpdate = new DataTable();
                            tblUpdate.Columns.Add("Id", typeof(int));               
                            tblUpdate.Columns.Add("DeliveryDateId", typeof(int));   
                            tblUpdate.Columns.Add("OldPrice", typeof(decimal));     
                            tblUpdate.Columns.Add("Price", typeof(decimal));        
                            tblUpdate.Columns.Add("ProductCost", typeof(decimal));  
                            tblUpdate.Columns.Add("AdditionalShippingCharge", typeof(decimal)); 
                            tblUpdate.Columns.Add("DisableBuyButton", typeof(bool));
                            tblUpdate.Columns.Add("Published", typeof(bool));       
                            tblUpdate.Columns.Add("AdminComment", typeof(string));  
                            tblUpdate.Columns.Add("UpdatedOnUtc", typeof(DateTime));

- add rows

                            object[] row = new object[10];                            

- now it is time to push it to SQL

                            var dataSettings = Core.Data.DataSettingsManager.LoadSettings();

                            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(dataSettings.DataConnectionString))
                                //check if table-valued type is already exists
                                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(
Id int not null,
DeliveryDateId int not null,
OldPrice decimal(18,4) not null,
Price decimal(18,4) not null,
ProductCost decimal(18,4) not null,
AdditionalShippingCharge decimal(18,4) not null,
DisableBuyButton bit not null,
Published bit not null,
AdminComment nvarchar(max) null,
UpdatedOnUtc datetime not null
);", conn))
                                    cmd.CommandType = CommandType.Text;

//create or update stored procedure using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand( @"CREATE OR ALTER PROCEDURE usp_UpdateAllProdPrices (@tvpUpdateProducts [TargetUDT] READONLY) AS BEGIN UPDATE dbo.Product SET Product.DeliveryDateId = ec.DeliveryDateId, Product.OldPrice = ec.OldPrice, Product.Price = ec.Price, Product.ProductCost = ec.ProductCost, Product.AdditionalShippingCharge = ec.AdditionalShippingCharge, Product.DisableBuyButton = ec.DisableBuyButton, Product.Published = ec.Published, Product.AdminComment = ec.AdminComment, Product.UpdatedOnUtc = ec.UpdatedOnUtc FROM @tvpUpdateProducts AS ec INNER JOIN dbo.Product ON dbo.Product.Id = ec.Id END", conn)) { cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //run the stored procedure and pass the all data as table-valued parameter using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand( @"usp_UpdateAllProdPrices", conn)) { System.Data.SqlClient.SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpUpdateProducts", tblUpdate); tvpParam.SqlDbType = SqlDbType.Structured; tvpParam.TypeName = "[TargetUDT]"; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.ExecuteNonQuery(); } conn.Close(); }

That's it guys enjoy your super-fast bulk update :)

Thank you and see you :)