CODEWITHSUNDEEP
sql-serverdatabaseentity-framework-coresqlconnection
svstnv
svstnv

Reputation: 233

How often do you need to re-create the connection to SQL Server?

I did an experiment with the speed of writing to the database. I have several tables where logs are stored. I took 1 million logs and sent them in batches of 750 records into the database, while measuring the speed of processing requests by the SQL Server. These 750 records are just plain 750 INSERT INTO's.

In the graph below you can see the distribution of processing time for each batch. Look at these 4 columns. I think they arose due to the re-creation of the connection to the SQL Server.

My questions are:

  1. Am I right?

  2. If yes, I'm curious how often do I need to recreate the connection if I want to handle it myself? Are there any rules/guidelines/researches about it? I would be grateful for any relevant information and links.

My goal is to achieve as less average processing time as possible.

Request processing chart

P.S. For working with database I'm using EntityFramework Core with single DbContext created:

var dbContext = _dbContextFactory.CreateDbContext();
dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
while(queueIsNotEmpty)
{
    var batch = GetBatch(size:750);
    dbContext.AddRange(batch);
    await dbContext.SaveChangesAsync();
}

Version of SQL Server is 2019, EF Core 6.0.1

Upvotes: 2

Views: 79

Answers (0)

Related Questions