从 SQL Server 数据库创建搜索系统
我最近在我的公司创建了一个搜索系统，使用基于 Lucene 的 Regain Search 来搜索所有软件支持请求。我在 SQL 中运行一些查询来提取数据、导出到 Excel、导入到 Access、运行一些其他查询、创建报告、将报告导出到 .txt 文件，并在传递之前使用文件拆分器将 HTML 文本拆分为多个页面将其发送至索引器。总结果是 90,000 多个页面被索引，我每天只更新大约 2,000 个左右，因为其余的都是关闭的支持事件，不会改变。结果是一个很棒的搜索功能，但更新记录是一个非常繁琐的手动过程。
我想将其改进为每晚自动更新的系统（或者至少尝试尽可能地自动化更新）。我已经构建了一个 SQL 2005 服务器并将其链接到我具有读取访问权限的生产服务器。我正在尝试从这里找出最好的方法。这些表基本上如下：
Case
CaseID 公司 ID (FK) 描述 决议
活动
ActivityID 案例 ID (FK) 日期 技术员 评论
公司
CompanyID 公司名称 许可证类型
升级
升级 ID 案例 ID (FK) 详细信息
那么，我是否要运行“select into”语句来创建一个可以索引并运行搜索的大型非标准化表？有这方面的例子或书籍吗？我需要报告服务吗？最重要的是要保持快速的查询速度。现在所有查询几乎都会立即返回。非常感谢任何帮助。
I have recently created a search system at my company using the Regain Search based on Lucene to search through all software support tickets. I run some queries in SQL to extract my data, export to Excel, import to Access, run some additional queries, create a report, export the report to .txt files, and use a file splitter to split the HTML text into pages before handing it off to the indexer. The total result is 90,000+ pages that are indexed and I only update about 2,000 or so daily as the rest are closed support incidents which do not change. The result is a great search functionality but it is a very tedious and manual process to update records.
I'd like to revamp this to a system that updates automatically each night (or at least try to automate this as much as possible). I have built a SQL 2005 server and linked it to the production server with which I have read access to. I'm trying to figure out the best approach to take from here. The tables are essentially as follows:
Case
CaseID
CompanyID (FK)
Description
Resolution
Activities
ActivityID
CaseID (FK)
Date
Technician
Comments
Company
CompanyID
CompanyName
LicenseType
Escalations
EscalationID
CaseID (FK)
Details
So do I run a "select into" statement to make one large non-normalized table that can be indexed and searches run against? Are there some examples or books on this? Will I need reporting services? The most important thing to maintain is the fast query speed. Right now all queries return almost instantly. Any help is greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您研究过 SQL Server 全文搜索吗？这听起来很适合您在这里想要完成的任务。如果您有报告需求，SQL 报告服务可以满足此要求。
索引维护可以设置为实时或按计划进行。
这两种产品在 SQL 2005 中都很可靠并且可以用于生产，但如果您能够使用 SQL 2008，它们都提供了一些改进。
一些全文参考：
BOL - http://technet.microsoft.com/en -us/library/ms142571(SQL.90).aspx
aspalliance.com/1512_understanding_full_text_search_in_sql_server_2005
一些 Reporting Services 参考资料：
BOL - technet.microsoft.com/en-us/library/cc917530.aspx
RulesToBetterSQLReportingServices - www.ssw.com.au/ssw/standards/Rules/RulesToBetterSQLReportingServices.aspx
关于这两个方面也有很多好书。
不确定直接针对链接服务器的全文的选项是什么，但您始终可以维护需要索引的内容的本地副本。如果您需要移动数据等，Sql Server Integration Services 是一个可靠的产品，我已经在生产中成功使用了很多年。
Have you looked into SQL Server Full Text Searching? It sounds like a direct fit for what you are trying to acomplish here. If you have reporting needs, SQL Reporting services could fill this requirement.
Index maintenance can be setup to happen real time or on a schedule.
Both products are solid and productino ready in SQL 2005 but both offer some improvements if you are able to go with SQL 2008.
Some Full Text references:
BOL - http://technet.microsoft.com/en-us/library/ms142571(SQL.90).aspx
aspalliance.com/1512_understanding_full_text_search_in_sql_server_2005
Some Reporting Services references:
BOL - technet.microsoft.com/en-us/library/cc917530.aspx
RulesToBetterSQLReportingServices - www.ssw.com.au/ssw/standards/Rules/RulesToBetterSQLReportingServices.aspx
There are many good books on both as well.
Not sure what the options are for Full Text directly against a linked server but you could always maintain local copies of what needs to be inexed. If you need to move data around, etc, Sql Server Integration Services is a solid product that I have used successfully in production for many years.