6

Some questions here in StackOverflow about NHibernate and multiple databases (dynamically connected), address me to believe that the best solution would be to keep a cache of SessionFactory (per database) and a cache to Configuration, to build SessionFactory faster.

I would like to know if this approach is correct, and mainly: What is the "weight" of SessionFactory in terms of memory consumption per instance and time to be built ?

MORE ABOUT MY APPROACH:

Known that the 1st and 2nd level caches live in the SessionFactory "realm", we could suppose that memory consumption could be related to the amount of ISession opened (which includes 1st cache level).

For 2nd cache level, in my case where the databases share the same schema and have some commom readonly tables, I could use a global cache implementation sharing only the readonly tables across all SessionFactories, it sounds nice for my project.

Am I right ? What kind of performance and memory issues I could have with this approach ? Consider a web application (ASP.NET MVC, not Webforms) accessing several hundreds (or maybe thousands) of databases, on each HTTP REQUEST it needs find the user database and address the access to it (get, use and dispose an ISession).

CONNECTION POOL: And what about connection pool ? What kind of behavior it should have to manage a pool of hundreds or thousands of databases like in that approach ? Some of the current implementations used with NHibernate could help or I would need to construct my own ?

Luciano
  • 2,695
  • 6
  • 38
  • 53
  • 1
    We used NHibernate in a multinenant setup with about 20-30 session factories, and I don't remember the exact numbers, but memory consumption was getting visibly higher for each added tenant. You can implement poor man's multitenancy support with a custom `IConnectionProvider`, but you won't be able to use the 2nd level cache in this case. The multitenancy feature is supported by original Hibernate (one session factory per multiple db connections), and there is a pull request for NHibernate implementing a part of this feature on github - https://github.com/nhibernate/nhibernate-core/pull/91. – Vasea Mar 25 '13 at 15:45

1 Answers1

2

Some tests confirmed what some guys said about the time and resources (cpu and memory) to constructs Configuration and SessionFactory, booth are expensive, but the SessionFactory is more, a lot more expensive. But we have only individual tests, empiric results, like my tests what depends from my class model, unfortunately.

So, I keep believing I need to cache SessionFactory and Configuration, but if we have hundreds of databases in a web solution we have a big problem, because SessionFactory is hungry for memory, it's a "memory eater", I could get a OutOfMemoryException near 400 instances and I expect to reach some thousands of databases.

Well, as we're using SQLServer, we designed a cache to have just one SessionFactory to each SQLServer instance instead one to each database, so in the ConnectionProvider we can inject a sql command USE 'catalog-name' which prepare each connection to work properly with its respectively database per request.

I hope others guys with the same problem could also explore resources like that from SQLServer, although some databases just allow you to do that through schema name and not database (catalog name).

Good luck.

Luciano
  • 2,695
  • 6
  • 38
  • 53
  • Wouldn't it be simplier to provide an IDbConnection to ISession.OpenSession like this : `var conn = myApp.GetOpenConnection(); var session = sessions.OpenSession(conn);` ? – bN_ Jan 19 '18 at 10:18
  • Nope. The context (eg. cache) isn't in the scope of a connection, you can change connection along "contexts" (sessions). Another database (connection), but what about the cached data/session? – Luciano Jan 19 '18 at 10:30