I've built a full-text catalog on a SQL 2005 box that, after it's re-build process completes, runs extremely slow. I've implemented a hack (i.e. try...catch{do again}) so that my users don't get a timeout error; this makes me feel bad inside. All subsequent queries are lightning fast.
我在SQL 2005的盒子上构建了一个全文目录,在它重新构建过程完成后,运行速度非常慢。我实现了一个hack(即try ... catch {do again}),这样我的用户就不会出现超时错误;这让我心情不好。所有后续查询都是快速的。
Has anyone experienced this issue and was/is there a solution? Thanks!
有没有人遇到过这个问题,是否有解决方案?谢谢!
P.S. Yes, I've Google'd it many times. Even with my left hand.
附:是的,我已多次谷歌了。即使用我的左手。
5 个解决方案
#1
2
It could also be caused by this Sql Server 'feature' which we've experienced.
它也可能是由我们经历过的这个Sql Server“功能”引起的。
You may experience a 45-second delay when you run a full-text query in an instance of SQL Server 2005 that is running on a server without Internet access
在没有Internet访问的服务器上运行的SQL Server 2005实例中运行全文查询时,可能会遇到45秒的延迟
#2
1
this might not be a direct answer to your question, but the full-text-search on mssql was covered on * podcast series, and the conclusion was it's not the best thing :)
这可能不是你的问题的直接答案,但是在*播客系列中涵盖了mssql的全文搜索,结论是它不是最好的东西:)
so, if you are able to change it to a 3rd party library, you may try what's used by jeff & co., the Apache Lucene library. Java version available at http://lucene.apache.org/java/docs/ , and .net port at http://incubator.apache.org/lucene.net/
因此,如果您能够将其更改为第三方库,您可以尝试使用jeff&co。,Apache Lucene库所使用的内容。 Java版本位于http://lucene.apache.org/java/docs/,和.net端口位于http://incubator.apache.org/lucene.net/
#3
0
i've had this as well. first hit very slow and rest are fast. tried all sorts and couldnt resolve it.
我也有这个。第一次打得很慢,休息很快。尝试各种各样,无法解决它。
would love to know the answer to this one.
我很想知道这个问题的答案。
#4
0
You could prevent having to fully rebuild the index by "setting start background updateindex" and "start change tracking" (there should be an underscore between each word) on each table that is full text indexed.
您可以通过“设置开始后台updateindex”和“开始更改跟踪”(每个单词之间应该有一个下划线)来防止必须完全重建索引。每个表都是全文索引。
This allows sql server to update the index only with changes when required. It may help your issue since the index is not being rebuilt.
这允许sql server仅在需要时使用更改来更新索引。它可能对您的问题有所帮助,因为索引没有被重建。
#5
0
I second the Lucene.Net suggestion. I have previously tried to build a 'search engine' of sorts using Full Text Search and SQL. It was always problematic when the search criteria gets complicated and often queries would time out. On my new site I built the search engine with Lucene.Net project and it works really well and is much faster than SQL FTS.
我是Lucene.Net的第二个建议。我之前曾尝试使用全文搜索和SQL构建各种“搜索引擎”。当搜索条件变得复杂并且查询经常超时时,它总是有问题的。在我的新网站上,我使用Lucene.Net项目构建了搜索引擎,它运行得非常好,并且比SQL FTS快得多。
#1
2
It could also be caused by this Sql Server 'feature' which we've experienced.
它也可能是由我们经历过的这个Sql Server“功能”引起的。
You may experience a 45-second delay when you run a full-text query in an instance of SQL Server 2005 that is running on a server without Internet access
在没有Internet访问的服务器上运行的SQL Server 2005实例中运行全文查询时,可能会遇到45秒的延迟
#2
1
this might not be a direct answer to your question, but the full-text-search on mssql was covered on * podcast series, and the conclusion was it's not the best thing :)
这可能不是你的问题的直接答案,但是在*播客系列中涵盖了mssql的全文搜索,结论是它不是最好的东西:)
so, if you are able to change it to a 3rd party library, you may try what's used by jeff & co., the Apache Lucene library. Java version available at http://lucene.apache.org/java/docs/ , and .net port at http://incubator.apache.org/lucene.net/
因此,如果您能够将其更改为第三方库,您可以尝试使用jeff&co。,Apache Lucene库所使用的内容。 Java版本位于http://lucene.apache.org/java/docs/,和.net端口位于http://incubator.apache.org/lucene.net/
#3
0
i've had this as well. first hit very slow and rest are fast. tried all sorts and couldnt resolve it.
我也有这个。第一次打得很慢,休息很快。尝试各种各样,无法解决它。
would love to know the answer to this one.
我很想知道这个问题的答案。
#4
0
You could prevent having to fully rebuild the index by "setting start background updateindex" and "start change tracking" (there should be an underscore between each word) on each table that is full text indexed.
您可以通过“设置开始后台updateindex”和“开始更改跟踪”(每个单词之间应该有一个下划线)来防止必须完全重建索引。每个表都是全文索引。
This allows sql server to update the index only with changes when required. It may help your issue since the index is not being rebuilt.
这允许sql server仅在需要时使用更改来更新索引。它可能对您的问题有所帮助,因为索引没有被重建。
#5
0
I second the Lucene.Net suggestion. I have previously tried to build a 'search engine' of sorts using Full Text Search and SQL. It was always problematic when the search criteria gets complicated and often queries would time out. On my new site I built the search engine with Lucene.Net project and it works really well and is much faster than SQL FTS.
我是Lucene.Net的第二个建议。我之前曾尝试使用全文搜索和SQL构建各种“搜索引擎”。当搜索条件变得复杂并且查询经常超时时,它总是有问题的。在我的新网站上,我使用Lucene.Net项目构建了搜索引擎,它运行得非常好,并且比SQL FTS快得多。