Im trying to create a view out of quite a complex select query and it wont let me put a clustered index on it because i have to use subqueries and some aggregate functions.
我试图从相当复杂的选择查询创建一个视图,它不会让我在其上放置聚集索引,因为我必须使用子查询和一些聚合函数。
I have to get a clustered index on it otherwise the queries that use the view will take forever. Apparently sql server will only store the result set if u meet a stupid amount of criteria.
我必须得到一个聚簇索引,否则使用该视图的查询将永远。显然,如果你满足一个愚蠢的标准,sql server将只存储结果集。
The base tables for the view are read-only and and only get updated by a bulk import once a day. I can't see why the results can't be cached.
视图的基表是只读的,并且每天只能通过批量导入进行更新。我不明白为什么结果无法缓存。
Does anyone know of any way to get sql server to cache the results of a view so they can in turn be queried later?? I dont really want to create another table cos that would snowball into a bunch of changes all over the place.
有没有人知道如何让sql server缓存视图的结果,以便以后可以查询?我真的不想创建另一个表cos,它会在整个地方滚雪球变成一堆变化。
Thanks in advance.
提前致谢。
6 个解决方案
#1
I think the answer you are looking for is: Don't use a view to do this. Use a table with the fields corresonding to the returned fields form the sql query . Automate the query to populate this table
我认为您正在寻找的答案是:不要使用视图来执行此操作。使用包含与sql查询返回的字段相对应的字段的表。自动化查询以填充此表
#2
The short answer is that a clustered index cannot be created, due to the reasons you mention.
简短的回答是,由于您提到的原因,无法创建聚簇索引。
When you ask for a way to cache the results of the complicated query, the only other object that SQL Server provides (and will solve your issue) is a table.
当您要求一种方法来缓存复杂查询的结果时,SQL Server提供的唯一其他对象(并将解决您的问题)是一个表。
If automation is a problem, you should consider creating the view, but only using it as a way to insert into a table, such that you can do a truncate/insert into the table (selecting from the view) immediately after the bulk insert.
如果自动化是一个问题,您应该考虑创建视图,但只使用它作为插入表的方式,这样您可以在批量插入后立即截断/插入表(从视图中选择)。
If you use SSIS (SQL Server Integration Services) this is a relatively trivial thing to add.
如果您使用SSIS(SQL Server Integration Services),这是一个相对简单的事情。
#3
As far as I'm aware, when compiling execution plans SQL Server essentially copies and pastes the definition of the view into the query its compiling - as long as you are able to add indexes to the underlying tables it should be possible to get good performance from the query.
据我所知,在编译执行计划时,SQL Server实质上将视图的定义复制并粘贴到其编译的查询中 - 只要您能够将索引添加到基础表,就应该可以获得良好的性能来自查询。
#4
What you are building sounds like a data warehouse, therefore your best option is to manipulate the data once it is in the system. You can build new tables of denormalised (or however else you are modifying it) and index them to allow quick querying.
您正在构建的内容听起来像数据仓库,因此您最好的选择是在数据进入系统后对其进行操作。您可以构建非规范化的新表(或者您正在修改它),并将它们编入索引以允许快速查询。
You can then build views on top of these tables if you need to.
如果需要,您可以在这些表的顶部构建视图。
#5
when using aggregates inside a indexed view you need to use COUNT_BIG() instead of COUNT() otherwise the view won't be created
在索引视图中使用聚合时,您需要使用COUNT_BIG()而不是COUNT(),否则将不会创建视图
Also if you are not on Enterprise Edition you need to provide the NOEXPAND hint otherwise the optimizer will not use the view
此外,如果您不在Enterprise Edition上,则需要提供NOEXPAND提示,否则优化程序将不使用该视图
SELECT *
FROM YourView WITH(NOEXPAND)
WHERE ....
Maybe you don't need a view but you just don't have th correct indexes on the tables, can you post the DDL of the tables (including indexes and constraints)
也许你不需要一个视图,但你只是在表上没有正确的索引,你可以发布表的DDL(包括索引和约束)
#6
I had the same problem and ended up putting the sub queries in clustered index views themselves.
我遇到了同样的问题,最终将子查询放在聚集索引视图中。
#1
I think the answer you are looking for is: Don't use a view to do this. Use a table with the fields corresonding to the returned fields form the sql query . Automate the query to populate this table
我认为您正在寻找的答案是:不要使用视图来执行此操作。使用包含与sql查询返回的字段相对应的字段的表。自动化查询以填充此表
#2
The short answer is that a clustered index cannot be created, due to the reasons you mention.
简短的回答是,由于您提到的原因,无法创建聚簇索引。
When you ask for a way to cache the results of the complicated query, the only other object that SQL Server provides (and will solve your issue) is a table.
当您要求一种方法来缓存复杂查询的结果时,SQL Server提供的唯一其他对象(并将解决您的问题)是一个表。
If automation is a problem, you should consider creating the view, but only using it as a way to insert into a table, such that you can do a truncate/insert into the table (selecting from the view) immediately after the bulk insert.
如果自动化是一个问题,您应该考虑创建视图,但只使用它作为插入表的方式,这样您可以在批量插入后立即截断/插入表(从视图中选择)。
If you use SSIS (SQL Server Integration Services) this is a relatively trivial thing to add.
如果您使用SSIS(SQL Server Integration Services),这是一个相对简单的事情。
#3
As far as I'm aware, when compiling execution plans SQL Server essentially copies and pastes the definition of the view into the query its compiling - as long as you are able to add indexes to the underlying tables it should be possible to get good performance from the query.
据我所知,在编译执行计划时,SQL Server实质上将视图的定义复制并粘贴到其编译的查询中 - 只要您能够将索引添加到基础表,就应该可以获得良好的性能来自查询。
#4
What you are building sounds like a data warehouse, therefore your best option is to manipulate the data once it is in the system. You can build new tables of denormalised (or however else you are modifying it) and index them to allow quick querying.
您正在构建的内容听起来像数据仓库,因此您最好的选择是在数据进入系统后对其进行操作。您可以构建非规范化的新表(或者您正在修改它),并将它们编入索引以允许快速查询。
You can then build views on top of these tables if you need to.
如果需要,您可以在这些表的顶部构建视图。
#5
when using aggregates inside a indexed view you need to use COUNT_BIG() instead of COUNT() otherwise the view won't be created
在索引视图中使用聚合时,您需要使用COUNT_BIG()而不是COUNT(),否则将不会创建视图
Also if you are not on Enterprise Edition you need to provide the NOEXPAND hint otherwise the optimizer will not use the view
此外,如果您不在Enterprise Edition上,则需要提供NOEXPAND提示,否则优化程序将不使用该视图
SELECT *
FROM YourView WITH(NOEXPAND)
WHERE ....
Maybe you don't need a view but you just don't have th correct indexes on the tables, can you post the DDL of the tables (including indexes and constraints)
也许你不需要一个视图,但你只是在表上没有正确的索引,你可以发布表的DDL(包括索引和约束)
#6
I had the same problem and ended up putting the sub queries in clustered index views themselves.
我遇到了同样的问题,最终将子查询放在聚集索引视图中。