Entity Framework Core 3.1.3 very slow first query used within AWS Lambda Function(s) and AWS API Gateway serverless API

Question

I have an AWS API Gateway which proxies to several AWS Lambda Functions. These functions read and write from a PostgreSQL database (AWS Aurora PostgreSQL). I have wired up the data access using Entity Framework Core Database First. I have an issue where the first time after a few minutes that I call my API, and the Lambda function executes, the first query via EF Core to the database takes up to 29 seconds(!). The next one, executed only a second later, will take only 200ms.

I know what you're thinking, it's Lambda cold starts. Well, I have kind of ruled that out, because if I take out any of the EF Core code, and just let my function return a dummy response, the time goes down to about 4 seconds response, then 200ms if I invoke again a second later.

If I examine the log for my function, I can also see that the latency is at the point when the first EF Core query is executed, other events prior to that occur quite quickly. See below excerpt.

"Found test customer" is the first return of data from the database from a query. See excerpt below:

            using (var loyalty = new loyaltyContext())
            {
                var testArray = new string[]
                    {Customer.CustomerStateReasonCodes.Deceased, 
                        Customer.CustomerStateReasonCodes.Fraud};

                var dupeEmailCustomers = (from c in loyalty.ContactInformation
                        where c.ContactType == "EMAIL"
                        join cu in loyalty.Customer on c.CustomerInternalId equals cu.CustomerInternalId
                        where cu.Status == Customer.CustomerStates.Active
                        select c).AsNoTracking()
                    .Union(from c in loyalty.ContactInformation
                        where c.ContactType == "EMAIL"
                        join cu in loyalty.Customer on c.CustomerInternalId equals cu.CustomerInternalId
                        where cu.Status != Customer.CustomerStates.Active &&
                              testArray.Contains(cu.StatusReason)
                        select c).AsNoTracking();

                foreach (var cust in dupeEmailCustomers)
                {
                    context.Logger.LogLine($"Found test customer {JsonConvert.SerializeObject(cust)}");
                }
            }

Here's the Lambda execution log:

Notice the jump from 9:26secs to 9:44secs. That's the trip to the database and back. Now if I invoke the same API again straight afterwards, it happens sub-second. I am assuming this is EF Core. My issue is that I am unsure within the architecture of AWS Lambda, how I might be able to decrease this first query latency. I have enabled provisioned concurrency for AWS Lambda which supposedly keeps instances of the containers containing my code 'warm' and ready to run, but it has made no difference.

I suspect this is because the only part of the Lambda code that's kept warm is the stuff that runs OUTSIDE the lambda handler. The EF Core query only occurs within my handler, e.g. within my:

    public APIGatewayProxyResponse PostCustomerProxyResponse(APIGatewayProxyRequest request, ILambdaContext context)

I believe that the only code that gets kept 'warm' by provisioned concurrency, is what occurs in the constructor, e.g.

public Functions()
{
    _jSchema = new JSchemaGenerator().Generate(typeof(CustomerPayload));
    _systemsManagementClient = new AmazonSimpleSystemsManagementClient(RegionEndpoint.APSoutheast2);

    SecurityKey = PopulateParameter(ParameterPath + Integration + JWT + "/secret", true);
    Issuer = PopulateParameter(ParameterPath + Integration + JWT + "/issuer", false);
    ClaimName = PopulateParameter(ParameterPath + Integration + JWT + "/claim", false);
    ScpiUser = PopulateParameter(ParameterPath + Integration + SCPI + "/user", false);
    ScpiPassword = PopulateParameter(ParameterPath + Integration + SCPI + "/password", true);
    //DbUser = PopulateParameter(ParameterPath + ParameterPathDatabase + "/iamuser", false);
}

I tried to add a small database query to the constructor, basically a call to ExecuteRawSQL() in Entity Framework to 'SELECT 1' from PostgreSQL, in the hopes that would count as that first query, and my actual API invocation would be faster, but this failed miserably. The entire API actually times out when trying to invoke the method if I have this 'SELECT 1' code in the Lambda constructor.

I'm at a loss. Can anyone assist? I am at the point of dumping EF Core and going back to a simple query engine like SqlKata which would be a shame, as the mappings and entities within EF Core make it great to work with. If it helps, I'm using Npgsql.EntityFrameworkCore.PostgreSQL for my EF Core connection.

Answer 1

I encountered this exact issue (hence bringing me here). My lambda was only allocated 128MB and was taking 17 seconds to build up EF. On upping the memory to 2048MB, the same process was reduced to 1 second. The lambda actually only required 168 MB but of course, that's more than the original 128MB.