LinkedIn
Проекты
Advertisement
RSS
разработка сайтов
оптимизация сайта
веб-дизайн
продвижение сайтов
ASP.NET
ASP.NET MVC
.NET Core
HTML5
SEO
CSS3
jQuery
Bootstrap
Angular
React
Always will be ready notify the world about expectations as easy as possible: job change page

Super Fast Query in Entity Framework

Created: 20 Nov 2022
Author: Juldhais Hengkyawan
Source: https://juldhais.net/super-fast-query-in-entity-framework-6d20cd5358e2
Views: 23

Sometimes we have to perform several queries to the database, put the results into memory, and then process them as needed.

For example, we have a Supplier table and a Customer table. Both tables have a substantial amount of data. We want to combine existing Supplier and Customer data with specific criteria.

Generally, we will perform two queries sequentially: query to the Supplier table first, then query to the Customer table. The second query will be executed after completing the first query.

If each query takes 5 seconds to process, and the query merge process in memory takes 2 seconds, then the total time required to complete the entire process is about 12 seconds (5+5+2).

Now the question arises: “Can we perform the first and second query simultaneously (parallel), then combine the results after the two queries are finished?”

The answer is: YES, WE CAN!

The total time required to complete the entire process will be significantly reduced by running the first and second queries simultaneously.

How?

First, create a Console Application project with the .NET 6.0 framework. Name the project AsyncEntity.

Add the EntityFrameworkCore.SqlServer package from NuGet Packages to the project.

Then create two entities, Supplier and Customer:

namespace AsyncEntity;

public class Supplier
{
    public int Id { get; set; }
    public string Name { get; set; }
}

 

namespace AsyncEntity;

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Next, we create a data context class. Create a new file named DataContext.cs:

using Microsoft.EntityFrameworkCore;

namespace AsyncEntity;

public class DataContext : DbContext
{
    public DataContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<Supplier> Supplier { get; set; }
    public DbSet<Customer> Customer { get; set; }
}

We need to populate the Supplier and Customer tables with dummy data. Create the DataInitializer class to initialize the data:

namespace AsyncEntity;

public class DataInitializer
{
    private readonly DataContext _db;

    public DataInitializer(DataContext db)
    {
        _db = db;
    }

    public async Task Run()
    {
        await _db.Database.EnsureDeletedAsync();
        await _db.Database.EnsureCreatedAsync();

        var total = 1000000;
        for (int i = 0; i < total; i++)
        {
            _db.Supplier.Add(new Supplier
            {
                Name = $"P{(i + total)}"
            });

            _db.Customer.Add(new Customer
            {
                Name = $"C{(i + total)}"
            });
        }

        await _db.SaveChangesAsync();
    }
}

When the Run() method in the DataInitializer class is executed, the Program will reset the database (EnsureDeleted() + EnsureCreated()) and fill the Supplier and Customer tables with 1 million data.

It’s time to run the data initialization process in Program.cs:

using System.Diagnostics;
using AsyncEntity;
using Microsoft.EntityFrameworkCore;

var stopwatch = new Stopwatch();

var connectionString = "Data Source=.\\SQL2019;Initial Catalog=AsyncEntity;Integrated Security=True";
var optionsBuilder = new DbContextOptionsBuilder().UseSqlServer(connectionString);

// initialize data
stopwatch.Start();
var dbInitializer = new DataContext(optionsBuilder.Options);
var dataInitializer = new DataInitializer(dbInitializer);
await dataInitializer.Run();

stopwatch.Stop();
Console.WriteLine($"Data initialization: {stopwatch.Elapsed}");

The data initialization process took about 1 minute 23 seconds on my computer.

Data initialization: 00:01:23.9350880

Sequential

Next, we’ll create a sequential process first:

// sequential
stopwatch.Restart();
var dbSequential = new DataContext(optionsBuilder.Options);

var sequentialSuppliers = await dbSequential.Supplier
    .Where(x => x.Name.Contains("7"))
    .OrderByDescending(x => x.Name)
    .ToListAsync();

var sequentialCustomers = await dbSequential.Customer
    .Where(x => x.Name.Contains("7"))
    .OrderByDescending(x => x.Name)
    .ToListAsync();

var sequentialTotal = sequentialSuppliers.Count + sequentialCustomers.Count;
stopwatch.Stop();
Console.WriteLine($"Sequential (Total: {sequentialTotal}): {stopwatch.Elapsed}");

In the code snippet above, the query is still executed sequentially, even though we have done asynchronous programming using the ToListAsync() method.

Sequential (Total: 937118): 00:00:04.5931360

The sequential process above takes about 4.5 seconds.

Parallel

Next, we will make the first query and the second query run in parallel:

// parallel
stopwatch.Restart();

// create two DataContext instance
var dbParallel1 = new DataContext(optionsBuilder.Options);
var dbParallel2 = new DataContext(optionsBuilder.Options);

// task for the first query (no need to await here)
var parallelSupplierTask = dbParallel1.Supplier    
    .Where(x => x.Name.Contains("7"))
    .OrderByDescending(x => x.Name)
    .ToListAsync();

// task for the second query (no need to await here)
var parallelCustomerTask = dbParallel2.Customer
    .Where(x => x.Name.Contains("7"))
    .OrderByDescending(x => x.Name)
    .ToListAsync();

// run both tasks in parallel (await here)
await Task.WhenAll(parallelSupplierTask, parallelCustomerTask);

// get the results from both queries
var parallelSuppliers = parallelSupplierTask.Result;
var parallelCustomers = parallelCustomerTask.Result;

var parallelTotal = parallelSuppliers.Count + parallelCustomers.Count;
stopwatch.Stop();
Console.WriteLine($"Parallel (Total: {parallelTotal}): {stopwatch.Elapsed}");

The code snippet above will run the first and second queries in parallel and wait until both queries are finished.

Parallel (Total: 937118): 00:00:02.6591467

The parallel process above only takes about 2.6 seconds! Almost half of the sequential process.

By using Task.WhenAll(), we can run multiple tasks at the same time. This will undoubtedly increase the performance and speed of the Program we make.

However, we must be careful because we have to use a different data context for each query that will be executed in parallel. Multiple data contexts require more memory allocation and more connection pool.

The source code of this article can be found here: https://github.com/juldhais/AsyncEntity

Similar
16 May 2022
Author: Hussein NM
In this article, I will show the most performant way to write queries for a collection of objects in .Net Core. The article discusses the common methods of LINQ and their comparison. After reading this article, you may have to...
4 Mar 2021
Author: Matthew Jones
We have an upcoming project in my group that is going to need to be very, very performant. This project will involve us querying for data from a SQL database, transforming that data into strongly-typed objects, then returning those objects...
28 Apr 2022
Author: Julia Smith
Table Of Content- Introduction- Top 6 Tips to optimize the performance of your .Net application- 1. Avoid throwing exceptions- 2. Minify your files- 3. Avoid blocking calls- 4. Cache your pages- 5. Optimize custom code- 6. Minimize large object allocation-...
10 Jul 2021
Author: Sam Walpole
I've recently gotten into using Docker in my development cycle and I'm really enjoying how much of a wonderful tool it is. One thing that always used to be a pain was setting up a development server to run SQL...
Send message
Email
Your name
*Message


© 1999–2022 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET | .NET Core | C# | ASP.NET | Windows Forms | WPF | Windows Phone | HTML5 | CSS3 | jQuery | AJAX | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban