我可以请求SQL Server缓存某个结果集吗?

There is a certain query that is being called from an ASP .NET page. I studied the execution plan of that query in Management Studio and 87% is for a sort. I badly need the sorting or else the data displayed would be meaningless.

有一个特定的查询正在从一个asp.net页面被调用。我在Management Studio中研究了该查询的执行计划，87%是某种类型的。我非常需要排序，否则显示的数据将毫无意义。

Is there anyway that I can request SQL Server to cache a sorted results set so it will return the data faster in consequent runs?

我是否可以请求SQL Server缓存已排序的结果集，以便在后续运行中更快地返回数据?

Or is SQL Server smart enough to do the cache handling and am I doing mistake by trying to force it to cache results, if that is possible?

或者SQL Server是否足够聪明，能够进行缓存处理?如果可能的话，我是否尝试强制它缓存结果?

Any relevant information will be highly appreciated and thanks a lot in advance :)

如有任何相关信息，我们将不胜感激。)

UPDATE:
I just read in an article that creating a View with a clustered index will increase performance because the index will persist the data in a view to disk. Is this true? How do i get about doing this? Any articles?

更新:我在一篇文章中读到，使用聚集索引创建视图将提高性能，因为索引将把视图中的数据持久化到磁盘。这是真的吗?我该怎么做呢?文章吗?

4 个解决方案

#1

In short, no: not at the SQL server end; it will of course load the data into memory if possible, and cache the execution plan - so subsequent calls may be faster, but it can't cache the results.

简而言之，no:不在SQL服务器端;如果可能的话，它当然会将数据加载到内存中，并缓存执行计划——因此后续调用可能会更快，但它不能缓存结果。

Options:

选项:

tune the plan; the sort sounds aggressive - could you perhaps denormalize some data or add an index (perhaps even a clustered index); there may be other things we can do with the query if you show it (but tuning without a fully working DB is guestimation at best)
调整计划;这种排序听起来很有挑战性——您是否可以反规范化一些数据或添加一个索引(甚至可能是一个聚集索引);如果您显示查询，我们还可以做其他事情(但是在没有完全工作的DB的情况下进行调优最好是guestimation)
cache the results at the web-server if it is sensible to do so
如果明智的话，在web服务器上缓存结果

#2

Whilst you can create an indexed view, as you allude to in your update, you should be aware that:

虽然您可以创建一个索引视图(正如您在更新中提到的)，但您应该知道:

There are quite a lot of rules you have to follow, both when creating the view, and when updating the tables upon which it is based, and,
在创建视图和更新基于视图的表时，您必须遵守很多规则，
Just because there's a (clustered) index, that doesn't imply a sort order - you would still have to use an ORDER BY when querying this table, and,
仅仅因为有一个(群集)索引，并不意味着排序顺序—在查询这个表时，您仍然需要使用一个顺序，
Unless you're using Enterprise edition, you have to query the view with the WITH (NOEXPAND) query hint
除非使用Enterprise edition，否则必须使用with (NOEXPAND)查询提示查询视图
You would specify the order for the index by specifying ASC and DESC in the CREATE INDEX statement, not in the CREATE VIEW. The "hack" to allow ORDER BY in a view (by specicfying top 100 percent) would have no effect.
您可以在CREATE index语句中指定ASC和DESC，而不是在CREATE视图中指定索引的顺序。“hack”允许在视图中进行排序(通过指定top 100%)将不会产生任何效果。

#3

An option that is used sometimes is to store the sorted data in a secondary table. This could e.g. be a temporary table which you create for just the session, or a cache table for the entire database.

有时使用的选项是将排序后的数据存储在辅助表中。例如，可以为会话创建临时表，也可以为整个数据库创建缓存表。

#4

Again, SQL server itself will cache frequent queries in memory. You can test this by using query analyzer and running a complex query a few times, each time it will get faster. Given this a permanent cache might not be necessary.

同样，SQL server本身将在内存中缓存频繁的查询。您可以使用query analyzer测试这一点，并多次运行复杂的查询，每次查询都会变得更快。因此，可能不需要永久缓存。

If it is I recommend using a cache table and running your query and inserting the values into the other table. You can either use a application variable or another sql table to determine when to refresh the cache again.

如果是，我建议使用缓存表并运行查询并将值插入到另一个表中。您可以使用应用程序变量或另一个sql表来确定何时再次刷新缓存。

Example query for inserting into the cache table:

插入到缓存表的示例查询:

Insert Into Cache Select Value, Value from Table 1 Order by Field

插入到缓存选择值，从表1按字段的顺序值。

#1