Let’s assume you are running a dating website where members have entered personal information. Your task in this competition is to write an efficient search match-making function.
A search could for example look like this.
Each WantedSetID represents a collection of search criteria. WantedSetID 1 has three criteria: gender must be F, age must be B and driver’s license must be B. Only people that fulfill all these critera should be returned as search results for WantedSetID 1.
The database contains the following tables.
- People, a list of members of the dating site
- PeopleAttribute, contains all the attribute values for all members
- Attribute, a list of searchable attributes
- WantedAttribute, that contains the search criteria (see example table above).
Create a search function that finds all people that match the search criteria in WantedAttribute and return as a table.
Your task is to make the search function as fast as possible.
Other restrictions in the competition:
- The solutions had to run on a SQL Server 2008 R2
- T-SQL or SQLCLR is allowed
- You are not allowed to make any changes to the MeterReadings table except creating non clustered indexes
- Indexed views are not allowed
This competition problem was authored by Peter Larsson and based on a real world scenario.