Advanced Search Design for Large Objects

I’m looking for some advice. I recently wrapped up a project where I inherited some terrible code. I got the application running but it’s safe to say there are a number of performance and design issues, specifically with the advanced search functionality. I have n开发者_StackOverflowow been asked to do a very similar project but much larger in scale. I have an opportunity here to build a much better domain model from scratch and create a much better application as a whole. The question is what would be the best way to implement the advanced search?

The advanced search page brings up a form with two required text fields, 4 optional dropdown lists, and two separate areas with multiple optional checkboxes to filter the results even further.

The current solution uses the two required fields to return a List of objects that I then filter and eliminate based on any optional form values. I then throw the filtered List into the cache and attach the user’s session id. Then on each results page, I have an Html.Helper that displays paging links that use the Take().Skip() approach on the List retrieved from the cache to display 10 results.

The issue I’m having is that the list can get pretty beefy. I’m trying to save database calls for every new page of results by putting it in the cache but I’m not sure if that’s the best way to approach this. Should I just put all the form values in a monster query string and keep making database calls from the GET requests? Should I save the users search criteria in a database or session and use those for every new page of results? Am I right with using the cache to hold such a beefy collection?

I asked a question similar to this here: Paging search results with asp.net MVC that led me to using the cache. Now that I have a clean slate I would love to follow best practices and do it right from the start. Any suggestions would be great.

Obviously, the best way to implement advanced search depends on your requirements and on your current estimates with regards to size of search space and frequency of searches. My guess is that your biggest issue is whether or not to implement search as a part of an ORM or in the relational database.

Large SQL queries can be significantly slow, hard to tune, and difficult to debug. If advanced search is a major feature and has lots of complex business rules, then consider implementing search as a part of your ORM. Also, intelligent search would favor the use of an ORM.

Loading lots of large objects into memory can take its toll on both performance and scalability. If the search space is large, then consider using SQL to do the searching.

There are algorithms available for distributed search but they are very sophisticated and your budget, expertise, and delivery schedule may not accommodate that approach. How strategic is advanced search to the success of the project?

Does the search have to be real-time? If not, then consider a hybrid approach where objects are indexed during non-peak times. It is wasteful to have to search through low relevancy words such "the" or "because" so searching through a paraphrased version of the objects may strike a happy medium.

Good luck and have fun!