I have a large table with 10+ millions records in a SQL Server database. The table contains certain type of data for all 50 states in the US. So if I create 50 views, one for each state, from this table, would the performance of making queries from my application be improved? Other suggestions?
我在SQL Server数据库中有一个包含10多万条记录的大表。该表包含美国所有50个州的特定类型的数据。因此,如果我从这个表创建50个视图,每个状态一个,那么从我的应用程序中进行查询的性能是否会提高?其他建议?
6 个解决方案
#1
8
No. A view is a macro that expands so the same tables end up in the plan anyway.
不是。视图是一个扩展的宏,因此无论如何,相同的表最终会在计划中结束。
Unless it's indexed. 50 indexed views is most likely overkill.
除非它被索引。 50个索引视图很可能是矫枉过正。
If you have slow performance with 50 million rows (it's not that much really) then it's an indexing problem.
如果你有5000万行的性能很慢(实际上并不是那么多),那么它就是一个索引问题。
Edit:
To start with, I'd use the weighted missing index dmv query to see where you get the most bang for your buck
首先,我将使用加权缺失索引dmv查询来查看您获得最大收益的位置
#2
2
Normal (non-indexed) views cannot improve performance - they could be thought of as 'shorthands' or 'aliases' for SELECT queries, as they have no physical structure under them.
正常(非索引)视图无法提高性能 - 它们可以被认为是SELECT查询的“缩写”或“别名”,因为它们下面没有物理结构。
Indexed views are a different beast, but it doesn't seem that you need them so far.
索引视图是一种不同的野兽,但到目前为止你似乎并不需要它们。
What you need is to create appropriate indexes on your table, and, possibly, to redesign the table (for instance, split it to several tables).
您需要的是在表上创建适当的索引,并且可能重新设计表(例如,将其拆分为多个表)。
If you want more specific advice, post here the table structure and some examples of typical queries (the ones you want to optimise for).
如果您需要更具体的建议,请在此处发布表结构和典型查询的一些示例(您要优化的查询)。
#3
0
You're on the right track:
你走在正确的轨道上:
Start with making sure you index the data to reflect the reads you need to be fast. Then, since filtering does make it faster, I would consider require filtering on state by allowing access to the data via a stored procedure (with a parameter for state).
首先确保索引数据以反映您需要快速读取的数据。然后,由于过滤确实使它更快,我会考虑通过允许通过存储过程(具有状态参数)访问数据来过滤状态。
#4
0
If you have the proper index and usage in your execution plans, then the big issues is the quantity of memory cache and read speed of the disk. Creating views won't fix any of those, it is still the same data on the same disk/cache, just a different logical way of referring to it.
如果您的执行计划中有正确的索引和用法,那么最大的问题是内存缓存的数量和磁盘的读取速度。创建视图不会解决任何问题,它仍然是同一磁盘/缓存上的相同数据,只是引用它的不同逻辑方式。
#5
0
One simple suggestion:
一个简单的建议:
use [YourDataBase]
select * from sys.dm_db_missing_index_details as ddmid
#6
0
That is such a tiny database that if you are having performance problems your indexing wrong, you databases design is bad or you have designed poorly performing queries. SQL server can handle trillions of records without breaking a sweat if designed correctly.
这是一个如此小的数据库,如果您遇到性能问题,索引错误,您的数据库设计很糟糕,或者您设计的性能不佳的查询。如果设计正确,SQL服务器可以处理数万亿条记录而不会出汗。
Views BTW can be performance Killers if you use views that call views.
如果您使用调用视图的视图,BTW可能是性能杀手。
#1
8
No. A view is a macro that expands so the same tables end up in the plan anyway.
不是。视图是一个扩展的宏,因此无论如何,相同的表最终会在计划中结束。
Unless it's indexed. 50 indexed views is most likely overkill.
除非它被索引。 50个索引视图很可能是矫枉过正。
If you have slow performance with 50 million rows (it's not that much really) then it's an indexing problem.
如果你有5000万行的性能很慢(实际上并不是那么多),那么它就是一个索引问题。
Edit:
To start with, I'd use the weighted missing index dmv query to see where you get the most bang for your buck
首先,我将使用加权缺失索引dmv查询来查看您获得最大收益的位置
#2
2
Normal (non-indexed) views cannot improve performance - they could be thought of as 'shorthands' or 'aliases' for SELECT queries, as they have no physical structure under them.
正常(非索引)视图无法提高性能 - 它们可以被认为是SELECT查询的“缩写”或“别名”,因为它们下面没有物理结构。
Indexed views are a different beast, but it doesn't seem that you need them so far.
索引视图是一种不同的野兽,但到目前为止你似乎并不需要它们。
What you need is to create appropriate indexes on your table, and, possibly, to redesign the table (for instance, split it to several tables).
您需要的是在表上创建适当的索引,并且可能重新设计表(例如,将其拆分为多个表)。
If you want more specific advice, post here the table structure and some examples of typical queries (the ones you want to optimise for).
如果您需要更具体的建议,请在此处发布表结构和典型查询的一些示例(您要优化的查询)。
#3
0
You're on the right track:
你走在正确的轨道上:
Start with making sure you index the data to reflect the reads you need to be fast. Then, since filtering does make it faster, I would consider require filtering on state by allowing access to the data via a stored procedure (with a parameter for state).
首先确保索引数据以反映您需要快速读取的数据。然后,由于过滤确实使它更快,我会考虑通过允许通过存储过程(具有状态参数)访问数据来过滤状态。
#4
0
If you have the proper index and usage in your execution plans, then the big issues is the quantity of memory cache and read speed of the disk. Creating views won't fix any of those, it is still the same data on the same disk/cache, just a different logical way of referring to it.
如果您的执行计划中有正确的索引和用法,那么最大的问题是内存缓存的数量和磁盘的读取速度。创建视图不会解决任何问题,它仍然是同一磁盘/缓存上的相同数据,只是引用它的不同逻辑方式。
#5
0
One simple suggestion:
一个简单的建议:
use [YourDataBase]
select * from sys.dm_db_missing_index_details as ddmid
#6
0
That is such a tiny database that if you are having performance problems your indexing wrong, you databases design is bad or you have designed poorly performing queries. SQL server can handle trillions of records without breaking a sweat if designed correctly.
这是一个如此小的数据库,如果您遇到性能问题,索引错误,您的数据库设计很糟糕,或者您设计的性能不佳的查询。如果设计正确,SQL服务器可以处理数万亿条记录而不会出汗。
Views BTW can be performance Killers if you use views that call views.
如果您使用调用视图的视图,BTW可能是性能杀手。