My question: what determines the speed(performance) of calling DbContext.SaveChanges() method? And is it a bad practice to put all the DbSets in a single DbContext?
I have a c#/WPF/MS SQL Server/Entity Framework Core project, which is actually for my company's wholesale business.
I implemented a single DbContext which contains dozens of DbSet's, each of which, of course, represents a table in the database. There are about 10 major tables representing orders, order details, customers, products, etc, and each of the major DbSet/tables contains about 50,000 to 150,000 records in it. The problem is when DbContext.SaveChanges method is called, it takes over 9,000ms(9 sec) to execute! I put ALL of the DbSets in the same DbContext. Is this a bad habit and the cause for slow speed?
For a test, I created a separate DbContext and put only one DbSet in it. The DbSet has about 100,000 records, but calling SaveChanges for that took about 500ms, which was a significant improvement.
Given my situation, what is the best practice for database performances? Please help.
What I have tried:
public class MyDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseLazyLoadingProxies().UseSqlServer(DbConn.GetConnStr());
base.OnConfiguring(optionsBuilder);
}
public DbSet<Order> Orders { get; set; }
public DbSet<OrderDetail> OrderDetails { get; set; }
public DbSet<Ship> Ships { get; set; }
public DbSet<ShipDetail> ShipDetails { get; set; }
public DbSet<Customer> Customers { get; set; }
public DbSet<Product> Products { get; set; }
public DbSet<ProductStock> ProductStocks { get; set; }
public DbSet<ProductPrice> ProductPrices { get; set; }
public DbSet<PriceType> PriceTypes { get; set; }
public DbSet<Claim> Claims { get; set; }
public DbSet<Carrier> Carriers { get; set; }
public DbSet<Channel> Channels { get; set; }
public DbSet<Import> Imports { get; set; }
public DbSet<ImportDetail> ImportDetails { get; set; }
}