CODEWITHSUNDEEP
c#postgresqlentity-framework-6npgsql
bruno.almeida
bruno.almeida

Reputation: 2896

Postgresql npgsql first connection takes too long

I am new to Postgresql. I am tring to use Postgresql with entity framework6, using npgsql.

I already have a database. I am using "Code First form database" option.

The problem is that the first time a query is executed, it takes me lots of time to execute it. I think that is when the connection is opened for the first time.

I created this simple example with the problem (TestJSONB is DbContext):

class Program
{
    static void Main(string[] args)
    {
        TestQuery();
        TestQuery();
    }

    private static void TestQuery()
    {
        using (DALJSONB.TestJSONB dataModel = new DALJSONB.TestJSONB())
        {
            var query1 =
                dataModel.Database.SqlQuery<int>(
                    @"

                    SELECT 1;
                ");

            query1.ToList();

            var query2 =
                dataModel.Database.SqlQuery<int>(
                    @"

                    SELECT 1;
                ");

            query2.ToList();
        }
    }
 }

The first execution times of TestQuery() are something like:

The second execution times of TestQuery() are something like:

My app.config is:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    </configSections>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
    <connectionStrings>
        <add name="TestJSONB" connectionString="Host=x.x.x.x;Username=x;Password=x;Persist Security Info=True;Database=TestJSONB" providerName="Npgsql" />
    </connectionStrings>
    <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
        <providers>
            <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
            <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
        </providers>
    </entityFramework>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" />
                <bindingRedirect oldVersion="0.0.0.0-3.2.1.0" newVersion="3.2.1.0" />
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
</configuration>

Is it possible to decrease this time?

Thanks in advance.

Upvotes: 0

Views: 1622

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16722

This very likely has nothing to do with PostgreSQL or Npgsql, but rather with Entitt Framework itself starting up, building your model etc. try to connect to your database without EF (I.e. just creating an NpgsqlConnection and opening it) and you should see it runs pretty quickly.

It's common for EF applications to send sa sort of dummy warm-up query before actually servicing user calls, precisely to avoid this significant first-time delay.

Upvotes: 3

Related Questions