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