Tävlingsuppgift 2011

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.

WantedSetID AttributeName AttributeValue
1 Gender F
1 Age B
1 Driver’s license B
2 Gender F
2 Age B
3 Gender M
3 Age C

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).

Task

Create a search function that finds all people that match the search criteria in WantedAttribute and return as a table.

PeopleID WantedSetID
1 2
2 1
2 2
3 2

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

Author

This competition problem was authored by Peter Larsson and based on a real world scenario.

SQL Script & Test data

SQL setup script
Testdata