nhibernate - archiving records

This is a simplistic view of our domain model (we are in healthcare):

Account
{
  List<Registration> Registrations {...}
  DateTime CreatedDate {...}
  Type1 Property1 {...}
  Type2 Property2 {...}
  ...

 }

Registration
{
 InsuranceInformation {...}
 PatientVisit {...}
 Type1 Property1 {...}
 Type2 Property2 {...}
 ...

 } 

Setup

1. We use Nhibernate/FluentNH to setup and configure the SessionFactory
2. And let's assume that we have setup all the required table indices

Usage

1. We get ~10,000 new account a day
2. We have about 500K accounts Total.
3. We have several Linq queries that operate over these accounts
4. All our queries use Linq, most queries are dynamically built using predicate builder pattern (we don't use Hql)

The problem is that, As the number of accounts increases the execution time for these queries increases.

Note:

1. Only accounts that are within at 48 hours window are relevant for our queries / application. But, Older accounts need to be preserved (so cannot be deleted). Even though these accounts are not needed by the application it may be consumed later by the analytics application

To solve this performance issue:

1. we are considering archiving accounts that are older than 48hrs
2. Creating an Archive database with the same schema as the main db
3. Adding a windows service that is scheduled to run on a nightly basic that moves "old" accounts from the main db to the archive db
4. The windows service will using nhiberate to read old accounts from the main db and save the old accounts(again using nhibernate) to the archive database, and then delete the old accounts from the main db. Right now, w开发者_JAVA技巧e think this service will move one account at a time until all the old accounts are moved to the archive database.
5. Ocassionally, when we do get a request to restore an account from the archive db, we will reverse the above step

Questions:

1. Is this archival approach any good? If not, why? can you suggest some alternate implementations?
2. Can I use the same sessionfactory to connect to the main db and the archive db during the copy process? How can i change the connection string dynamically? Can I have two simulateous open sessions that work with two database
3. Can I copy more than one account at a time using this approach? Batch Copy and batch deletes?

Any help appreciated, thank you for your input.

I think your issue is more database related then nhibernate. A database with 500k records is not so much. To optimize access you should think about how you query and how to optimize for those queries.

1. Query only the data you need
2. Optimize your table by making indexes
3. Use the 20/80 rule, find the 20% expensive queries and optimize the code/queries. You program will be 80% faster
4. NHibernate: optimize your mappings
5. HHibernate: use batch size if you do multiple updates
6. Add stored procedures if something is hard to do in code

If your db grows, hire a db expert to advise on database optimization (they can improve your performance by 10% to 90%). You need him first for a few days and then once a week/month depending on how much work there is.