DEV Community 👩‍💻👨‍💻

Ali Kolahdoozan
Ali Kolahdoozan

Posted on

.NET 6 Performance !

.NET 6 is fast. Very fast!. Since I ❤️ .NET, I am going to show you how fast the .NET 6 is !.

Image description

In this short article or let's say an example(source code), I am going to show you how to insert 1.5 million records and then fetch, massage, and reinsert again in a very short time.

First of all, I am going to create a simple table in SQL Server 2019 as you can see below.

  • Data Base Name : BenchMarkDB
  • The table name should be : StockMarket

Image description

You can see the table structure in the picture.

Image description

The table script is provided as well.

CREATE TABLE [dbo].[StockMarket](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StockShare] [int] NULL,
    [Name] [nvarchar](50) NULL,
    [Family] [nvarchar](50) NULL,
    [State] [nvarchar](50) NULL,
    [LogTime] [datetime] NULL,
 CONSTRAINT [PK_StockMarketNormal] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Enter fullscreen mode Exit fullscreen mode

Since I am going to use a Memory-Optimized Table, you should convert your table to a Memory-Optimized Table to have the best performance result. How should/can you do that ?. Follow the picture and instruction below.

Image description
Click on the respected item, then you can see the below page.

Image description

If you click on Next, the next page should be the following page as I have shown below.

Image description

Continue clicking on Next

Image description

As you can see in the screenshot, SQL Server would create a new table and would change your current table name with an "_old" suffix to keep whatever you have already created as it is.

Image description

Click Next, then you will see the below page.

Image description

Dealing with SQL Server is almost done, click next to see the below page.

Image description

Migrate button should be clicked and you are done with SQL Server.

Image description
You should click on OK button to see the new table.

Image description

This should be the last screenshot regarding to an In-Memory optimized table creation.

Image description

As I said, SQL Server is enough. I have created a simple .NET 6 Console application and called it "EF_6_BenchMark". Then as you can see, there is a folder that I named "EF_6_Performance".

Image description

Whatever outside of this folder except the Program.cs is totally irrelevant to this sample and should be ignored.

Before start talking about coding, lets add whatever we need from Nuget. First of all, please install this Nuget package I have introduced below.

Image description
If dealing with pictures are difficult, you can copy and paste the Nuget name as I have mentioned below. I do not say install it by command, I am saying you can copy the addon name from the text below, but if you are interested to add by command, go ahead, I already provided what you need.

Install-Package EFCore.BulkExtensions -Version 6.5.6
Enter fullscreen mode Exit fullscreen mode

The next Nuget package would be this:

Image description

You need text, it is already here:

Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 7.0.0-preview.6.22329.4
Enter fullscreen mode Exit fullscreen mode

I have created a Folder and called it : Entity so lets see what is happening inside this folder. There is a simple Model/Entity related to my table. Have a look:

 public class StockMarket
    {
        [Key]
        public int ID { get; set; }
        [Column(TypeName = "varchar")]
        public string Name { get; set; }
        [Column(TypeName = "varchar")]
        public string Family { get; set; }
        public int StockShare { get; set; }
        [Column(TypeName = "varchar")]
        public string State { get; set; }
        public DateTime LogTime { get; set; }
    }
Enter fullscreen mode Exit fullscreen mode

The next folder is : DbContext that contains my DbContext and it's configurations as below.

public partial class StockMarketContext : DbContext
    {
        public StockMarketContext()
        {
        }
        public StockMarketContext(DbContextOptions<StockMarketContext> options)
            : base(options)
        {

        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=ALIKOLAHDOOZAN;Database=BenchMarkDB;Trusted_Connection=True;",
                 options => options.EnableRetryOnFailure(
                maxRetryCount: 4,
                maxRetryDelay: TimeSpan.FromSeconds(1),
                errorNumbersToAdd: new int[] { }
            ));
            base.OnConfiguring(optionsBuilder);
        }

        public virtual DbSet<StockMarket> StockMarket { get; set; } = null!;
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<StockMarket>(entity =>
            {
                entity.ToTable("StockMarket");
                entity.Property(e => e.ID).HasColumnName("ID");
                entity.Property(e => e.Name)
                    .HasMaxLength(50)
                    .IsUnicode(false);
                entity.Property(e => e.Family)
                    .HasMaxLength(100)
                    .IsUnicode(false);
                entity.Property(e => e.StockShare)
                    .IsUnicode(false);
                entity.Property(e => e.State)
                    .HasMaxLength(10)
                    .IsUnicode(false);
            });
            OnModelCreatingPartial(modelBuilder);
        }
        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
Enter fullscreen mode Exit fullscreen mode

My Connection String and all the configuration regarding my Entity is there.

The next folder is : Service. I have two subfolder inside the Service folder.

  • Interface
  • Concrete

Let's have a look at the Interface content.

