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


Unable to find vcvarsall.bat or not found

Dear User,

If you re-installed your Visual Studio or updated it to a new version then you may loose the vcvarsall.bat file and here are quick steps to restore it:

1. Run the Visual Studio Installer

2. See the screenshot below to find the package you need

3. Click install/modify

After installed you will find it there (depending on a drive you installed your Visual Studio):

D:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Auxiliary\Build

Thank you and see you ;)


The Kotlin Language Client server crashed 5 times in the last 3 minutes. The server will not be restarted.

Hello friends,

seems like you have faced the same issue like me ;)

What I can say is that I was not able to fully solve this issue but I solved it partially at least.

So if you see this error in the Visual Studio Code:

The Kotlin Language Client server crashed 5 times in the last 3 minutes. The server will not be restarted.

then you have Kotlin extension installed and while updating it you hit this issue.

To be able to work, build and debug my app i did the following:

- uninstall the Kotlin extension

yes - not just disable but uninstall it and now you can continue working with Flutter & Dart

please note, even you have Kotlin Language extension that is more similar to the error the real problem is in the Kotlin extension itself.

If I will find out further steps to resolve it completely I will update this post.

Thank you and see you soon! :)


Gmail G Suite as SMTP server for your website

Hello friends!

Its time to share so non obviouse case as using G Suite Gmail account and SMTP server to send emails from your website.

(it seems different than using private Gmail account)

Check restrictions and rules google uses for such sending emails scheme

First step and the most time consuming is to Enable less secure applications!

Go to the G Suite Admin > Security > Less secure apps 

and enable "Allow users to manage their access to less secure apps"

Pay attention - it really may take up to 24 hours before you see this "Less secure" option available 

While you are waiting you can do some other steps.

Go to the gmail account you are going to use and click on the top-right settings icon:

and select "Settings". Go to the POP/IMAP tab and enable POP and IMAP Access options.

Next step - add an SMTP Relay rule to allow your email accounts be accessible from outside:

G Suite Admin > Apps > G Suite > Gmail > Advanced Settings > SMTP relay service

Hover over this section and click the Add button. 

Add the following rule:

Wait some time until this option is available under the G Suite Gmail (email) account and turn it on:

Final step - use the following mail configuration in your website:

      <smtp from="">
        <network host="" port="587" enableSsl="true" userName="" password="pppaaassswwwooorrrddd" />
</smtp> </mailSettings> </>

That's it! Now you can send emails :)

Thank you and see you soon!


Dropbox and Drive C - Clean disk on Windows and Windows Server

Hello friends,

You are trying to free up a few GBs on drive C? You tried to clean WinSxS folder, you have tried Disk cleanup util and even the Dism.exe but still need a few GBs of free space? ;)

You got to the right place - your problem is Dropbox.

- Go to the Program Files (x86) > Dropbox folder
- Delete all Client XX.XX folders except the latest one

You will be amaized that it will free up to 2-4 GB of space! :)

Thank you and see you ;)


Split by comma but not inside of any parentheses or brackets

Hello friends,

If you are here then you in need of the same as me - split string by comma that is not inside of any brackets or parentheses.

So to do that you need the following piece of code (C# code):

string oval = "Home, Edit, HDMI (4K, ARC), Test, Double 2.0 x1, Multi (F1, (f2-f5, f6, 58), FF, o2), ARG";
string roval = "";
int isOpen = 0;
for (int i = 0i < oval.Lengthi++)
    if (isOpen <= 0 && oval[i] == ',')
        roval += ",,";
    else if (isOpen > 0 && (oval[i] == ')' || oval[i] == ']' || oval[i] == '}'))
        roval += oval[i];
    else if ((oval[i] == '(' || oval[i] == '[' || oval[i] == '{'))
        roval += oval[i];
        roval += oval[i];

string[] split = roval.Split(",,"StringSplitOptions.RemoveEmptyEntries);

String you get to split looks like this: 

Home,, Edit,, HDMI (4K, ARC),, Test,, Double 2.0 x1,, Multi (F1, (f2-f5, f6, 58), FF, o2),, ARG

so you need just split it by double comma now.

This is fast, short and clear code that can be easily translated into any other language

Thank you and see you :)


AWS Windows Server - Restore RDP access blocked by Friewall

Hello friends,

today I'm going to share my experience on how we did restore RDP access blocked by Firewall after we blocked 3389 port by accident.

So first thing we tried was the standard steps suggested by AWS support, here they are:

1.Stop the instance i-xxyyzzaabb
Please note: it is recommended you take a snapshot or AMI of the instance prior to performing modifications for backup purposes.

I would note that if you have any data on any temporary drive attached to your instance then you will loose that data unless you will figure out how to backup it (I'm not sure if creating snapshot is backing them up but I guess - no)

2. Launch a temporary Windows EC2 Instance (20yy) in the same Availability Zone us-west-2a)

Be Sure to select exactly the same Availability Zone!

3. Detach the root volume vol-aabbccdd from the problem instance.

4. Attach the root volume to the new temporary instance (xvdf mapping is fine)
5. Login to the helper instance.
6. Download EC2Rescue on the temporary/helper instance from the following location:
7. Run EC2Rescue and select Next -> Offline Instance
8. Select the root volume vol-aabbccdd from the list of disks and select Next
9. Select Diagnose and Rescue which will provide suggestions to restore connectivity to the instance.
10. In the list of Detected Possible Issues, select all the checkboxes under windows firewall.
11. Select Next, then Rescue, and OK for the volume to be offline
12. Once EC2Rescue has completed, detach the volume from the temporary instance and re-attach the volume back to the original instance i-xxyyzzaabb as /dev/sda1. It is important to attach it back as /dev/sda1 .
13. Start the instance i-xxyyzzaabb and attempt to RDP

After two tries we were not able to get ride the Firewall and were not able to connect to Server via RDP.

So we decide to edit Register file of that problematic instance and yes we were right and managed to solve it.

Here are steps to do it:

1. Attach the volume to the temporary instance if not yet
2. Right-click the Windows toolbar button and open the Disk Management system tool

3. Find the offline disk (volume) and bring/make it online

4. Open RegEdit tool: Win+R > RegEdit > ENTER

5. Select HKEY_LOCAL_MACHINE node then File > Load Hive ...