C#: Very fast object search & retrieval using any persistence model

I am developing an application with Fluent nHibernat/nHibernate 3/Sqlite. I have run into a very specific problem for which I need help with.

I have a product database and a batch database. Products are around 100k but batches run in around 11 million+ mark as of now. When provided with a product, I need to fill a Combobox with batches. As I do not want to load all the batches at once because of memory constraints, I am loading them, when the product is provided, directly from the database. But the problem is that sqlite (or maybe the combination of sqlite & nh) for this, is a little slow. It normally takes around 3+ seconds to retrieve the batches for a particular product. Although it might not seem like a slow scenario, I want to know that can I improve this time? I need sub second results to make order entry a smooth experience.

The details:

• New products and batches are imported periodically (bi-monthly).
• Nothing in the already persisted 开发者_如何学Goproducts or batchs ever changes (No Update).
• Storing products is not an issue. Batches are the main culprit.
• Product Ids are long
• Batch Ids are string
• Batches contain 3 fields, rate, mrp (both decimal) & expiry (DateTime).

The requirements:

• The data has to be stored in a file based solution. I cannot use a client-server approach.
• Storage time is not important. Search & retrieval time is.
• I am open to storing the batch database using any other persistence model.
• I am open to using anything like Lucene, or a nosql database (like redis), or a oodb, provided they are based on single storage file implementation.

Please suggest what I can use for fast object retrieval.

Thanks.

---

You need to profile or narrow down to find out where those 3+ seconds are.

Is it the database fetching?
Try running the same queries in Sqlite browser. Does the queries take 3+ seconds there too? Then you might need to do something with the database, like adding some good indexes.

Is it the filling of the combobox? What if you only fill the first value in the combobox and throw away the others? Does that speed up the performance? Then you might try BeginUpdate and EndUpdate.

Are the 3+ seconds else where? If so, find out where.

---

This may seem like a silly question, but figured I'd double-check before proceeding to alternatives or other optimizations, but is there an index (or hopefully a primary key) on the Batch Id column in your Batch table. Without indexes those kinds of searches will be painfully slow.

For fast object retrieval, a key/value store is definitely a viable alternative. I'm not sure I would necessarily recommend redis in this situation since your Batches database may be a little too large to fit into memory, and although it also stores to a disk it's generally better when suited with a dataset that strictly fits into memory.

My personal favourite would be mongodb - but overall the best thing to do would be to take your batches data, load it into a couple of different nosql dbs and see what kind of read performance you're getting and pick the one that suits the data best. Mongo's quite fast and easy to work with - and you could probably ditch the nhibernate layer for such a simple data structure.

There is a daemon that needs to run locally, but depending on the size of the db it will be single file (or a few files if it has to allocate more space). Again, ensure there is an index on your batch id column to ensure quick lookups.

---

3 seconds to load ~100 records from the database? That is slow. You should examine the generated sql and create an index that will improve the query's performance.

In particular, the ProductId column in the Batches table should be indexed.