Caching SessionFactory and Configuration for multiple databases with NHibernate

Question

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 ?

Answer 1

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.