Suggestions for search methodology requested

Question

I am throwing this question out to the 'greater creativity' for some suggestions on search methodology that might solve this problem. I have come up with a somewhat contrived example to attempt to explain the problem well enough that some solution(s) might be recommended.

The problem: We are using a relational DB (MSSQL), and need to search across products with the following constraints:

• Availability
• Market
• Price

We also need to group and sort on price, market, and larger entity roll-up.

Let me see if I can explain using semi-real world entities: Think of an application that allows users to search for hotel rooms that they can book. The following criteria might be available for the search: days that the rooms would be booked for, the market in which they want a hotel room (e.g. NYC, Boston), amenities that the hotel room(s) might have (hot tub, fireplace, number of beds, suite or not).

In all cases, the search criteria above may or may not be selected. So, a very broad search is possible (on these dates, I don't care which market), or varying the levels of specificity (in NYC, 2 beds, with fireplace).

To add complexity, we also need to return the prices of the hotel rooms for the selected criteria. Obviously, special pricing, different prices for the different days of the stay (one price on Thursday night and a different price on Friday and Saturday nights for a weekend stay).

Some of the things we've played around with are: Solr, Endeca, SQL querying, implementing in C# code and optimizing when/where necessary (think of the decorator pattern).

What I'm looking for here is some suggestions from the community on: which of the above is NOT suited for this problem, which might be well suited for this problem, which other technology might work better that we haven't thought about, techniques that others have used on similar problems that worked well (non-technology specific).

Answer 1

I am a big fan of Sphinx. It is super fast, supports MSSQL and is pretty easy to use. Not sure on the C# integration, but I think there are examples of using it there.

In your configuration you just setup what you want to index (i.e. tables and columns). Then when you search you can search either just a single object type or across all object types.

There are quite a few sorting and filtering options as well including Faceting.

Answer 2

It looks like you have a performance problem. This can be caused by many things. For one, are you running the query against several web services or directly through your database? How is the database schema organized? Do you use normalized database or a star-schema type? How about your indexes? How do you handle optional information (any number of beds and any price)? There are lots of factors that could affect the performance. You need to sit with DBA and examine your SQL and plan statistics and review your schema carefully. Without knowing more details about the architecture and the other specific points mentioned above, it is not possible to make a recommendation (at least that is what I think).