0

We use EF6.2 in our Web SaaS application. We have database per account architecture which means every customer gets separate SQL Server database.

What we see is extremely slow startups. We also see startups getting proportionaly slower with amount of accounts we add.

We currently do not use any of optimizations possible. We don't do ngen, we don't "pre-compile" views. But what bothers me is that it's not performance hit we take overall, but per account.

That leads me to beleive it is related to metadata cache which according to article here being built in-memory and tagged with hash derived from connection string. https://docs.microsoft.com/en-us/ef/ef6/fundamentals/performance/perf-whitepaper

3.4.1 Metadata Caching algorithm

1.Metadata information for a model is stored in an ItemCollection for each EntityConnection. •As a side note, there are different ItemCollection objects for different parts of the model. For example, StoreItemCollections contains the information about the database model; ObjectItemCollection contains information about the data model; EdmItemCollection contains information about the conceptual model.

2.If two connections use the same connection string, they will share the same ItemCollection instance.

Looking for any suggestions on how to reduce "warm up" time to single occurence

EDIT: This is Model-first EDMX based DB, no migrations

  • Can you guarantee that there is no migration or initialization work that EF needs to do for each tenant database? And is this EDMX-based Database-first, Code First from and Existing Database, or Code-First? – David Browne - Microsoft Apr 16 at 11:58
  • @DavidBrowne-Microsoft I updated question. Yes, it's EDMX DB first model – katit Apr 16 at 13:36
  • One thing to rule out before looking at EF is SqlClient connection pooling. Each connection string gets its own connection pool, so any delays in connection or authentication will manifest as a per-database startup delay. – David Browne - Microsoft Apr 16 at 22:52
  • @DavidBrowne-Microsoft David, do you have suggestions on how to look into that? Actually SQL hosted on same machine, no performance/latency issues with it.. – katit Apr 16 at 22:58
  • If there's a connection delay it should repro from any app, including PowerShell or SSMS when using the same connection string. Also check that the customer databases aren't set to auto-close. – David Browne - Microsoft Apr 16 at 23:11
0

Each DB Context will incur a one-off spin-up cost when it is first accessed, so you will want to ensure that a client connection is only touching its own DbContext to avoid that first spin-up waiting on all client DB Context's being touched. For my model I have a common database that the initial login uses to resolve what versioned database a client should be talking to, from there the authenticated user is handed off to their database. The last thing you want is code that iterates over DbContexts in any way.

Edit: The above is not correct, verified with a test. DbContext instances for the same schema hitting different database instances in the same application domain do not incur the spin-up cost. The key factor for a web application is how the application domain is scoped to the request. (considering any load balancing, etc.)

Another factor to consider is to use DB-First and setting the database initializer to #null to avoid any Migrations checks etc. This will save spin-up time.

You can adopt bounded contexts to service key areas of your application(s). The larger and more complex a context definition is, the longer the spin-up takes. By using bounded contexts to cover closely related areas of an application you can cut down on the time needed to spin up a given context on first use. For instance OrderManagementContext, CustomerManagementContext. Entities can be referenced in each context, but you can optimize the definitions where you only need a summary representation in a context that doesn't treat that entity at a top-level. For instance when I'm working with orders, I need a summary of customer. Rather than mapping the whole of Customer and it's related entities that I don't care about, the OrderDbContext maps a OrderCustomer entity which only maps the fields from the Customers table (or view) for the fields I need about a customer when dealing with orders. Fewer, smaller, simpler entities in each context = faster context init.

Lastly, when a user goes to log in, spin up the context(s) early as part of the login process by executing a simple query against one entity in the context. This triggers the initial one-off spin up for the mapping and that way the first "real" query won't get hit with that delay. I.e.

using (var orderContext = new OrdersDbContext())
{
   var result = orderContext.Orders.Any();
}
using (var customerContext = new CustomersDbContext())
{
   var result = customerContext.Customers.Any();
}

Each of these executes a very fast query, but initialize the mapping for that initial spin-up hit.

Another factor you may to consider beyond minimizing your contexts spin-up cost is any load balancing your environment is doing and possible implications for spin-up costs. The spin up is associated to an application domain, so the questions there will be with load balancing whether that spin-up has to happen on each worker or not. (That's beyond what I know, I'd start with minimizing the context spin up cost, then start peeking down that rabbit hole if it's still needed.)

  • Technically I have only one context/model. Why do I have to incur spin-up cost for each different DB connection? All metadata/etc should be the same – katit Apr 16 at 18:01
  • That was something I actually hadn't tested to date, but got me curious. I will revise the answer because connecting a DB Context to an alternate database within the same application domain does not incur a new spin-up. In your situation, how is it behaving? If you have 1 client database, what is the first access spin up time? How much does that first spin-up change as you add databases? I've done a test with 10 databases and there is a slight, consistent performance hit (ms) as the # of databases increase. 1-off spin up in seconds, then ms for others. (Very basic schema though) – Steve Py Apr 16 at 21:37
  • Our schema is pretty big. Maybe that's why. We see it in production, so it's hard to say how long is "first access". It just takes minutes to startup. Let' say 3-5 seconds per DB sounds right.. – katit Apr 16 at 21:51
  • You can use the spin-up check on login ("Lastly point") then log a time diff to see how long the context is taking to warm up. Large, complex contexts are a bit of a killer though. The performance cost quickly escalates when you're writing against a single context that represents the complete state of your application, which can be hundreds of tables, with many more times the # of relationships. Breaking it up with bounded contexts with simpler summary types can make a noticeable impact, but it is a bit of a re-factor cost. Is that minutes for the app server to start up, or per login? – Steve Py Apr 16 at 22:11
  • Not sure what you mean by "you can use spin-up check on login". What login? It is prod server, constantly under use and any "iisreset" will cause 2min 100cpu from application – katit Apr 16 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.