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];                            
                            ...
                            ...
                            tblUpdate.Rows.Add(row);
                            ...

- 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))
                            {
                                conn.Open();
                                
                                //check if table-valued type is already exists
                                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(
@"IF TYPE_ID(N'TargetUDT') IS NULL CREATE TYPE [TargetUDT] AS TABLE
(
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;
                                    cmd.ExecuteNonQuery();
                                }

//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 :)


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



There was an error running the selected code generator

Hi friends,


there is good article about OAuth/OWIN and MVC 5 that I did read to get an idea what this all is and how to work with that. But after I created a project and went through all steps in the article I had decided to add my own tables and controllers and I did face with the following problem:

so right after I did click the add button to add my controller and to extend the ApplicationDbContext with my models I did get this error again and again.

I did try all steps that I found through the internet but non of them did help me to solve this problem. After a few hours I spent on it I had come to the following solution:

- Open your working project folder.
- Delete the BIN folder
- Delete OBJ folder
- Clean Solution, Rebuild Solution and try to Add Controller


But if you will get another error, like I got, about something like " the AppData\Temp\xxxxx.dll can not be accessed because of it is used by another process" - you will have to stop your antivirus and in my case it was free Panda Antivirus.


Now everything should work and you don't need to change from System.Data.Entity.DbSet<> to System.Data.Entity.IDbSet<>

or whatever in your code.


Thank you :)


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y