Searching in multiple tables using MS SQL Server 2000

We have a database with a lot of information about Persons. I won't post the entire database structure because it is too big, but it looks something like this:

---

Person

ID

Name

Street

City

State

Country

Language

LangCode

Language

Interest

ID

LastChangedBy

LastChangedOn

LocalizedInterest

InterestID

LangCode

Description

PersonInterest

PersonID

InterestID

---

Now, this is just a small example. In our database, we have about 8-9 localized tables (like Interest) a P开发者_如何学Goerson can be linked to. A Person can have multiple Interests, a Person can have multiple Jobs, a Person can have multiple Educations, a Person can have multiple Experiences, ...

I have to build a search function. Let's say you enter "tom" as the search term. This should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables.

If you enter multiple words to search for (eg. "tom php"), it should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables AND "php" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables.

At the moment there are about 4,500 records in the Person table and if I do an outer join of Person with all the tables I have to search, there are about 1,300,000 records and 40-50 fields to search in.

How should I approach this problem so that performance will be acceptable? The client expects "something like Google" in terms of speed and ease of use.

We're using MS SQL Server 2000 and ASP.NET 2.0. The search functionality has to be added to an existing application and changing the technology or database structure is not an option.

---

You could implement full-text search in involved tables/fields and then create a query based on it. You can find quick info here.