public interface IStockMarketService
    {
        Task BulkInsertAsync(int NumberOfRows, string state);
        void BulkUpdate(int NumberOfRows);
        void BulkDelete(int NumberOfRows);
        Task BulkInsertfromlist(IEnumerable<StockMarket> Data);
        List<StockMarket> LoadStockBasedOnState(string State);
        Task<IEnumerable<StockMarket>> LoadStockBasedOnStateByRawSQL(string State);
    }
Enter fullscreen mode Exit fullscreen mode

Image description

As you can see, I have to implement the interface but before doing that, let's have a look at the scenario I am going to implement.

My Entity has a State field. This means my Table in the Database also has a State Column. If you look at BulkInsertAsync method inside my Interface, the method has two input parameter. NumberOfRows and state so after implementing the Interface, I can insert as many as record as I wish for each State by the NumberOfRows and state Input parameter(My Plan is 150_000 row for each state) so this means , by having 10 different state, I can insert 1.5 million row and then I will fetch the whole data belonging to each state, will change the StockShare entity value and then , I will reinsert the row into the table so at the end of the story, I would have 3 million row in my SQL table. As I have tried, doing this by .NET 6 is very very fast !. Let's come back to code and see what is happening in the Service implementation.

 public class StockMarketService : IStockMarketService
    {
        public async Task BulkInsertAsync(int NumberOfRows, string state)
        {
            var _StockMarketContext = new StockMarketContext();
            var stockMarkets = Enumerable.Range(0, NumberOfRows).Select(i => new StockMarket
            {
                Name = i.ToString(),
                Family = i.ToString(),
                State = state,
                StockShare = i,
                LogTime=DateTime.Now
            }).ToList();
            await _StockMarketContext.BulkInsertAsync(stockMarkets);
        }

        public async Task BulkInsertfromlist(IEnumerable<StockMarket> Data)
        {
            var _StockMarketContext = new StockMarketContext();
            await _StockMarketContext.BulkInsertAsync(Data.ToList());
        }

        public async Task<IEnumerable<StockMarket>> LoadStockBasedOnStateByRawSQL(string State)
        {
            var _StockMarketContext = new StockMarketContext();
            var data = await _StockMarketContext.StockMarket
                                           .FromSqlRaw("SELECT [ID],[StockShare],[Name],[Family],[State],[LogTime] FROM [dbo].[StockMarket] Where [State] = {0}", State)
                                           .AsNoTracking().ToArrayAsync();
            return data;
        }


        public List<StockMarket> LoadStockBasedOnState(string State)
        {
            var _StockMarketContext = new StockMarketContext();
            var data = _StockMarketContext.StockMarket
                .Where(x=>x.State==State)
                .AsNoTracking().ToList();
            return data;
        }

        public void BulkDelete(int NumberOfRows)
        {
            throw new NotImplementedException();
        }



        public void BulkUpdate(int NumberOfRows)
        {
            throw new NotImplementedException();
        }

        //public Array LoadStockBasedOnStateCompiledQuery(string State)
        //{
        //    using (var db = new StockMarketContext())
        //    {
        //        var Data = ListOfStockBasedOnStateByCompiledQuery(db, State);
        //        return Data;
        //    }
        //}


        //public List<StockMarket> LoadStockBasedOnStateByRawSQL(string State)
        //{
        //    StockMarketContext _StockMarketContext = new StockMarketContext();
        //    var data = _StockMarketContext.StockMarket.FromSqlRaw("SELECT [ID],[StockShare],[Name],[Family],[State] FROM[dbo].[StockMarket] Where [State] = {0}", State)
        //        .AsNoTracking()
        //        .ToList();
        //    return data;
        //}

        //private static Func<StockMarketContext, string, Array> ListOfStockBasedOnStateByCompiledQuery =
        //     EF.CompileQuery((StockMarketContext db, string State) => db.StockMarket
        //    .Where(x => x.State == State)
        //    .AsNoTracking().ToArray());

    }
Enter fullscreen mode Exit fullscreen mode

I am going to use the service inside the program.cs to insert, massage and reinsert the rows for each state. Since I need to observe the performance and what is happening during the runtime, I have created a Helper folder that contains a very simple custom logger like the code below:

public static class CustomLogger
    {
        static Dictionary<DateTime, string> ListOfTimes = new();
        public static void SaveTime(string Message)
        {
            Console.WriteLine($"{DateTime.Now:HH:mm:ss.fffffff} : {Message}");
            ListOfTimes.Add(DateTime.Now,Message);
        }

        public static void SaveToFile()
        {
            var sb = new StringBuilder();
            ListOfTimes.ToList().ForEach(kvp => sb.AppendLine($"{kvp.Key:HH:mm:ss.fffffff} : {kvp.Value}"));
            var LogWriter = System.IO.File.CreateText(@"C:\temp\BenchMarkLogs.txt");
            LogWriter.Write(sb.ToString());
            LogWriter.Close();
        }

    }
}
Enter fullscreen mode Exit fullscreen mode

The next is my program.cs !. First of all, I should remind you that we are going to deal with 10 state!.

string[] States = {
    "Alabama",
    "Alaska",
    "Arizona",
    "Arkansas",
    "California",
    "Colorado",
    "Connecticut",
    "Delaware",
    "Florida",
    "Georgia"
};
Enter fullscreen mode Exit fullscreen mode

