Skip to content

Instantly share code, notes, and snippets.

@mu88
Created May 15, 2023 14:44
Show Gist options
  • Save mu88/4ad74c1bb582129768aac68a8eb66851 to your computer and use it in GitHub Desktop.
Save mu88/4ad74c1bb582129768aac68a8eb66851 to your computer and use it in GitHub Desktop.
Compare PostgreSQL and SQL Server regarding CanConnectAsync
using System.Diagnostics;
using Microsoft.EntityFrameworkCore;
using Testcontainers.MsSql;
using Testcontainers.PostgreSql;
var msSqlContainer = new MsSqlBuilder()
.WithImage(MsSqlBuilder.MsSqlImage.Replace("mcr.microsoft.com/", string.Empty))
.Build();
await msSqlContainer.StartAsync();
SqlServerContext.Port = msSqlContainer.GetMappedPublicPort(MsSqlBuilder.MsSqlPort);
var postgresContainer = new PostgreSqlBuilder()
.WithImage(PostgreSqlBuilder.PostgreSqlImage)
.Build();
await postgresContainer.StartAsync();
PostgresContext.Port = postgresContainer.GetMappedPublicPort(PostgreSqlBuilder.PostgreSqlPort);
var factories = new List<Func<DbContext>> { () => new SqlServerContext(), () => new PostgresContext() };
const int runs = 10;
var results = new Dictionary<string, double>(runs*factories.Count);
foreach (var factory in factories)
{
var tester = new Tester(factory);
await tester.InitializeAsync();
results = results.Concat(await tester.TestAsync(runs)).ToDictionary(x => x.Key, x => x.Value);
}
foreach (var (key, value) in results)
{
Console.WriteLine($"{key}: {value} ms");
}
public class Tester
{
private readonly Func<DbContext> _factory;
public Tester(Func<DbContext> factory) => _factory = factory;
public async Task InitializeAsync()
{
using var context = _factory();
await context.Database.EnsureCreatedAsync();
}
public async Task<Dictionary<string, double>> TestAsync(int runs)
{
var results = new Dictionary<string, double>(runs);
for (int i = 0; i < runs; i++)
{
using var context = _factory();
var stopwatch = Stopwatch.StartNew();
await context.Database.CanConnectAsync();
stopwatch.Stop();
results.Add($"{context.GetType().Name}-{i}", stopwatch.ElapsedMilliseconds);
}
return results;
}
}
public class SqlServerContext : DbContext
{
public static int Port { get; set; }
readonly string _connectionString = $"server=127.0.0.1,{Port};user id={MsSqlBuilder.DefaultUsername};password={MsSqlBuilder.DefaultPassword};database={MsSqlBuilder.DefaultDatabase};encrypt=false";
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(_connectionString);
}
public class PostgresContext : DbContext
{
public static int Port { get; set; }
readonly string _connectionString = $"Host=127.0.0.1;Port={Port};Database={PostgreSqlBuilder.DefaultDatabase};User Id={PostgreSqlBuilder.DefaultUsername};Password={PostgreSqlBuilder.DefaultPassword};Include Error Detail=true;";
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseNpgsql(_connectionString);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment