Sql Server 2008 全文优化
我正在使用 Sql Server 2008 中的全文索引。我创建了索引、目录并完全填充它。 我的表有近 400,000 条记录。我的全文索引定义为 varchar(Max) 列（现在将其称为“文本”）。我正在执行以下查询：
select * from MyTable 其中包含（文本，'房子'）。
此查询在 14 秒内返回近 20,000 条记录。我认为那非常慢。但是，当我执行此查询时：
select Count(*) from MyTable 其中 contains(Text, 'house')
只需 1 秒即可显示结果。
我一直在寻找，据我所知，两个查询的执行计划是相同的。 为什么 Sql Server 需要花费这么多时间来显示第一个查询结果？
我已经做了什么：
我买了一个SSD，并将mdf和ldf都放在这个磁盘上。但是当我执行第一个查询时，我可以看到正在我的 HDD（而不是 SSD）上创建日志和 tmp 数据库。我的 SSD 是 D:，所有临时文件都在 C: 上创建。
这是否需要这么长时间，因为 sql 需要这些文件来填充企业管理器结果网格？我可以做些什么来优化数据库吗？我确实需要查询在 2 秒内运行。
I am playing a little with fulltext indexes in Sql Server 2008. I created the index, catalog and full populate it.
My table has almost 400,000 records. My full text index is defined to a varchar(Max) column (lets call it Text for now). I am executing the follow query:
select * from MyTable
where contains(Text, 'house').
This query returns almost 20,000 records in 14 seconds. I think thats very slow. But, when i execute this query:
select Count(*) from MyTable
where contains(Text, 'house')
It takes only 1 second to show the result.
I've been looking and as far as I can see, the execution plan is the same for both queries.
Why is Sql Server taking that much to show the fist query result?
What I have already did:
I bought a SSD and put both mdf and ldf on this disk. But when I execute the first query, i can see that logs and a tmp database are being created at my HDD (not in the SSD). My SSD is D: and all temp files are being created at C:.
Is this taking so long because sql needs those files to populate the enterprise manager result grid? is there anything i can do to optimize the Database? I really need the queries to run in less than 2 seconds.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您确实需要显示所有文本，还是想对结果运行更多查询并进一步缩小范围？我假设这 14 秒中的大部分时间都用于显示结果 - 如果您不需要显示结果或只想显示这些结果的某些子集，则执行时间应该会缩短。
Do you really need to display all that text, or do you want to run some more queries on the results and narrow them further? I assume that most of the 14 seconds are used to display the results -- if you don't need to show them or only want to show some subset of these results, execution time should go down.
有什么理由需要返回 20,000 条记录吗？您能否向查询添加分页，以便以 20、100、1000 或小于 20,000 的块返回结果集？无论您优化多少，返回这么大的结果集都需要时间。
Is there any reason why you need to return 20,000 records? Could you add paging to the query so you return the result set in chunks of 20, 100, 1000 or something smaller than 20,000? It's going to take time to return a result set that large, no matter how much you optimize.
您的查询的额外时间可能是由于在 Management Studio 中填充网格而花费的。
请记住，当您执行 SELECT * 时，您还将文本列本身包含在网格结果中。尝试仅选择文本列以外的列，您可能会发现执行时间更接近 SELECT COUNT(*) 查询的执行时间。您可能还想在选择列表中包含 DATALENGTH(Text)。
Management Studio 中还有一个选项可以控制检索到网格中的最大字符数。您可以在“工具”-->“工具”下找到它。选项 -->查询结果-->结果到网格。
Your query's extra time is probably from populating the grid in Management Studio.
Keep in mind that when you do a SELECT * you're also including the Text column itself in the grid results. Try selecting only columns other than your Text column and you will probably see the execution time drop closer to that of your SELECT COUNT(*) query. You might want to also include DATALENGTH(Text) in your select list.
There's also an option in Management Studio that controls the maximum characters retrieved into the grid. You can find it under Tools --> Options --> Query Results --> Results to Grid.
我同意克里斯蒂安的观点。您的问题在于结果的呈现。尝试输出到文本而不是网格以消除一些开销。
I agree with Christian. Your problem is with the rendering of the result. Try output to Text rather than Grid to eliminate some of this overhead.