We have a SQL SERVER with only one table. This table contains 50 million rows. The table has 31 columns. 1 Identity column and 30 nchar columns with different sizes. The table size is of 15 GB.
我们有一个只有一个表的SQL SERVER。该表包含5000万行。该表有31列。 1个标识列和30个具有不同大小的nchar列。表大小为15 GB。
The table contains information of the year 2017. Initially there's no going to be inserts in the table. But in the future the client maybe will want to add the year 2018 or maybe 2016. For the time being is for queries.
该表包含2017年的信息。最初表中不会有插入。但在未来,客户可能会想要添加2018年或2016年。暂时是查询。
The queries need minutes to perform. So, I thought that it will be better to divide the table in 1 table per month because the table is too big. But the solution doesn't satisfy the client because he wants to query all the data.
查询需要几分钟才能执行。所以,我认为最好每月将表分成1个表,因为表格太大了。但该解决方案不满足客户端,因为他想查询所有数据。
So, given this scenario what would be the best approach to handle this table and make it possible to query fast?
那么,鉴于这种情况,处理此表的最佳方法是什么,并且可以快速查询?
1 个解决方案
#1
1
I would start with obvious which is index. Refer to: What columns generally make good indexes?
我会从明显的索引开始。请参阅:哪些列通常可以生成良好的索引?
At the problem you mention, you could always break down the table into smaller one and in order to query to the whole data set, it could be achieved either from view either from some stored procedures where you could include some logic.
在您提到的问题中,您总是可以将表分解为较小的表,并且为了查询整个数据集,可以从某些存储过程查看,也可以包含某些逻辑。
#1
1
I would start with obvious which is index. Refer to: What columns generally make good indexes?
我会从明显的索引开始。请参阅:哪些列通常可以生成良好的索引?
At the problem you mention, you could always break down the table into smaller one and in order to query to the whole data set, it could be achieved either from view either from some stored procedures where you could include some logic.
在您提到的问题中,您总是可以将表分解为较小的表,并且为了查询整个数据集,可以从某些存储过程查看,也可以包含某些逻辑。