title: Node is 20x faster Than .NET Core in Connecting to Postgres
body: <p>I have two servers connecting to a <code>PostgresSQL 9.6</code> db hosted on Azure. The servers are doing one thing - hitting the Postgres db with a <code>SELECT 1</code> query every 5 seconds.</p>
  
  <p>Typical time to connect to db and get data:</p>
  
  <ul>
  <li>Node: <code>25 MS</code></li>
  <li>.NET Core 3.1 using Npsql 4.1.1(I have tried 4.1.2 too, no diff): <code>500 MS</code></li>
  </ul>
  
  <p>My <strong>problem</strong> is that my .NET Core app is <strong>20x slower</strong> than Node in getting data. I <strong>believe</strong> .NET Core is not pooling connections for some reason. This slowness occurs with both running the app locally and while running it on Azure App Services - no difference. <strong>I want to solve the .NET --> Postgres slowness.</strong></p>
  
  <p>Please only skim the relevant details and don't read the whole thing past this point - I believe only the <code>.NET Core</code> code is relevant.</p>
  
  <p>A <code>PsPing</code> to the db from my machine (on which both the <code>Node</code> and the <code>.NET Core</code> apps are running:</p>
  
  <pre><code>Connecting to foobarPostGres:5432 (warmup): from someIp: 19.98ms
  Connecting to foobarPostGres:5432: from someIp: 1.65ms
  Connecting to foobarPostGres:5432 from someIp: 1.18ms
  Connecting to foobarPostGres:5432: from someIp: 1.23ms
  Connecting to foobarPostGres:5432: from someIp: 1.06ms
  </code></pre>
  
  <p>For sake of completeness, a sample of <code>NODE</code> times look like this (note that the first time it establishes a connection, it is also "slow"):</p>
  
  <pre><code>Attempting to establish a connection...
  Elapsed ms:  644.1334999799728
  RESP:  { '?column?': 1 }
  Elapsed ms:  22.76109904050827
  RESP:  { '?column?': 1 }
  Elapsed ms:  21.984400033950806
  RESP:  { '?column?': 1 }
  Elapsed ms:  26.043799996376038
  RESP:  { '?column?': 1 }
  Elapsed ms:  22.538798987865448
  RESP:  { '?column?': 1 }
  </code></pre>
  
  <p>Connection times for <code>.NET Core</code> look like this:</p>
  
  <pre><code>5:13:32 PM: SLOW QUERY, CONN TIME: 4153, QUERY TIME: 18 
  5:13:53 PM: SLOW QUERY, CONN TIME: 707, QUERY TIME: 17 
  5:14:14 PM: SLOW QUERY, CONN TIME: 589, QUERY TIME: 16
  5:14:35 PM: SLOW QUERY, CONN TIME: 663, QUERY TIME: 18 
  5:14:56 PM: SLOW QUERY, CONN TIME: 705, QUERY TIME: 16 
  </code></pre>
  
  <p>Note the super-slow initial connection time and a long time to establish a connection on subsequent requests.</p>
  
  <p>Anyway, because I am desperate, I am going to dump all my code now, with explanations. The connection string looks like this:</p>
  
  <pre><code>public static string CONNECTION_STRING {
    get {
      return $"Server={HOST}; User Id={USER}; Database={DB_NAME}; Port={PORT}; Password={PWD}; SSLMode=Prefer";
    }
  }
  </code></pre>
  
  <p>It is my understanding that I should get connection pooling out of the box if I use this connection string. Note that I have tried turning of <code>SSL</code> on both the db and taking that line out - it did not help.</p>
  
  <p>My health check controller looks like this:</p>
  
  <pre><code>// GET api/health/getdbhealthselectone
  [HttpGet]
  [Route("getdbhealthselectone")]
  public async Task&lt;IActionResult&gt; GetDbHealthSelectOne()
  {
    int testData = await _healthCheckRepo.RunHealthCheckSelectOne();
    return Ok(testData);
  }
  </code></pre>
  
  <p>My health check repo method looks like this:</p>
  
  <pre><code> public async Task&lt;int&gt; RunHealthCheckSelectOne()
      {
  
        await using var conn = new NpgsqlConnection(AzureDbConnectionInfo.CONNECTION_STRING);
  
        var connTimer = System.Diagnostics.Stopwatch.StartNew(); // TODO: Remove this testing line
        await conn.OpenAsync();
        connTimer.Stop(); // TODO: Remove this testing line
        var msToConnect = connTimer.ElapsedMilliseconds; // TODO: Remove this testing line
  
        int testData = 999;
        var jobsQueryTimer = System.Diagnostics.Stopwatch.StartNew(); // TODO: Remove this testing line0
        await using (var cmd = new NpgsqlCommand("SELECT 1", conn))
        await using (var reader = await cmd.ExecuteReaderAsync())
        while (await reader.ReadAsync()) {
          testData = reader.GetInt32(0);
        };
  
        jobsQueryTimer.Stop(); // TODO: Remove this testing line
        var msToQuery = jobsQueryTimer.ElapsedMilliseconds; // TODO: Remove this testing line
  
        LogQueryIfSlow(msToConnect, msToQuery, _logger); // TODO: Remove this testing line
  
        return testData;
      }
  </code></pre>
  
  <p>Note the timers here - <code>await conn.OpenAsync();</code> is what takes the bulk of the time by far, the queries themselves are fast. Also, for sake of saving time - I have run this code WITHOUT <code>async</code> before, no difference.</p>
  
  <p>Finally, in case there are dependency injection concerns, the repository is in a class library, the API project references it, and:</p>
  
  <p><code>services.AddSingleton&lt;IHealthCheckRepository, HealthCheckRepository&gt;();</code></p>
  
  <p>This is how it sees it.</p>
  
  <p>I believe this is all the relevant information - I have been on the phone with Azure support and they found no issues with the db config. The .NET Core app is super light, so it's not like it's overloaded and it's in testing, so no traffic besides my tests.</p>
  
  <blockquote>
    <p>Extra: For the sake of completeness, here is my WHOLE node app which hits the db and gets the performance posted (conn data taken out).</p>
  </blockquote>
  
  <pre><code>const { Pool, Client } = require('pg');
  const { performance } = require('perf_hooks');
  
  const pool = new Pool({
    user: 'SECRET',
    host: 'SECRET',
    database: 'SECRET',
    password: 'SECRET',
    port: 5432,
  })
  
  
  function runQuery(pool) {
    var t0 = performance.now();
    pool.query('SELECT 1', (err, res) =&gt; {
      if (err) {
        console.log('ERROR: ', err.stack)
      } else {
        console.log('RESP: ', res.rows[0])
      }
      var t1 = performance.now();
      console.log('Elapsed ms: ', t1-t0);
      //pool.end()
  });
  
  }
  
  setInterval(() =&gt; {runQuery(pool)}, 5000);
  </code></pre>
  
  <hr>
  
  <p>EDIT: For posterity, here are the times in .NET Core after fixing the connection pool timeout - it's faster than node, except on that initial connection, which seems to take a while, but I haven't checked some defaults:</p>
  
  <pre><code>CONN: 1710 QUERY: 18
  CONN: 0 QUERY: 16
  CONN: 0 QUERY: 16
  CONN: 0 QUERY: 17
  CONN: 0 QUERY: 16
  CONN: 0 QUERY: 23
  CONN: 0 QUERY: 16
  CONN: 0 QUERY: 16
  CONN: 0 QUERY: 23
  CONN: 0 QUERY: 16
  CONN: 0 QUERY: 16
  </code></pre>