This is my entire program.cs

using EF_6_BenchMark.EF_6_Performance.Entity;
using EF_6_BenchMark.EF_6_Performance.Helper;
using EF_6_BenchMark.EF_6_Performance.Service.Concrete;
using System.Diagnostics;



//Start Time
CustomLogger.SaveTime("Main Thread: Application has just been Started...");

Stopwatch stw = new Stopwatch();
IEnumerable<StockMarket> data;
StockMarketService Sr = new StockMarketService();
const int Row_Numbers = 150_000;


string[] States = {
    "Alabama",
    "Alaska",
    "Arizona",
    "Arkansas",
    "California",
    "Colorado",
    "Connecticut",
    "Delaware",
    "Florida",
    "Georgia"
};



stw.Start();
CustomLogger.SaveTime("Main Thread: StopWatch has just been Started...");



await Parallel.ForEachAsync(States, (state, tmp) =>
{
    CustomLogger.SaveTime($"Thread of State {state} : Iteration has just been Started");

    Parallel.Invoke(
            async () =>
             {
                 CustomLogger.SaveTime($"Thread of operation for State:{state} - Iteration has just been Started");

                 await Sr.BulkInsertAsync(Row_Numbers, state);
                 //
                 data = await Sr.LoadStockBasedOnStateByRawSQL(state);
                 Parallel.ForEach(data, x => x.StockShare += 10);
                 await Sr.BulkInsertfromlist(data);

                 CustomLogger.SaveTime($"Thread of operation for State:{state} - Iteration has just been Finished");
             }
           );

    CustomLogger.SaveTime($"Thread of State {state} : Iteration has just been Finished");
    return ValueTask.CompletedTask;
});

stw.Stop();
CustomLogger.SaveTime("Main Thread: StopWatch has just been FINISHED");

Thread.Sleep(10000);

CustomLogger.SaveTime("Main Thread: Application has just been FINISHED");
CustomLogger.SaveToFile();

Console.ReadKey();
Enter fullscreen mode Exit fullscreen mode

You can see, I am going to deal with 150,000 rows for each state :
const int Row_Numbers = 150_000;

My whole program.cs is all about logging and Console.Write or using StopWatch. The most important part is using BulkInsertAsync method to insert 150,000 row for each state and then fetchig data by LoadStockBasedOnStateByRawSQL for each state , massaging the data by :

Parallel.ForEach(data, x => x.StockShare += 10);
Enter fullscreen mode Exit fullscreen mode

and then, I have to BulkInsert the whole list by : BulkInsertfromlist method and done !.

This means, the most important part the code is :

await Parallel.ForEachAsync(States, (state, tmp) =>
{
    CustomLogger.SaveTime($"Thread of State {state} : Iteration has just been Started");

    Parallel.Invoke(
            async () =>
             {
                 CustomLogger.SaveTime($"Thread of operation for State:{state} - Iteration has just been Started");

                 await Sr.BulkInsertAsync(Row_Numbers, state);
                 //
                 data = await Sr.LoadStockBasedOnStateByRawSQL(state);
                 Parallel.ForEach(data, x => x.StockShare += 10);
                 await Sr.BulkInsertfromlist(data);

                 CustomLogger.SaveTime($"Thread of operation for State:{state} - Iteration has just been Finished");
             }
           );

    CustomLogger.SaveTime($"Thread of State {state} : Iteration has just been Finished");
    return ValueTask.CompletedTask;
});
Enter fullscreen mode Exit fullscreen mode

Do not forget to change the Connection String from OnConfiguring method inside StockMarketContext.cs then you can run the code and see the result.

Image description

Image description
I have collected the result into a Text file also so you should keep the console opened to be able to go through the result. Plus, you can see the how much time .NET 6 and SQL Server need to insert the first row and then the last row after fetching and changing the StockShare value inside the SQL table in Milliseconds.

Image description

By these 3 T-SQL Statement , you can simply see the result.

Select Top 100 * from [BenchMarkDB].[dbo].[StockMarket] Where State='Alabama'
Order By ID Desc


Select FORMAT(Count(*),'N0') 
From [BenchMarkDB].[dbo].[StockMarket]

Select DATEDIFF(MILLISECOND,Min(LogTime),MAX(LogTime)) As TimeDiff 
From [BenchMarkDB].[dbo].[StockMarket]
Enter fullscreen mode Exit fullscreen mode

I know you are looking for the GitHub repo : GitHub repository

If link did not work for any reason, find the link below:

https://github.com/AliCharper/DOTNET_6_Performance
Enter fullscreen mode Exit fullscreen mode

If you want to know my Hardware specification, look at the below pic!.

Image description

I wish everybody like the content and that is end of the story here.

Enjoy and continue coding with .NET !
Ali Kolahdoozan
I ❤️ .NET
KUL, Malaysia

Latest comments (0)

🌚 Friends don't let friends browse without dark mode.

Sorry, it's true.