使用 MS SQL Server 2000 在多个表中搜索
我们有一个数据库，其中包含有关人员的大量信息。我不会发布整个数据库结构，因为它太大了，但它看起来像这样：
Person
身份证号
姓名
街道
城市
状态
国家
语言
语言代码
语言
兴趣
身份证号
最后更改者
LastChangedOn
本地化兴趣
兴趣ID
语言代码
描述
个人兴趣
人员ID
InterestID
现在，这只是一个小例子。在我们的数据库中，我们有大约 8-9 个可以链接到人员的本地化表（例如兴趣）。一个人可以有多种兴趣，一个人可以有多种工作，一个人可以有多种教育，一个人可以有多种经验，......
我必须建立一个搜索功能。假设您输入“tom”作为搜索词。这应该给出一份姓名、街道、城市、州、国家、他们的一项兴趣、一项工作、一项教育、一项经验或一项工作中带有“tom”的所有人员的列表。其他链接表的。
如果您输入多个单词进行搜索（例如“tom php”），它应该给出姓名中包含“tom”的所有人员的列表，街道，城市，州，国家，其兴趣之一，其中之一他们的工作、他们的一种教育、他们的一种经历或其他链接表之一和他们的姓名、街道、城市、州、国家、他们的兴趣之一中的“php” ，在他们的一项工作中，在他们的一项教育中，在他们的一项经验中或在其他链接表之一中。
目前，Person 表中有大约 4,500 条记录，如果我对 Person 与我必须搜索的所有表进行外连接，则大约有 1,300,000 条记录和 40-50 个字段需要搜索。
我应该如何解决这个问题那么这样的表现是可以接受的吗？客户期望在速度和易用性方面“像谷歌一样”。
我们使用 MS SQL Server 2000 和 ASP.NET 2.0。搜索功能必须添加到现有应用程序中，并且无法更改技术或数据库结构。
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 Person 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以在涉及的表/字段中实现全文搜索，然后基于它创建查询。您可以在此处找到快速信息。
You could implement full-text search in involved tables/fields and then create a query based on it. You can find quick info here.