none
When switching to job specific databases with EF5, will caching still work? RRS feed

  • Question

  • I'm designing a web service that returns simple query information. Using webApi & EF5. Easy. The problem I need help solving is this. We have 15 SQL servers with a combined 200+ databases. The schemas are all the same. Each DB is customer/case specific. So the API gets called with a case# and finds the sql server & db name from a lookup table.

    I know how to set the dbContext to a specific connection string. So telling EF5 what to connect to is easy enough. It seems I will be losing any connection caching capabilities by having to switch DB's with every call.

    On a busy day, we'll have 10-60 users per case #, and about 20 cases a day. (read 20 unique DB connections a day)

    Should I create my own connection cache inside my WebAPI? As simple as a List of last used connections? Or will EF5 just work?

    Thursday, January 17, 2013 6:49 PM

Answers

  • In terms of what caching EF does you aren't going to miss anything that I know of. If you are using the same DbContext then all the caching that EF does will still work, it isn't keyed to the connection string.

    The one thing that you might hit is that each time you connect to a new database the initializers will run, which might mean that the first query against each new database is slow. If this is an issue then disabling the initializers might help.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    • Marked as answer by Alexander Sun Wednesday, January 30, 2013 8:31 AM
    Tuesday, January 22, 2013 7:41 PM
    Moderator

All replies

  • Hi,

    If you meant connection pooling this is at the provider level and it works anyway for each unique connection string (and user if integrated authentication) so I'm not sure how you could cache connections yourself if they don't go anyway to the same db.

    So my understanding is that you would then have at most 20 pools a day and perhaps 10 connections in each pool to server those 60 users. You have performance counters to keep an eye on this.

    IMO it's always best to first see how it behaves (or do you have *seen* an actual issue) or at least here it doesn't seems to produce something that will surely cause a problem.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Friday, January 18, 2013 12:07 PM
  • In terms of what caching EF does you aren't going to miss anything that I know of. If you are using the same DbContext then all the caching that EF does will still work, it isn't keyed to the connection string.

    The one thing that you might hit is that each time you connect to a new database the initializers will run, which might mean that the first query against each new database is slow. If this is an issue then disabling the initializers might help.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    • Marked as answer by Alexander Sun Wednesday, January 30, 2013 8:31 AM
    Tuesday, January 22, 2013 7:41 PM
    Moderator