//https://www.npgsql.org/doc/index.html
public void TestPostgreSql()
{
var pSqlConnString = Program.gConfiguration.GetConnectionString("PostgreSqlDefault");
using var conn = new NpgsqlConnection(pSqlConnString);
gLogger.BeginGProfiling();
Stopwatch watch = Stopwatch.StartNew();
Stopwatch watch0 = Stopwatch.StartNew();
conn.Open();
watch0.Stop();
Console.WriteLine($"Connection takes {watch0.Elapsed.TotalMilliseconds:0.00}ms"); // first connection: 360-392ms, later: 0, so connections are cached
gLogger.GProfiledInfo("CONNECTION", true);
gLogger.ProfiledInfo("CONNECTION", watch, true);
// Insert some data
Stopwatch watch1 = Stopwatch.StartNew();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO testtable (column1) VALUES (@p)";
cmd.Parameters.AddWithValue("p", "Hello SqCore.Tools.BenchmarkDB");
cmd.ExecuteNonQuery();
}
watch1.Stop();
Console.WriteLine($"INSERT takes {watch1.Elapsed.TotalMilliseconds:0.00}ms"); // "INSERT takes 27,33,30,37,29,30 ms". If I do it from pgAdmin, it says: 50msec
gLogger.GProfiledInfo("INSERT", true);
gLogger.ProfiledInfo("INSERT", watch, true);
// Retrieve all rows
Stopwatch watch2 = Stopwatch.StartNew();
using (var cmd = new NpgsqlCommand("SELECT column1 FROM testtable", conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
watch2.Stop();
Console.WriteLine($"SELECT takes {watch2.Elapsed.TotalMilliseconds:0.00}ms"); // "SELECT takes 22,29,32,37,43,47,45 ms", If I do it from pgAdmin, it says: 64msec
gLogger.GProfiledInfo("SELECT", true);
gLogger.ProfiledInfo("SELECT", watch, true);
// Delete inserted data
Stopwatch watch3 = Stopwatch.StartNew();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM testtable WHERE column1=@p;";
cmd.Parameters.AddWithValue("p", "Hello SqCore.Tools.BenchmarkDB");
cmd.ExecuteNonQuery();
}
watch3.Stop();
Console.WriteLine($"DELETE takes {watch3.Elapsed.TotalMilliseconds:0.00}ms"); // "INSERT takes 27,33,30,37,29,30 ms". If I do it from pgAdmin, it says: 50msec
// pgAdmin running on local webserver reports worse numbers. maybe because pgAdmin first access local webserver + implemented badly. And that overhead is also calculated.
gLogger.GProfiledInfo("DELETE", true);
gLogger.ProfiledInfo("DELETE", watch, true);
}
