.NET 6 is fast. Very fast!. Since I ❤️ .NET, I am going to show you how fast the .NET 6 is !.
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
You can see the table structure in the picture.
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
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.
Click on the respected item, then you can see the below page.
If you click on Next, the next page should be the following page as I have shown below.
Continue clicking on Next
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.
Click Next, then you will see the below page.
Dealing with SQL Server is almost done, click next to see the below page.
Migrate button should be clicked and you are done with SQL Server.
You should click on OK button to see the new table.
This should be the last screenshot regarding to an In-Memory optimized table creation.
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".
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.
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
The next Nuget package would be this:
You need text, it is already here:
Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 7.0.0-preview.6.22329.4
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; }
}
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);
}
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);
}
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());
}
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();
}
}
}
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"
};
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();
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);
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;
});
Do not forget to change the Connection String from OnConfiguring method inside StockMarketContext.cs then you can run the code and see the result.
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.
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]
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
If you want to know my Hardware specification, look at the below pic!.
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
Oldest comments (0)